Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Number of weeks between two dates Expand / Collapse
Author
Message
Posted Wednesday, January 5, 2011 3:52 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, September 8, 2014 11:34 AM
Points: 218, Visits: 712
.
Post #1043396
Posted Wednesday, January 5, 2011 4:23 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 5:17 PM
Points: 1,787, Visits: 5,728
Does this help?

Basically, the idea is to have another column that counts the weeks between two dates.


--= test data

DECLARE @t table(theDate smalldatetime, FinYear smallint, FinPer tinyint, FinWeek tinyint) ;

set nocount on ;

insert @t values('2009-12-27', 2010, 1, 1) ;
insert @t values('2009-12-28', 2010, 1, 1) ;
insert @t values('2009-12-29', 2010, 1, 1) ;
insert @t values('2010-01-19', 2010, 1, 4) ;
insert @t values('2010-01-20', 2010, 1, 4) ;
insert @t values('2010-02-18', 2010, 2, 8) ;
insert @t values('2010-02-19', 2010, 2, 8) ;
insert @t values('2010-03-25', 2010, 3, 13) ;
insert @t values('2010-03-26', 2010, 3, 13) ;
insert @t values('2010-04-30', 2010, 4, 18) ;
insert @t values('2010-05-01', 2010, 4, 18) ;
insert @t values('2010-05-28', 2010, 5, 22) ;
insert @t values('2010-05-29', 2010, 5, 22) ;
insert @t values('2010-06-28', 2010, 7, 27) ;
insert @t values('2010-06-29', 2010, 7, 27) ;
insert @t values('2010-08-27', 2010, 8, 35) ;
insert @t values('2010-08-28', 2010, 8, 35) ;
insert @t values('2010-09-15', 2010, 9, 38) ;
insert @t values('2010-09-16', 2010, 9, 38) ;
insert @t values('2010-09-29', 2010, 10, 40) ;
insert @t values('2010-09-30', 2010, 10, 40) ;
insert @t values('2010-12-16', 2010, 12, 51) ;
insert @t values('2010-12-17', 2010, 12, 51) ;
insert @t values('2010-12-18', 2010, 12, 51) ;
insert @t values('2010-12-19', 2010, 12, 52) ;
insert @t values('2010-12-20', 2010, 12, 52) ;
insert @t values('2010-12-26', 2011, 1, 1) ;
insert @t values('2010-12-27', 2011, 1, 1) ;
insert @t values('2010-12-28', 2011, 1, 1) ;
insert @t values('2011-03-03', 2011, 3, 10) ;
insert @t values('2011-04-03', 2011, 4, 15) ;
insert @t values('2011-05-05', 2011, 5, 19) ;
insert @t values('2011-05-28', 2011, 5, 22) ;
insert @t values('2011-06-18', 2011, 6, 25) ;
insert @t values('2011-06-19', 2011, 6, 26) ;
insert @t values('2011-07-10', 2011, 7, 29) ;
insert @t values('2011-08-08', 2011, 8, 33) ;
insert @t values('2011-08-29', 2011, 9, 36) ;
insert @t values('2011-10-08', 2011, 10, 41) ;
insert @t values('2011-11-21', 2011, 11, 48) ;
insert @t values('2011-11-22', 2011, 11, 48) ;
insert @t values('2011-12-27', 2011, 12, 53) ;
insert @t values('2011-12-28', 2011, 12, 53) ;
insert @t values('2011-12-29', 2011, 12, 53) ;
insert @t values('2011-12-30', 2011, 12, 53) ;
insert @t values('2011-12-31', 2011, 12, 53) ;

--= sample for dates 2010-12-18 and 2011-12-31 -
--= unfortunately the answer is wrong because all dates are not present between, but the idea works

select max(WeekNum) from (select DENSE_RANK() OVER(ORDER BY FinYear,FinPer,FinWeek) as WeekNum
from @t
where theDate BETWEEN '2010-12-18' AND '2011-12-31') as a


You could add a column like this to the table itself and just pull the difference between the two values by date to save some io cost.


MM


  • MMGrid Addin
  • MMNose Addin


  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • Post #1043407
    Posted Wednesday, January 5, 2011 4:41 PM
    SSC Veteran

    SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

    Group: General Forum Members
    Last Login: Monday, September 8, 2014 11:34 AM
    Points: 218, Visits: 712
    This is very interesting. But I will need to run this in two steps, since the date prior the current date need to be negative and after positive.

    How I can apply this to the whole table. This give me only the information between tow date, but I need to add a new column to my table that will host these values.

    Thanks a lot
    Post #1043413
    Posted Wednesday, January 5, 2011 5:13 PM


    SSCommitted

    SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

    Group: General Forum Members
    Last Login: Today @ 5:17 PM
    Points: 1,787, Visits: 5,728
    Add a new column to your table e.g. RollingWeek smallint

    update yourTable set RollingWeek=FinWeek where FinYear=2010

    update yourTable set RollingWeek=FinWeek + 52 where FinYear=2011

    update yourTable set RollingWeek=FinWeek + 105 where FinYear=2012


    etc etc etc

    then query for the difference between two dates :

    e.g.


    --= find weeks between 19th november 2010 and today 5th Jan 2011
    select b.RollingWeek - a.RollingWeek as WeekDiff
    from yourTable a, yourTable b
    where a.theDate = '2010-11-19' AND b.theDate = '2011-01-05'



    MM


  • MMGrid Addin
  • MMNose Addin


  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • Post #1043423
    Posted Thursday, January 6, 2011 7:06 AM
    SSC Veteran

    SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

    Group: General Forum Members
    Last Login: Monday, September 8, 2014 11:34 AM
    Points: 218, Visits: 712
    This seems to be a good approach, but some year have 52 weeks when some other have 53.

    What I need to do it's adding a new volume to my table with the difference of number of weeks between the current date and the past and future date.

    Post #1043725
    Posted Thursday, January 6, 2011 7:50 AM


    SSCommitted

    SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

    Group: General Forum Members
    Last Login: Today @ 5:17 PM
    Points: 1,787, Visits: 5,728
    Rem70Rem (1/6/2011)
    This seems to be a good approach, but some year have 52 weeks when some other have 53.

    Yes, you would have to deal with that manually when populating the RollingWeek column as in my example - there is no magic bullet for that unless you have a formula that can calculate whether there would be 52 or 53 weeks....

    Rem70Rem (1/6/2011)

    What I need to do it's adding a new volume to my table with the difference of number of weeks between the current date and the past and future date.



    I don't know what you mean, sorry!


    MM


  • MMGrid Addin
  • MMNose Addin


  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • Post #1043770
    Posted Thursday, January 6, 2011 8:17 AM
    Valued Member

    Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

    Group: General Forum Members
    Last Login: Friday, May 16, 2014 12:54 PM
    Points: 53, Visits: 231
    mister.magoo (1/6/2011)
    [quote]Rem70Rem (1/6/2011)
    This seems to be a good approach, but some year have 52 weeks when some other have 53.



    The DATEPART function has a WEEK datepart that displays the week of the year. You should be able to use this to dislpay the weeks of the year, if you use it with some custom code to account for
    (1) your financial year's week 1,
    (2) your financial week's starting day (e.g., does the week always begin on a Sunday?), and
    (3) which year has 53 weeks instead of 52. While DATEPART(wk,...) takes into account years with 53 weeks, you need to make sure that this aligns with your financial calendar.

    Disregarding all of that for the moment, a simple example of finding the week number for any given date would be:

    SELECT DATEPART(WK,GETDATE())

    And, to find the difference in weeks between two dates:

    SELECT DATEDIFF ( WK , '1-1-2010', GETDATE())

    BOL for DatePart: http://msdn.microsoft.com/en-us/library/ms174420.aspx
    BOL for DateDiff http://msdn.microsoft.com/en-us/library/ms189794.aspx

    -Jen
    Post #1043790
    Posted Thursday, January 6, 2011 8:35 AM


    SSCertifiable

    SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

    Group: General Forum Members
    Last Login: Sunday, September 7, 2014 11:27 PM
    Points: 7,164, Visits: 13,257
    JenMidnightDBA (1/6/2011)
    mister.magoo (1/6/2011)
    [quote]Rem70Rem (1/6/2011)
    This seems to be a good approach, but some year have 52 weeks when some other have 53.



    The DATEPART function has a WEEK datepart that displays the week of the year. You should be able to use this to dislpay the weeks of the year, if you use it with some custom code to account for
    (1) your financial year's week 1,
    (2) your financial week's starting day (e.g., does the week always begin on a Sunday?), and
    (3) which year has 53 weeks instead of 52. While DATEPART(wk,...) takes into account years with 53 weeks, you need to make sure that this aligns with your financial calendar.

    Disregarding all of that for the moment, a simple example of finding the week number for any given date would be:

    SELECT DATEPART(WK,GETDATE())

    And, to find the difference in weeks between two dates:

    SELECT DATEDIFF ( WK , '1-1-2010', GETDATE())

    BOL for DatePart: http://msdn.microsoft.com/en-us/library/ms174420.aspx
    BOL for DateDiff http://msdn.microsoft.com/en-us/library/ms189794.aspx

    -Jen


    But be careful! This function does NOT return a week number as per ISO standard! (it will start with week 1 for Jan 1st regardless if this week has more than 4 days or not)




    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question
    How to post performance related questions
    Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
    Post #1043810
    Posted Friday, January 7, 2011 2:46 AM
    Forum Newbie

    Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

    Group: General Forum Members
    Last Login: Wednesday, June 29, 2011 1:23 AM
    Points: 1, Visits: 11
    SELECT DATEDIFF(dd,'2010-APR-01','2010-DEC-01') / 7

    OR

    SELECT DATEDIFF(wk,'2010-APR-01','2010-DEC-01')

    Is this the question?
    Post #1044242
    Posted Friday, January 7, 2011 9:54 AM


    SSCommitted

    SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

    Group: General Forum Members
    Last Login: Thursday, August 28, 2014 8:53 PM
    Points: 1,521, Visits: 3,039
    Rem70Rem (1/5/2011)

    Edited: Yesterday @ 10:38:18 AM by Rem70Rem



    Uh, what was the question?

    Seriously, though, please restate the starting point so that others can learn from the discussion. That way folks wouldn't have to work out whatever the problem is that is beyond simply using a DateDiff() function.
    Post #1044527
    « Prev Topic | Next Topic »

    Add to briefcase 12»»

    Permissions Expand / Collapse