sql - Updating a Script -

sql - Updating a Script -

a while created script sums number of employees have , number of terms have. rather simple when used "active" , "withdrawn" status. have updated status include "continuous loa" , "retiree" additional status.

what need know how update below script include these new parameters. continuous loa fall active , retiree fall withdrawn.

merge dbo.hris_hc_sumtotals tgt using ( select datename(year, getdate()) [year], sum(case when empstatusname = 'active' 1 else 0 end) historicalhc, sum(case when empstatusname = 'withdrawn' , termyear = datename(year, getdate()) 1 else 0 end) numboftermee dbo.hris_hc_mstrtbl changestatus = 'current' , empstatusname in ('active', 'withdrawn') ) src on src.[year] = tgt.[year] when matched update set tgt.historicalhc = src.historicalhc, tgt.numbtermedemp = src.numboftermee when not matched target insert ( [year], historicalhc, numbtermedemp ) values ( src.[year], src.historicalhc, src.numboftermee );

can't alter 2 lines of code 'in' statements:

sum(case when empstatusname in ('active','continuous loa') 1 else 0 end) historicalhc, sum(case when empstatusname in ('withdrawn','retiree') , termyear = datename(year, getdate()) 1 else 0 end) numboftermee

and alter statement

and empstatusname in ('active', 'withdrawn', 'continuous loa','retiree')

sql tsql