Reuse Case statement calculation in select Query

Question

select 
ROW_NUMBER()OVER(PARTITION BY [ConsLookUpID] ORDER BY [ConsLookUpID]) as [Write-off-Count]  
,CASE
WHEN ROW_NUMBER()OVER(PARTITION BY [ConsLookUpID] ORDER BY [ConsLookUpID])=1
THEN 
        CASE
            WHEN DATEPART(DAY,GETDATE())>15
                    THEN FORMAT(DATEADD(MONTH,0,GETDATE()),'yyyy-MM') 
            ELSE
                FORMAT(DATEADD(MONTH,-1,GETDATE()),'yyyy-MM')
        END
WHEN ROW_NUMBER()OVER(PARTITION BY [ConsLookUpID] ORDER BY [ConsLookUpID])=2
THEN 
        CASE
            WHEN DATEPART(DAY,GETDATE())>15
                THEN FORMAT(DATEADD(MONTH,-1,GETDATE()),'yyyy-MM') 
            ELSE
                FORMAT(DATEADD(MONTH,-2,GETDATE()),'yyyy-MM')
        END
WHEN ROW_NUMBER()OVER(PARTITION BY [ConsLookUpID] ORDER BY [ConsLookUpID])=3
THEN 
        CASE
            WHEN DATEPART(DAY,GETDATE())>15
                THEN FORMAT(DATEADD(MONTH,-2,GETDATE()),'yyyy-MM') 
            ELSE
                FORMAT(DATEADD(MONTH,-3,GETDATE()),'yyyy-MM')
        END
ELSE ''
        END AS [Write-off-Month]
,[RevLookUpId]+'_'+[Write-off-Month] as [Write-off-Key]
FROM ABC

Result I want is use the case statement result in the Writ-off-Key column as the value of Write-off-Month. I can use the case statement again, will give me the answer, but I do not want to do that, as it will increase the processing time.

Tried performing case statement calculation in different query and then inner joining both he queries using conslookUpId, but conslookupId are repeating thus even though I had used inner join, it is performing cross join.

Seems to me may be not achievable in same query, but there will be some way, and cannot figure it out. Also I would like to use only single query if possible.

Answer 1

SQL is unfortunately weird sometimes about making you repeat yourself for best results. But I need to address this:

I can use the case statement again, will give me the answer, but I do not want to do that, as it will increase the processing time.

... which is not true. While SQL is weird about making you repeat yourself, most database engines are (usually!) good about recognizing when you repeat the same expression and only doing the calculation work once.

However, in this case, you can significantly improve things by nesting some of the code as a subquery (or pulling it out to a CTE). Note you have to do some of this anyway for the windowing function to work as you expect. Significantly, this also lets us use some math to compute the number of months to adjust in one place, rather than needing a CASE result for each possible number of write-offs.

WITH PreCompute As (
    SELECT RevLookUpId
        , ROW_NUMBER()OVER(PARTITION BY [ConsLookUpID] ORDER BY [ConsLookUpID]) as [Write-off-Count] 
        , DATEPART(DAY, GETDATE()) / 16 As DayOffset
    FROM ABC 
)
SELECT 
    [Write-off-Count]
    ,CASE WHEN [Write-off-Count] BETWEEN 1 AND 3
          THEN FORMAT(DATEADD(MONTH, DayOffset - [Write-off-Count], GETDATE()), 'yyyy-MM')
          ELSE '' END As [Write-off-Month]
    ,[RevLookUpId]
        +'_'
        +CASE WHEN [Write-off-Count] BETWEEN 1 AND 3
              THEN FORMAT(DATEADD(MONTH, DayOffset - [Write-off-Count], GETDATE()), 'yyyy-MM')
              ELSE '' END as [Write-off-Key]
FROM PreCompute

In fact, the only reason the above code still uses CASE at all is to control for the set number of write-off counts provided in the original code. We can get around even that if we join to a table-value constructor to filter the allowed values, like this:

