Case statement

[teddysnow12]

Hi,

The following is my code,

select
sum(mms.plcy_holdr_mnth_cnt) as curre ,
'Enrolled Employees' as measure
from mbr.mbr_mnth_fact mms ,
mbr.mnth_dim mt,
mbr.PRDCT_dim_old pd ,
mbr.grp_structr_dim gsd ,
cmrcl_wlns.grp_dim@HM_LINK gd
where mt.yyyymm_cd = MMS.RPTG_YYYYMM
and MMS.PRIMRY_MEDCL_PRDCT_CD_HSN = PD.PRDCT_CD_HIST_SEQ_NBR
and gsd.grp_structr_hist_seq_nbr = mms.grp_structr_hist_seq_nbr
and gsd.MBR_GRP_NBR = gd.mbr_grp_nbr
and gd.ftn_grp_nme = '50 BELOW'
AND gd.ftn_nbr = '42958'
and mt.yyyymm_cd = '2011-03'
and pd.dw_funding_type_cd = 'F'
and pd.dw_bus_seg_cd = 'Mmkt'
AND PD.DW_MKT_SEG_CD = 'C'
AND GD.MBR_GRP_NBR = '46854'
group by
GD.mbr_grp_nbr


The RPTG_YYYYMM changes from 2011-03 to 2010-03 and rest of the code remains same.

I need the 'enrolled members' for 2011-03 and also 2010-03.

can anyone help me out in writing the case statement for this code.

Thanks in advance!

 

[PWise]

and mt.yyyymm_cd = '2011-03'

change this to

and mt.yyyymm_cd = in('2011-03','2010-03')

 

[teddysnow12]

Thanks for your response,

but I need them as 2 different results.
one for 2011-03 and one for 2010-03

 

[teddysnow12]

measure current prior

enrolled members 300 216




This is the way I want the result.

Here current represents the 2011-03 and
prior represents the 2010-03 ( 1 year prior)

 

[PWise]

sum(Case when mt.yyyymm_cd='2011-03' then 1 else 0 end) as curre,
sum(Case when mt.yyyymm_cd='2010-03' then 1 else 0 end) as prior

 

[teddysnow12]

Thank you,

It worked!

 

[teddysnow12]

I need help in writing the syntax for the mt.yyyymm_cd to go 1 year prior.

in the code I used static values like 2011-03 and 2010-03.

But I want as parameters which user can enter.

these are dates but they are in string format.

 

[Qik3Coder]

You need to cast that as a date, then substract a year, then trim the days off the end

Declare @_myDateParm varchar(10), @_myOtherDateParm varchar(10), @_myDate datetime, @_myOtherDate datetime
SELECT @_myDateParm = '2011-09'
Select @_myDate = cast(@myDateParm+'-01' as datetime)
Select @_myOtherDate = dateAdd(yy, -1, @myDate)
select @_myOtherDateParm = convert(varchar(7), @_myOtherDate, 120)
Select @_myDateParm, @myOtherDateParm

Lodlaiden

You've got questions and source code. We want both!