Here i will explain, how can we apply Case statement in Where clause.
Look on the example given below.
I am having a Table called DIM_DATE.
This table contain 4 column (DATE, MONTH_OF_YEAR ,WEEK_OF_YEAR,YEAR)
Now look on the below query :-
Look on the example given below.
I am having a Table called DIM_DATE.
This table contain 4 column (DATE, MONTH_OF_YEAR ,WEEK_OF_YEAR,YEAR)
Now look on the below query :-
DECLARE @Rpt_Parm_Is_Week BIT
,@Rpt_Parm_Month INT
,@Rpt_Parm_Week INT
SELECT DATE,MONTH_OF_YEAR ,WEEK_OF_YEAR,YEAR FROM DIM_DATE
WHERE YEAR=2015
AND MONTH_OF_YEAR = CASE WHEN @Rpt_Parm_Is_Week = 1 THEN MONTH_OF_YEAR ELSE @Rpt_Parm_Month END
AND
WEEK_OF_YEAR =
CASE WHEN
@Rpt_Parm_Is_Week = 0 THEN
WEEK_OF_YEAR ELSE @Rpt_Parm_Week END
Now will pass some value into those parameter.
@Rpt_Parm_Is_Week = 1
@Rpt_Parm_Month = 5
@Rpt_Parm_Week=21
Output: Will get only one week(21) value for particular month(5) in 2015 year.
Now will pass fasle value into @Rpt_Parm_Is_Week parameter.
@Rpt_Parm_Is_Week = 0
@Rpt_Parm_Month = 5
@Rpt_Parm_Week=21
No comments:
Post a Comment