Tuesday, June 30, 2015

Apply Case statement in Where clause in SSMS

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 :-

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

Output: Will get whole one month(5) record for 2015 year.



No comments:

Post a Comment