CASE When Distinct Count 2

[RJL1]

I have the following qury that returns a cycle count class (a,b or c) as it is stored inthe DB. I like to put a case statement that would return what the cycle count class should be based on the COUNT(DISTINCT SD.SHIPMENT_ID).

The case statement should be somethin like

WHEN COUNT(DISTINCT SD.SHIPMENT_ID) < 100      THEN 'C' 
WHEN COUNT(DISTINCT SD.SHIPMENT_ID) 100 to 199 THEN 'B' 
WHEN COUNT(DISTINCT SD.SHIPMENT_ID) > 200      THEN 'A'

This would give me a list of what the item Cycle Coutn Class is set up now and what it should be chnaged to depending on the COUNT(DISTINCT SD.SHIPMENT_ID)

SELECT
LI.COMPANY,
LI.ITEM,
LI.ITEM_DESC,
I.ITEM_CATEGORY5 AS 'CYCLE_COUNT_WEEK',
I.ITEM_CATEGORY4 AS 'CYCLE_COUNT_CLASS',
SUM(LI.ON_HAND_QTY) AS 'QTY',
LI.QUANTITY_UM,
COUNT(DISTINCT SD.SHIPMENT_ID) AS 'SHIPMENTS'
FROM
LOCATION_INVENTORY LI WITH(NOLOCK)
  JOIN ITEM I ON I.ITEM = LI.ITEM
  JOIN SHIPMENT_DETAIL SD ON SD.ITEM = LI.ITEM
  JOIN SHIPMENT_HEADER SH ON SH.INTERNAL_SHIPMENT_NUM = SD.INTERNAL_SHIPMENT_NUM
WHERE
LI.ITEM in ('X0588700','X0584700','X04655N0') AND
SH.ACTUAL_SHIP_DATE_TIME BETWEEN '01/01/2009 00:00:00' AND '01/30/2009 23:59:59'
GROUP BY
LI.COMPANY,
LI.ITEM,
LI.ITEM_DESC,
I.ITEM_CATEGORY5,
I.ITEM_CATEGORY4,
LI.QUANTITY_UM,
I.USER_STAMP
HAVING
SUM(LI.ON_HAND_QTY) > 0
ORDER BY
COUNT(DISTINCT SD.SHIPMENT_ID),
LI.COMPANY,
I.ITEM_CATEGORY5,
I.ITEM_CATEGORY4

Any help with this is appreciated
Thanks
RJL1

 

[markros]

Use your original query as derived table, e.g.

select *, case when Shipments < 100 then ... from (myBigSelect) BS

 

[RJL1]

Thanks for the hepl markros.

If I wanted to turn this into an update query to automaticaly set the value I.ITEM_CATEGORY4 (CYCLE_COUNT_CLASS) to the new calculated value how could this be done. I have done update statements before but not using a case

Current query after markros help

SELECT *,
CASE
    WHEN SHIPMENTS < 100  THEN 'C'
    WHEN SHIPMENTS BETWEEN 100 AND 199 THEN 'B'
    WHEN SHIPMENTS > 200 THEN 'A'
 END AS 'CCCLASS'
FROM(
SELECT
LI.COMPANY,
LI.ITEM,
LI.ITEM_DESC,
I.ITEM_CATEGORY5 AS 'CYCLE_COUNT_WEEK',
I.ITEM_CATEGORY4 AS 'CYCLE_COUNT_CLASS',
SUM(LI.ON_HAND_QTY) AS 'QTY',
LI.QUANTITY_UM,
COUNT(DISTINCT SD.SHIPMENT_ID) AS 'SHIPMENTS'
FROM
LOCATION_INVENTORY LI WITH(NOLOCK)
  JOIN ITEM I ON I.ITEM = LI.ITEM
  JOIN SHIPMENT_DETAIL SD ON SD.ITEM = LI.ITEM
  JOIN SHIPMENT_HEADER SH ON SH.INTERNAL_SHIPMENT_NUM = SD.INTERNAL_SHIPMENT_NUM
WHERE
LI.ITEM in ('X0588700','X0584700','X04655N0') AND
SH.ACTUAL_SHIP_DATE_TIME BETWEEN '01/01/2009 00:00:00' AND '01/30/2009 23:59:59'
GROUP BY
LI.COMPANY,
LI.ITEM,
LI.ITEM_DESC,
I.ITEM_CATEGORY5,
I.ITEM_CATEGORY4,
LI.QUANTITY_UM,
I.USER_STAMP
HAVING
SUM(LI.ON_HAND_QTY) > 0) BS

Thanks
RJL1

 

[markros]

What is your SQL Server version?

Assuming SQL Server 2005 or above

;with cte_Shipments as (you original inner select)

update myTable set value = case when SC.Shipments < 100 then 'A' when etc. end from myTable inner join cte_Shipments CS on
myTable.PK = CS.PK

 

[RJL1]

I looked at your reply and does this mean I need to create a table with the results of the first script then join them on the primary key.

Can this be done on the fly (e.g. create the table, populate it with the data and then run the update)

Thanks for you help
RJL1

 

[bborissov]

You could use Derived tables in UPDATE also, no matter what version of SQL Server you use (at least 2000 and above)

UPDATE YourTable SET SomeField = CASE WHEN Tbl1.Shipment ....
FROM YourTable
INNER JOIN (Your select goes here) Tbl1
      ON (join condition)

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[markros]

I just wanted to separate big fat SQL from the update into CTE. Of course, you can use it as a derived table, as Boris showed.

 

[RJL1]

Thanks to everyone for their help It worked like a charm. This will save a ton of time

Thanks
RJL1