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!
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.
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