Case Statement

mysqlquestion · March 5, 2018, 11:00pm

I used Birthday field to get age_group column. if there is no age_group for "01-19" and "75 or older", then I still want to show "01-19" under column "age_group and 0 under no_employees column. I don't know how to add into my case statement below. Please help me how to add nest case statement into the current case statement or whatever way...
here's my query and out put

DECLARE @Date DATE = (SELECT DATEADD(M, DATEDIFF(M, 0, GETDATE()), 0))
DECLARE @LastDay date = DATEADD(D, -1, @Date)
DECLARE @FirstDay date= DATEADD(M, -1, @Date)

SELECT
BENEFIT.PLAN_CODE,
R_PLAN.R_DESC,
SUM(BENEFIT.COVER_AMT) AS COVG_AMT,
CASE
WHEN DATEDIFF(YY, BIRTHDATE, GETDATE()) - CASE WHEN RIGHT(CONVERT(VARCHAR(6), GETDATE(), 12), 4) >= RIGHT(CONVERT(VARCHAR(6), BIRTHDATE, 12), 4) THEN 0 ELSE 1 END BETWEEN 1 AND 19 THEN '01-19'
WHEN DATEDIFF(YY, BIRTHDATE, GETDATE()) - CASE WHEN RIGHT(CONVERT(VARCHAR(6), GETDATE(), 12), 4) >= RIGHT(CONVERT(VARCHAR(6), BIRTHDATE, 12), 4) THEN 0 ELSE 1 END BETWEEN 20 AND 24 THEN '20-24'
WHEN DATEDIFF(YY, BIRTHDATE, GETDATE()) - CASE WHEN RIGHT(CONVERT(VARCHAR(6), GETDATE(), 12), 4) >= RIGHT(CONVERT(VARCHAR(6), BIRTHDATE, 12), 4) THEN 0 ELSE 1 END BETWEEN 25 AND 29 THEN '25-29'
WHEN DATEDIFF(YY, BIRTHDATE, GETDATE()) - CASE WHEN RIGHT(CONVERT(VARCHAR(6), GETDATE(), 12), 4) >= RIGHT(CONVERT(VARCHAR(6), BIRTHDATE, 12), 4) THEN 0 ELSE 1 END BETWEEN 30 AND 34 THEN '30-34'
WHEN DATEDIFF(YY, BIRTHDATE, GETDATE()) - CASE WHEN RIGHT(CONVERT(VARCHAR(6), GETDATE(), 12), 4) >= RIGHT(CONVERT(VARCHAR(6), BIRTHDATE, 12), 4) THEN 0 ELSE 1 END BETWEEN 35 AND 39 THEN '35-39'
WHEN DATEDIFF(YY, BIRTHDATE, GETDATE()) - CASE WHEN RIGHT(CONVERT(VARCHAR(6), GETDATE(), 12), 4) >= RIGHT(CONVERT(VARCHAR(6), BIRTHDATE, 12), 4) THEN 0 ELSE 1 END BETWEEN 40 AND 44 THEN '40-44'
WHEN DATEDIFF(YY, BIRTHDATE, GETDATE()) - CASE WHEN RIGHT(CONVERT(VARCHAR(6), GETDATE(), 12), 4) >= RIGHT(CONVERT(VARCHAR(6), BIRTHDATE, 12), 4) THEN 0 ELSE 1 END BETWEEN 45 AND 49 THEN '45-49'
wHEN DATEDIFF(YY, BIRTHDATE, GETDATE()) - CASE WHEN RIGHT(CONVERT(VARCHAR(6), GETDATE(), 12), 4) >= RIGHT(CONVERT(VARCHAR(6), BIRTHDATE, 12), 4) THEN 0 ELSE 1 END BETWEEN 50 AND 54 THEN '50-54'
WHEN DATEDIFF(YY, BIRTHDATE, GETDATE()) - CASE WHEN RIGHT(CONVERT(VARCHAR(6), GETDATE(), 12), 4) >= RIGHT(CONVERT(VARCHAR(6), BIRTHDATE, 12), 4) THEN 0 ELSE 1 END BETWEEN 54 AND 59 THEN '55-59'
WHEN DATEDIFF(YY, BIRTHDATE, GETDATE()) - CASE WHEN RIGHT(CONVERT(VARCHAR(6), GETDATE(), 12), 4) >= RIGHT(CONVERT(VARCHAR(6), BIRTHDATE, 12), 4) THEN 0 ELSE 1 END BETWEEN 60 AND 64 THEN '60-64'
WHEN DATEDIFF(YY, BIRTHDATE, GETDATE()) - CASE WHEN RIGHT(CONVERT(VARCHAR(6), GETDATE(), 12), 4) >= RIGHT(CONVERT(VARCHAR(6), BIRTHDATE, 12), 4) THEN 0 ELSE 1 END BETWEEN 65 AND 69 THEN '65-69'
WHEN DATEDIFF(YY, BIRTHDATE, GETDATE()) - CASE WHEN RIGHT(CONVERT(VARCHAR(6), GETDATE(), 12), 4) >= RIGHT(CONVERT(VARCHAR(6), BIRTHDATE, 12), 4) THEN 0 ELSE 1 END BETWEEN 70 AND 74 THEN '70-74'
ELSE '75 AND Older'
END AS age_group,
COUNT(*) AS no_employees
FROM
EMDEPEND INNER JOIN
BENEFIT ON EMDEPEND.EMPLOYEE = BENEFIT.EMPLOYEE INNER JOIN
R_PLAN ON BENEFIT.COMPANY = R_PLAN.COMPANY AND BENEFIT.PLAN_TYPE = R_PLAN.PLAN_TYPE AND BENEFIT.PLAN_CODE = R_PLAN.PLAN_CODE
WHERE (EMDEPEND.REL_CODE = 'SPOUSE')
AND (EMDEPEND.ACTIVE_FLAG = 'A')
AND ((BENEFIT.STOP_DATE = CONVERT(DATETIME, '1753-01-01 00:00:00', 102))
or (benefit.STOP_DATE) between @FirstDay and @LastDay)
GROUP BY
BENEFIT.PLAN_CODE,
R_PLAN.R_DESC,
CASE
WHEN DATEDIFF(YY, BIRTHDATE, GETDATE()) - CASE WHEN RIGHT(CONVERT(VARCHAR(6), GETDATE(), 12), 4) >= RIGHT(CONVERT(VARCHAR(6), BIRTHDATE, 12), 4) THEN 0 ELSE 1 END BETWEEN 1 AND 19 THEN '01-19'
WHEN DATEDIFF(YY, BIRTHDATE, GETDATE()) - CASE WHEN RIGHT(CONVERT(VARCHAR(6), GETDATE(), 12), 4) >= RIGHT(CONVERT(VARCHAR(6), BIRTHDATE, 12), 4) THEN 0 ELSE 1 END BETWEEN 20 AND 24 THEN '20-24'
WHEN DATEDIFF(YY, BIRTHDATE, GETDATE()) - CASE WHEN RIGHT(CONVERT(VARCHAR(6), GETDATE(), 12), 4) >= RIGHT(CONVERT(VARCHAR(6), BIRTHDATE, 12), 4) THEN 0 ELSE 1 END BETWEEN 25 AND 29 THEN '25-29'
WHEN DATEDIFF(YY, BIRTHDATE, GETDATE()) - CASE WHEN RIGHT(CONVERT(VARCHAR(6), GETDATE(), 12), 4) >= RIGHT(CONVERT(VARCHAR(6), BIRTHDATE, 12), 4) THEN 0 ELSE 1 END BETWEEN 30 AND 34 THEN '30-34'
WHEN DATEDIFF(YY, BIRTHDATE, GETDATE()) - CASE WHEN RIGHT(CONVERT(VARCHAR(6), GETDATE(), 12), 4) >= RIGHT(CONVERT(VARCHAR(6), BIRTHDATE, 12), 4) THEN 0 ELSE 1 END BETWEEN 35 AND 39 THEN '35-39'
WHEN DATEDIFF(YY, BIRTHDATE, GETDATE()) - CASE WHEN RIGHT(CONVERT(VARCHAR(6), GETDATE(), 12), 4) >= RIGHT(CONVERT(VARCHAR(6), BIRTHDATE, 12), 4) THEN 0 ELSE 1 END BETWEEN 40 AND 44 THEN '40-44'
WHEN DATEDIFF(YY, BIRTHDATE, GETDATE()) - CASE WHEN RIGHT(CONVERT(VARCHAR(6), GETDATE(), 12), 4) >= RIGHT(CONVERT(VARCHAR(6), BIRTHDATE, 12), 4) THEN 0 ELSE 1 END BETWEEN 45 AND 49 THEN '45-49'
wHEN DATEDIFF(YY, BIRTHDATE, GETDATE()) - CASE WHEN RIGHT(CONVERT(VARCHAR(6), GETDATE(), 12), 4) >= RIGHT(CONVERT(VARCHAR(6), BIRTHDATE, 12), 4) THEN 0 ELSE 1 END BETWEEN 50 AND 54 THEN '50-54'
WHEN DATEDIFF(YY, BIRTHDATE, GETDATE()) - CASE WHEN RIGHT(CONVERT(VARCHAR(6), GETDATE(), 12), 4) >= RIGHT(CONVERT(VARCHAR(6), BIRTHDATE, 12), 4) THEN 0 ELSE 1 END BETWEEN 54 AND 59 THEN '55-59'
WHEN DATEDIFF(YY, BIRTHDATE, GETDATE()) - CASE WHEN RIGHT(CONVERT(VARCHAR(6), GETDATE(), 12), 4) >= RIGHT(CONVERT(VARCHAR(6), BIRTHDATE, 12), 4) THEN 0 ELSE 1 END BETWEEN 60 AND 64 THEN '60-64'
WHEN DATEDIFF(YY, BIRTHDATE, GETDATE()) - CASE WHEN RIGHT(CONVERT(VARCHAR(6), GETDATE(), 12), 4) >= RIGHT(CONVERT(VARCHAR(6), BIRTHDATE, 12), 4) THEN 0 ELSE 1 END BETWEEN 65 AND 69 THEN '65-69'
WHEN DATEDIFF(YY, BIRTHDATE, GETDATE()) - CASE WHEN RIGHT(CONVERT(VARCHAR(6), GETDATE(), 12), 4) >= RIGHT(CONVERT(VARCHAR(6), BIRTHDATE, 12), 4) THEN 0 ELSE 1 END BETWEEN 70 AND 74 THEN '70-74'
ELSE '75 AND Older'
END

