SQL Server => How to use "case when" on multiple column to show a formula result based-CodePudding

I'm trying to provide an overall result of a formula ([CALCULATION]) and then in two extra columns ([NO_TENURE] & [TENURE]) the same calculation but using "CASE WHEN" to filter the information based on another column called [TENURE], everything in one single line like this:

MONTH       TYPE       CALCULATION                             NO_TENURE                               TENURE
----------- ---------- --------------------------------------- --------------------------------------- ---------------------------------------
1           TYPE1      5.33333333333                           5.33333333333                           6.81333333333

Right now is being shown this way:

MONTH       TYPE       CALCULATION                             NO_TENURE                               TENURE
----------- ---------- --------------------------------------- --------------------------------------- ---------------------------------------
1           TYPE1      5.33333333333                           5.33333333333                           NULL
1           TYPE1      6.81333333333                           NULL                                    6.81333333333
1           TYPE2      5.55555555555                           5.55555555555                           NULL
1           TYPE2      5.95238095238                           NULL                                    5.95238095238

This is the code I'm using:

IF OBJECT_ID('TEMPDB..#TEST') IS NOT NULL DROP TABLE #TEST
GO

CREATE TABLE #TEST 
(
    MONTH INT,
    TYPE VARCHAR(10),
    TENURE INT,
    NUMERADOR INT,
    DENOMINADOR INT
)

INSERT INTO #TEST
VALUES  (1, 'TYPE1', 35, 320, 60),
        (1, 'TYPE1', 96, 511, 75),
        (1, 'TYPE2', 23, 400, 72),
        (1, 'TYPE2', 102, 500, 84);

SELECT MONTH
       ,TYPE
       ,(SUM(NUMERADOR)/CONVERT(DECIMAL(10,4) ,SUM(DENOMINADOR))) [CALCULATION]
       ,CASE WHEN TENURE < 90 THEN (SUM(NUMERADOR)/CONVERT(DECIMAL(10,4) ,SUM(DENOMINADOR))) END [NO_TENURE]
       ,CASE WHEN TENURE >= 90 THEN (SUM(NUMERADOR)/CONVERT(DECIMAL(10,4) ,SUM(DENOMINADOR))) END [TENURE]

FROM #TEST
GROUP BY MONTH,
       TYPE,
       TENURE

I really appreciate the help!

CodePudding user response：

You have TENURE in your GROUP BY, which means that each different tenure in your source data will get a different row in the output.

If you take TENURE out of there, you'll get the one row you want. The your CASE expression won't work, because it should be INSIDE the aggregate functions...

SELECT
   MONTH
  ,TYPE
  ,SUM(                            NUMERADOR    ) / CONVERT(DECIMAL(10,4), SUM(                            DENOMINADOR    )) [CALCULATION]
  ,SUM(CASE WHEN TENURE <  90 THEN NUMERADOR END) / CONVERT(DECIMAL(10,4), SUM(CASE WHEN TENURE <  90 THEN DENOMINADOR END)) [NO_TENURE]
  ,SUM(CASE WHEN TENURE >= 90 THEN NUMERADOR END) / CONVERT(DECIMAL(10,4), SUM(CASE WHEN TENURE >= 90 THEN DENOMINADOR END)) [TENURE]
FROM
  #TEST
GROUP BY
   MONTH,
   TYPE

The next question is how to avoid the massive repetition. That's done by using sub-queries, CTEs or applied functions, to define expressions in one scope and use them in a different scope.

I'm on a phone, so that will take me a few minutes, but I'll add it shortly.

WITH
  NORMALISED_RESULT AS
(
  SELECT
    MONTH,
    TYPE,
    IS_TENURED,
    SUM(NUMERADOR) / CONVERT(DECIMAL(10,4), SUM(DENOMINADOR)) AS CALCULATION
  FROM
    #TEST
  CROSS APPLY
  (
    SELECT
      CASE WHEN TENURE <  90 THEN 0
           WHEN TENURE >= 90 THEN 1
                             ELSE -1 END AS IS_TENURED
  )
    AS TENURE_CHECK
  GROUP BY
    GROUPING SETS(
      (MONTH, TYPE, IS_TENURED),
      (MONTH, TYPE)
    )
)

-- This next part just pivots the normalised results
-- It can be skipped if three rows of normalised results is acceptable.

SELECT
  MONTH,
  TYPE,
  MAX(CASE WHEN IS_TENURED IS NULL THEN CALCULATION END) AS CALCULATION,
  MAX(CASE WHEN IS_TENURED = 0     THEN CALCULATION END) AS NO_TENURE,
  MAX(CASE WHEN IS_TENURED = 1     THEN CALCULATION END) AS TENURE
FROM
  NORMALISED_RESULT
GROUP BY
  MONTH,
  TYPE

The ELSE -1 is only there incase some rows have a NULL for the TENURE.

Demo : db<>fiddle