Friday, May 15, 2015

Week of Year Calculation (logic)

We know that in sql server we have a lib function for week of year calculation.
But we need to know the calculation behind that. Here is the actual calculation given below.

DECLARE @Inputdate DATE='2015-01-01'
DECLARE @ORDINAL INT
DECLARE @RESULT FLOAT

IF (datepart(yy,@Inputdate)%100=0 and datepart(yy,@Inputdate)%4=0)
      BEGIN
            SET @ORDINAL=
                CASE WHEN datepart(mm,@Inputdate)=1 then 0
                         WHEN datepart(mm,@Inputdate)=2 then 31
                         WHEN datepart(mm,@Inputdate)=3 then 60
                       WHEN datepart(mm,@Inputdate)=4 then 91
                       WHEN datepart(mm,@Inputdate)=5 then 121
                       WHEN datepart(mm,@Inputdate)=6 then 152
                       WHEN datepart(mm,@Inputdate)=7 then 182
                       WHEN datepart(mm,@Inputdate)=8 then 213
                       WHEN datepart(mm,@Inputdate)=9 then 244
                       WHEN datepart(mm,@Inputdate)=10 then 274
                       WHEN datepart(mm,@Inputdate)=11 then 305
                  ELSE 335 END
      SET @ORDINAL=@ORDINAL+datepart(DD,@Inputdate)
      SET @RESULT=(@ORDINAL-(datepart(DW,@Inputdate)-1)+10)/7
     
      SELECT CAST(@RESULT AS INT) WEEK_OF_YEAR
      END
ELSE
      BEGIN
      SET @ORDINAL=
               CASE WHEN datepart(mm,@Inputdate)=1 then 0
                        WHEN datepart(mm,@Inputdate)=2 then 31
                        WHEN datepart(mm,@Inputdate)=3 then 59
                        WHEN datepart(mm,@Inputdate)=4 then 90
                        WHEN datepart(mm,@Inputdate)=5 then 120
                        WHEN datepart(mm,@Inputdate)=6 then 151
                        WHEN datepart(mm,@Inputdate)=7 then 181
                        WHEN datepart(mm,@Inputdate)=8 then 212
                        WHEN datepart(mm,@Inputdate)=9 then 243
                        WHEN datepart(mm,@Inputdate)=10 then 273
                        WHEN datepart(mm,@Inputdate)=11 then 304
                  ELSE  334 END
      SET @ORDINAL=@ORDINAL+datepart(DD,@Inputdate)       
      SET @RESULT=(@ORDINAL-(datepart(DW,@Inputdate)-1)+10)/7
     
      SELECT CAST(@RESULT AS INT) WEEK_OF_YEAR

      END

No comments:

Post a Comment