WITH PreCompute As (
    SELECT RevLookUpId
        ,ROW_NUMBER()OVER(PARTITION BY [ConsLookUpID] ORDER BY [ConsLookUpID]) as [Write-off-Count] 
        ,DATEPART(DAY, GETDATE()) / 16 As DayOffset
    FROM ABC 
)
SELECT [Write-off-Count]
    ,COALESCE(FORMAT(DATEADD(MONTH, DayOffset - WOCount, GETDATE()), 'yyyy-MM'),'') As [Write-off-Month]
    ,[RevLookUpId] + '_'
        + COALESCE(FORMAT(DATEADD(MONTH, DayOffset - WOCount, GETDATE()), 'yyyy-MM'),'') as [Write-off-Key]
FROM PreCompute
LEFT JOIN ( VALUES (1), (2), (3) ) Map(WOCount)
     ON Map.WOCount = PreCompute.[Write-Off-Count]

And now, since I also use an integer division operation to determine which half of the month we have, there are no CASE expressions anywhere in the code at all! There is still some repetition, but it's greatly reduced and, again, not as costly to the query as you might expect.

Note these options are intended to exactly match behavior of the code from the question... but it seems like not having any value at all for the month portion of the Write-off-key if the count is out of spec is probably actually a mistake (or a data impossibility). And if you can, indeed, be 100% certain you'll only have counts from 1 to 3, we can simplify even further:

WITH PreCompute As (
    SELECT RevLookUpId
        ,ROW_NUMBER()OVER(PARTITION BY [ConsLookUpID] ORDER BY [ConsLookUpID]) as [Write-off-Count] 
        ,DATEPART(DAY, GETDATE()) / 16 As DayOffset
    FROM ABC 
)
SELECT 
    [Write-off-Count]
    ,FORMAT(DATEADD(MONTH, DayOffset - [Write-off-Count], GETDATE()), 'yyyy-MM') As [Write-off-Month]
    ,[RevLookUpId] + '_'
        + FORMAT(DATEADD(MONTH, DayOffset - [Write-off-Count], GETDATE()), 'yyyy-MM') as [Write-off-Key]
FROM PreCompute

Answer 2

There are two way I usually do this...

1. Sub-queries / CTEs to create nested levels of scope
2. APPLYing scalar valued expressions

The below uses both, just so that they can both be shown, but putting the CASE expression in the CTE would have been enough.

(There are several other improvements that can be made, but this is just to demonstrate two techniques to reuse preceding expressions.)

WITH
   counted AS
(
   SELECT
      ABC.*,
      ROW_NUMBER()
         OVER(
            PARTITION BY [ConsLookUpID]
                ORDER BY [ConsLookUpID]
         )
            AS [Write-off-Count]
   FROM
      ABC
)
SELECT
   [Write-off-Count],
   [Write-off-Month],
   [RevLookUpId] + '_' + [Write-off-Month]
      AS [Write-off-Key]
FROM
   counted
CROSS APPLY
(
   SELECT
      CASE
         WHEN DATEPART(DAY, GETDATE()) > 15
         THEN GETDATE()
         ELSE DATEADD(MONTH, -1, GETDATE())
      END
         AS base_date
)
   base_date
CROSS APPLY
(
   SELECT
      CASE
         [Write-off-Count]
            WHEN 1 THEN FORMAT(DATEADD(MONTH,  0, base_date.base_date), 'yyyy-MM')
            WHEN 2 THEN FORMAT(DATEADD(MONTH, -1, base_date.base_date), 'yyyy-MM')
            WHEN 3 THEN FORMAT(DATEADD(MONTH, -2, base_date.base_date), 'yyyy-MM')
                   ELSE ''
      END
         AS [Write-off-Month]
)
   month_calc

Less repetition:

WITH
   counted AS
(
   SELECT
      ABC.*,
      ROW_NUMBER()
         OVER(
            PARTITION BY [ConsLookUpID]
                ORDER BY [ConsLookUpID]
         )
            AS [Write-off-Count]
   FROM
      ABC
)
SELECT
   [Write-off-Count],
   [Write-off-Month],
   [RevLookUpId] + '_' + [Write-off-Month]   AS [Write-off-Key]
FROM
   counted
CROSS APPLY
(
   SELECT
      CASE
         WHEN [Write-off-Count] <= 3
         THEN FORMAT(
                 DATEADD(
                     MONTH,
                     DATEPART(DAY, GETDATE()) / 16 
                      - [Write-off-Count],
                     getDate()
                 ),
                 'yyyy-MM'
              )
         ELSE ''
     END
        AS [Write-off-Month]
)
   month_calc