HAVING (BENEFIT.PLAN_CODE = 'DLS1')
ORDER BY
age_group

PLAN_CODE R_DESC COVG_AMT age_group no_employees
DLS1 Spouse Life Plan 820000.00 25-29 18
DLS1 Spouse Life Plan 4680000.00 30-34 81
DLS1 Spouse Life Plan 6450000.00 35-39 110
DLS1 Spouse Life Plan 7480000.00 40-44 129
DLS1 Spouse Life Plan 7540000.00 45-49 144
DLS1 Spouse Life Plan 4200000.00 50-54 91
DLS1 Spouse Life Plan 2815000.00 55-59 76
DLS1 Spouse Life Plan 1115000.00 60-64 35
DLS1 Spouse Life Plan 305000.00 65-69 7
DLS1 Spouse Life Plan 40000.00 70-74 4

gbritton · March 7, 2018, 1:05am

first off, stop doing this!

write your query like this to make it DRYer:

declare @d date = getdate(); -- avoid extra calls to getdate()
select ...

case when diffyy - iif(mmdd >= bdmmdd, 0, 1) between....        -- use iif instead of nested case
from ...
cross apply ( select              -- compute these once to make the main query shorter
          datediff(yy, birthdate, @d), 
          RIGHT(CONVERT(VARCHAR(6), @d, 12), 4),
          RIGHT(CONVERT(VARCHAR(6), BIRTHDATE, 12), 4)
  ) d(diffyy, mmdd, bdmmdd)

