robert0803
Board Regular
- Joined
- Aug 3, 2005
- Messages
- 115
Is it possible to do CASE WHEN "AND" option. Below is an example of what I am trying to create. Long code is the original.
, CASE WHEN Jobs.PaysTotal < 2000 AND <b>WHEN Jobs.Splits .150 THEN 'Under 2K with less than 150'</b>
WHEN Jobs.PaysTotal BETWEEN 2000 AND 4000 THEN '2K to 4K'
WHEN Jobs.PaysTotal BETWEEN 4001 AND 8000 THEN '4K to 8K'
ELSE 'Over 8K' END AS BatchRngSize
, CASE WHEN Jobs.PaysTotal < 2000 AND <b>WHEN Jobs.Splits .150 THEN 'Under 2K with less than 150'</b>
WHEN Jobs.PaysTotal BETWEEN 2000 AND 4000 THEN '2K to 4K'
WHEN Jobs.PaysTotal BETWEEN 4001 AND 8000 THEN '4K to 8K'
ELSE 'Over 8K' END AS BatchRngSize
Code:
USE PTS;
SELECT CONVERT(Date,Jobs.CreateDate,101) AS BATCH_DATE
, PrintCenters.PrintCenterName
, PrtLpar.LparName AS PRINT_LPAR
, Jobs.JobName
, Jobs.Batch
, DATEPART(Hour,(Jobs.CreateDate)) AS PRT_HR
, Priority.PriorityName
, RTRIM(LTRIM(CASE WHEN DATEPART(n,Jobs.CreateDate) BETWEEN 00 AND 06 THEN STR( DATEPART( Hour,( Jobs.CreateDate ) ) ) +':00'
WHEN DATEPART(n,Jobs.CreateDate) BETWEEN 07 AND 22 THEN STR( DATEPART( Hour,( Jobs.CreateDate ) ) ) + ':15'
WHEN DATEPART(n,Jobs.CreateDate) BETWEEN 23 AND 36 THEN STR( DATEPART( Hour,( Jobs.CreateDate ) ) ) + ':30'
WHEN DATEPART(n,Jobs.CreateDate) BETWEEN 37 AND 52 THEN STR( DATEPART( Hour,( Jobs.CreateDate ) ) ) + ':45'
WHEN DATEPART(n,Jobs.CreateDate) BETWEEN 53 AND 59 THEN STR( DATEPART( Hour,( Jobs.CreateDate ) ) + 1) + ':00'
END)) as PRT_QTR_HR
, CASE WHEN JobLabel.LabelName LIKE '%/**/%'
THEN TripGroups.TripGroupName
ELSE JobLabel.LabelName
END AS LabelType
, JobLabel.LabelName
, TripGroups.TripGroupName
, Jobs.CoCount
, Jobs.PaysTotal
, Jobs.CheckTotal
, Jobs.VoucherTotal
, Jobs.Splits
, CASE WHEN Jobs.PaysTotal < 2000 THEN 'Under 2K'
WHEN Jobs.PaysTotal BETWEEN 2000 AND 4000 THEN '2K to 4K'
WHEN Jobs.PaysTotal BETWEEN 4001 AND 8000 THEN '4K to 8K'
ELSE 'Over 8K' END AS BatchRngSize
, DATENAME(dw,Jobs.CreateDate) AS DOW
FROM Jobs
INNER JOIN
LPARS AS PrtLpar ON PrtLpar.LparId = Jobs.PrintLParId
LEFT JOIN
PrintCenters ON PrintCenters.PrintCenterId = Jobs.PrintCenterId
LEFT JOIN
Priority ON Priority.PriorityId = Jobs.PriorityId
LEFT JOIN
JobLabel ON JobLabel.LabelId = Jobs.JobLabelId
LEFT JOIN
TripGroups ON TripGroups.TripGroupId = Jobs.TripGroupId
WHERE CONVERT(Date,Jobs.CreateDate,101) BETWEEN '11/01/2016' AND '11/30/2016'
AND Jobs.JobTypeId = '5'
AND CASE WHEN LEN (JobLabel.LabelName) = 11
THEN TripGroups.TripGroupName
ELSE JobLabel.LabelName
END <> 'REGENS'
ORDER BY
PrintCenters.PrintCenterName ASC,
PrtLpar.LparName ASC,
Jobs.Batch DESC,
CONVERT(Date,Jobs.CreateDate,101) ASC;
Last edited: