none
Weekly report creating issue during end of year.(Combined two week in one week when last week is split in two.)

    Question

  • Hi all,

    I have one weekly report that show week wise data in column group.

    but now I'm getting the issue; like when the year will be end then it shows last week data in two groups .

    For Example;

    select Datepart(WK,(GETDATE()-14)),GETDATE()-14
    union all
    select Datepart(WK,(GETDATE()-13)),GETDATE()-13
    union all
    select Datepart(WK,(GETDATE()-12)),GETDATE()-12
    Union all
    select Datepart(WK,(GETDATE()-11)),GETDATE()-11
    union all
    select Datepart(WK,(GETDATE()-10)),GETDATE()-10
    WK	date
    53	2013-12-31 12:46:34.200
    53	2013-12-30 12:46:34.200
    1	2014-01-01 12:46:34.200
    1	2014-01-02 12:46:34.200
    1	2014-01-03 12:46:34.200

    current output :

    1		53	
    date		date	
    		12-30-2013 1:09:36 PM	
    		12-31-2013 1:09:36 PM	
    1-1-2014 1:09:36 PM			
    1-2-2014 1:09:36 PM			
    1-3-2014 1:09:36 PM			

    but i need   either below

    1
    date
    12-30-2013 1:09:36 PM
    12-31-2013 1:09:36 PM
    1-1-2014 1:09:36 PM
    1-2-2014 1:09:36 PM
    1-3-2014 1:09:36 PM

    or

    53	
    date	
    12-30-2013 1:09:36 PM	
    12-31-2013 1:09:36 PM	
    1-1-2014 1:09:36 PM	
    1-2-2014 1:09:36 PM	
    1-3-2014 1:09:36 PM	
    	
    


    i.e Combined two week in one week when last week is split in two.

    Kindly help.

    Thanks .



    Monday, January 13, 2014 7:21 AM

Answers