When you do that, the whole thing will be easier to read. chances are the problem will be easier to spot

harishgg1 · January 3, 2019, 2:16pm

hi

i know this topic is from long long time ago

i took a stab at it

.. this is not the final solution

i created sample data
please check my solution and provide
feedback

drop create data

use tempdb 
go 

drop table data
go 

create table data
(
BIRTHDATE DATE ,
birthdaterange varchar(100) null
)
go 

insert into data select '2008-09-07',null
insert into data select '1920-09-08',null
insert into data select '1996-09-08',null
go

SQL

SELECT birthdaterange = Replace(CASE 
                                  WHEN ( ( Year(Getdate()) * 10000 + 
                                           Month(Getdate()) * 100 + Day( 
                                           Getdate()) 
                                           - Year( 
                                                      birthdate) * 10000 - Month 
                                           ( 
                                           birthdate) * 100 - Day( 
                                           birthdate) ) / 
                                                10000 ) BETWEEN 0 AND 19 THEN 1 
                                  WHEN ( ( Year(Getdate()) * 10000 + 
                                           Month(Getdate()) * 100 + Day( 
                                           Getdate()) 
                                           - Year( 
                                                      birthdate) * 10000 - Month 
                                           ( 
                                           birthdate) * 100 - Day( 
                                           birthdate) ) / 
                                                10000 ) > 75 THEN 1 
                                  ELSE ( ( Year(Getdate()) * 10000 + 
                                           Month(Getdate()) * 100 + Day( 
                                           Getdate()) - Year( 
                                                      birthdate) * 10000 - Month 
                                           ( 
                                           birthdate) * 100 - Day( 
                                           birthdate) ) / 
                                                10000 ) 
                                END, 1, '0-19') 
FROM   data 

go

Result