CASE WHEN "AND" options

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


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:

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
Should work, but don't use 2 WHEN's. Something like:

CASE WHEN Jobs.PaysTotal < 2000 AND Jobs.Splits [need an operator here: equals, less than, greater than?] .150 THEN 'Under 2K with less than 150'
 
Upvote 0
Thanks Russell..great help! I'm running into another snag. I need the SQL to calculate percentages of each JobSplits as well.

So for each "LPAR" I in theory should have my counts "JobSplits" that return a number. I need the percentage of each one of those categories for each LPAR.

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 AND Jobs.Splits < 150 THEN 'Under 2K less 150'
                       WHEN Jobs.PaysTotal	< 2000 AND Jobs.Splits > 150 THEN 'Under 2K over 150'
                       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;

Here is link for a view of my sheet. I need percentages next to each number.
http://imgur.com/a/uyUu0
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top