All replies

    • Edited by SaravanaC Monday, January 13, 2014 7:48 AM
    • Marked as answer by PrajapatiNeha Monday, January 13, 2014 2:56 PM
    Monday, January 13, 2014 7:47 AM
  • Many thanks for reply ..

    can we do something on report end using any code and something else.???

    Because I have to do this at Report end.

    Monday, January 13, 2014 8:10 AM
  • I have worked on it and found some correlation between the question and outlook calendar. I studied 100 years of Outlook Calendar. And found some observation:-

    1.  Some year end with 52 weeks and some with 53 week
    2. To Further explore it I found that year starting with Thursday are having 53 weeks and the leap years starting with Wednesday also consist of 53 weeks. The remaining years have 52 weeks.
    3. Then there was another observation, that the years starting on Monday, Tuesday and Wednesday have the first week as last week of the previous year & the years starting on Friday, Saturday and Sunday have the first week as first week of same year.
    4. In accordance with the point 3, we can work on calculating the last week of the year.

    You can use the following code to work on Weekday and Year calculation:

    ========================================

    Calculate week number and Year.

    ========================================

    case
    when
    (DATENAME(dw,DAtefromParts(Year(derivedtbl_1.TimeByDay),1,1)) = 'Thursday' or
         (DATENAME(dw,DAtefromParts(Year(derivedtbl_1.TimeByDay),1,1)) = 'Wednesday' AND
      (Year(derivedtbl_1.TimeByDay)%100)%4=0))
    then Year(derivedtbl_1.TimeByDay)
    else
     case when (DATEPart(week,derivedtbl_1.TimeByDay)>=2
      And DATEPart(week,derivedtbl_1.TimeByDay)<=52)
     then Year(derivedtbl_1.TimeByDay)
     else
     case
     when (DATEPart(week,derivedtbl_1.TimeByDay) =1)
     then case when (DATEPart(dw,DAtefromParts(Year(derivedtbl_1.TimeByDay),1,1))<5)
       then Year(derivedtbl_1.TimeByDay)
      
       else Year(derivedtbl_1.TimeByDay)-1
       end
     else
      case when (DATEPart(week,derivedtbl_1.TimeByDay) =53)
       then case when (DATEPart(dw,DAtefromParts(Year(derivedtbl_1.TimeByDay),1,1))<5)
         then Year(derivedtbl_1.TimeByDay)+1
         else Year(derivedtbl_1.TimeByDay)
         end
       end
     end
     end
    end AS Year,
     
    case
    when (DATENAME(dw,DAtefromParts(Year(derivedtbl_1.TimeByDay),1,1)) = 'Thursday' or
         (DATENAME(dw,DAtefromParts(Year(derivedtbl_1.TimeByDay),1,1)) = 'Wednesday' AND
      (Year(derivedtbl_1.TimeByDay)%100)%4=0))
    then
         DatePart(Week,derivedtbl_1.TimeByDay)

    else
         case when (DATEPart(week,derivedtbl_1.TimeByDay)>=2 And DATEPart(week,derivedtbl_1.TimeByDay)<=52)
     then DatePart(Week,derivedtbl_1.TimeByDay)-1
     else
            case 
            when DATEPart(week,derivedtbl_1.TimeByDay) =1
      then case when (DATEPart(dw,DAtefromParts(Year(derivedtbl_1.TimeByDay),1,1))<5)
            then DatePart(Week,derivedtbl_1.TimeByDay)
            else Case when (DATENAME(dw,DAtefromParts(Year(derivedtbl_1.TimeByDay)-1,1,1)) = 'Thursday' or
             (DATENAME(dw,DAtefromParts(Year(derivedtbl_1.TimeByDay)-1,1,1)) = 'Wednesday' AND
          ((Year(derivedtbl_1.TimeByDay)-1)%100)%4=0))
         then 53
         else 52
        end
        end
      else
       Case when (DATEPart(week,derivedtbl_1.TimeByDay) =53)
        then case when (DATEPart(dw,DAtefromParts(Year(derivedtbl_1.TimeByDay),1,1))<5)
         then 1
         else
          52
        end
                    
    end
    end
    end
    end
    AS Week,

    ========================================

    Calculate Year.

    ========================================


    case
    when
    (DATENAME(dw,DAtefromParts(Year(derivedtbl_1.TimeByDay),1,1)) = 'Thursday' or
         (DATENAME(dw,DAtefromParts(Year(derivedtbl_1.TimeByDay),1,1)) = 'Wednesday' AND
      (Year(derivedtbl_1.TimeByDay)%100)%4=0))
    then Year(derivedtbl_1.TimeByDay)
    else
     case when (DATEPart(week,derivedtbl_1.TimeByDay)>=2
      And DATEPart(week,derivedtbl_1.TimeByDay)<=52)
     then Year(derivedtbl_1.TimeByDay)
     else
     case
     when (DATEPart(week,derivedtbl_1.TimeByDay) =1)
     then case when (DATEPart(dw,DAtefromParts(Year(derivedtbl_1.TimeByDay),1,1))<5)
       then Year(derivedtbl_1.TimeByDay)
      
       else Year(derivedtbl_1.TimeByDay)-1
       end
     else
      case when (DATEPart(week,derivedtbl_1.TimeByDay) =53)
       then case when (DATEPart(dw,DAtefromParts(Year(derivedtbl_1.TimeByDay),1,1))<5)
         then Year(derivedtbl_1.TimeByDay)+1
         else Year(derivedtbl_1.TimeByDay)
         end
       end
     end
     end
    end

    ========================================

    Calculate week number.

    ========================================


    case
    when (DATENAME(dw,DAtefromParts(Year(derivedtbl_1.TimeByDay),1,1)) = 'Thursday' or
         (DATENAME(dw,DAtefromParts(Year(derivedtbl_1.TimeByDay),1,1)) = 'Wednesday' AND
      (Year(derivedtbl_1.TimeByDay)%100)%4=0))
    then
         DatePart(Week,derivedtbl_1.TimeByDay)

    else
         case when (DATEPart(week,derivedtbl_1.TimeByDay)>=2 And DATEPart(week,derivedtbl_1.TimeByDay)<=52)
     then DatePart(Week,derivedtbl_1.TimeByDay)-1
     else
            case 
            when DATEPart(week,derivedtbl_1.TimeByDay) =1
      then case when (DATEPart(dw,DAtefromParts(Year(derivedtbl_1.TimeByDay),1,1))<5)
            then DatePart(Week,derivedtbl_1.TimeByDay)
            else Case when (DATENAME(dw,DAtefromParts(Year(derivedtbl_1.TimeByDay)-1,1,1)) = 'Thursday' or
             (DATENAME(dw,DAtefromParts(Year(derivedtbl_1.TimeByDay)-1,1,1)) = 'Wednesday' AND
          ((Year(derivedtbl_1.TimeByDay)-1)%100)%4=0))
         then 53
         else 52
        end
        end
      else
       Case when (DATEPart(week,derivedtbl_1.TimeByDay) =53)
        then case when (DATEPart(dw,DAtefromParts(Year(derivedtbl_1.TimeByDay),1,1))<5)
         then 1
         else
          52
        end
                    
    end
    end
    end
    end

    Tuesday, March 08, 2016 9:06 AM