SUM when case statement

[RJL1]

Hi

In my table I have a work type field that lets me know what area an order was picked from. I like to get a summary by month of how many cartons were picked from each area

The wotk types are

Case
Repack
Bulk

This query gives me a total count of how many picks were made in each area but I like to get how many cartons were picked in each area. So the results are a total picks across all areas and a breakdown b area on how many pickswere made in each area. I like to get something similar but on the number of cartons that were picked in each area. My carton qty field is "Quantity"

SELECT
MONTH(TH.ACTIVITY_DATE_TIME) AS 'MONTH',
COUNT(TH.INTERNAL_ID) AS 'TOAL PICKS',
COUNT(CASE WORK_TYPE
WHEN 'Bulk Pallet Pick' THEN 'Bulk'
END) AS 'BULK',
COUNT(CASE WORK_TYPE
WHEN 'Order Pick' THEN 'Case'
END) AS 'CASE',
COUNT(CASE WORK_TYPE
WHEN 'Repack' THEN 'Repack'
END) AS 'REPACK'
FROM
TRANSACTION_HISTORY TH WITH(NOLOCK)
WHERE
TH.ACTIVITY_DATE_TIME BETWEEN '10/1/2007 00:00:00 AM' AND '9/30/2008 23:59:59 PM'

Thanks in advance for any help
RL

 

[r937]

you mention Case, Repack, Bulk

what's a carton?


r937.com | rudy.ca

 

[gmmastros]

Just guessing...

SELECT
MONTH(TH.ACTIVITY_DATE_TIME) AS 'MONTH',
COUNT(TH.INTERNAL_ID) AS 'TOAL PICKS',
COUNT(CASE WORK_TYPE
    WHEN 'Bulk Pallet Pick' THEN 'Bulk'
END) AS 'BULK',
COUNT(CASE WORK_TYPE
    WHEN 'Order Pick' THEN 'Case'
END) AS 'CASE',
COUNT(CASE WORK_TYPE
    WHEN 'Repack'  THEN 'Repack'
END) AS 'REPACK',
[red]Sum(Case When WORK_TYPE = 'Bulk Pallet Pick' Then Quantity Else 0 End) As BulkCartonsPicked,
Sum(Case When WORK_TYPE = 'Order Pick' Then Quantity Else 0 End) As OrderPickCartonsPicked,

Sum(Case When WORK_TYPE = 'Repack' Then Quantity Else 0 End) As RepackCartonsPicked
[/red]
FROM
TRANSACTION_HISTORY TH WITH(NOLOCK)
WHERE
TH.ACTIVITY_DATE_TIME BETWEEN '10/1/2007 00:00:00 AM' AND '9/30/2008 23:59:59 PM'

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom