Using CASE statement name as another reference filed in another CASE statement

Question

Is it possible to refer to another case statement name in another case statement within SQL query?

Example: I have 3 case statements. The first 2 case statements are returning values based off coded fields. My 3rd case statement I would like to refer to the ending case name to return a sum of quantity.

However, I cannot figure how to get the case statement to refer to the previous case names I created. I hope I am explaining this correctly.

Any assistance would be greatly appreciated. Please see attached image for more detail.

SELECT CI_ITEM.ITEMCODE
    , CI_ITEM.ITEMCODEDESC

    , CASE WHEN DATEDIFF("M",CI_ITEM.DATECREATED,GETDATE()) <60 THEN DATEDIFF("M",CI_ITEM.DATECREATED,GETDATE()) 
    ELSE 60 END AS NO_OF_MONTHS

    , CASE WHEN DATEDIFF("M",IM_ITEMTRANSACTIONHISTORY.TRANSACTIONDATE,GETDATE()) <=60 
    AND IM_ITEMTRANSACTIONHISTORY.TRANSACTIONCODE IN ('BI','II','SO','WI') 
    THEN IM_ITEMTRANSACTIONHISTORY.TRANSACTIONQTY *-1 ELSE '0' END AS QTY_CONSUMED_60_MONTHS

    , CASE WHEN NO_OF_MONTHS = 0 THEN 0 ELSE SUM([QTY_CONSUMED_60_MONTHS])/ [NO_OF_MONTHS] END AS MONTHLY_AVE_ON_60MONTHS_DATA

FROM CI_ITEM
INNER JOIN IM_ITEMTRANSACTIONHISTORY ON CI_ITEM.ITEMCODE = IM_ITEMTRANSACTIONHISTORY.ITEMCODE

enter image description here

You cannot refer to a column alias in the `SELECT` where it is defined. You need to use a subquery. Or, a CTE. Tag your question with the database you are using. — Gordon Linoff, Apr 18 '17 at 15:27
Please don't use ALL CAPS in titles, it's considered the equivalent of yelling — jmoerdyk, Apr 18 '17 at 15:31
The reason you can't do what you're trying in the "SAME" select is because the SELECT values are generated at the same time. So the time the last case is being generated, it has no knowledge of the prior cases. This may be true. There may be some exceptions to this on different databases (mySQL I think does it in order of select) in which the order of the select matters, but I do not believe that is the case with MS-Access — xQbert, Apr 18 '17 at 15:48

score 1 · Accepted Answer · answered Apr 18 '17 at 15:34

Simply wrap your dependent cases within a sub query and reference them as fields of the sub query result.

SELECT
    *,
     CASE WHEN NO_OF_MONTHS = 0 THEN 0 ELSE SUM([QTY_CONSUMED_60_MONTHS])/ [NO_OF_MONTHS] END AS MONTHLY_AVE_ON_60MONTHS_DATA
FROM
(
    SELECT CI_ITEM.ITEMCODE
        , CI_ITEM.ITEMCODEDESC

        , CASE WHEN DATEDIFF("M",CI_ITEM.DATECREATED,GETDATE()) <60 THEN DATEDIFF("M",CI_ITEM.DATECREATED,GETDATE()) 
        ELSE 60 END AS NO_OF_MONTHS

        , CASE WHEN DATEDIFF("M",IM_ITEMTRANSACTIONHISTORY.TRANSACTIONDATE,GETDATE()) <=60 
        AND IM_ITEMTRANSACTIONHISTORY.TRANSACTIONCODE IN ('BI','II','SO','WI') 
        THEN IM_ITEMTRANSACTIONHISTORY.TRANSACTIONQTY *-1 ELSE '0' END AS QTY_CONSUMED_60_MONTHS

    FROM CI_ITEM
    INNER JOIN IM_ITEMTRANSACTIONHISTORY ON CI_ITEM.ITEMCODE = IM_ITEMTRANSACTIONHISTORY.ITEMCODE
)AS X

This was perfect! Thank you Ross! – Neisha Apr 19 '17 at 14:54 — Neisha, Apr 19 '17 at 14:54

Using CASE statement name as another reference filed in another CASE statement

1 Answers1