Week numbers and week start dates

• balars_2000

  Right there with Babe

  Points: 717

  More actions

  June 9, 2009 at 12:14 am

  #99705

  Hi Folks,

  Can you help me out with this?

  I have a report that displays the week # and start of the week date and sums up revenue collected for the period.

  For example, if I choose current year, Week # would start as 1 and start of the week should be 5/01 (first Monday of the year) and so on till current week which is 24 and week date is 08/06.

  Thanks.

• t.hitendra

  SSCommitted

  Points: 1620

  More actions

  June 9, 2009 at 12:58 am

  #1006475

  Hi there,

  I have tried to write a query for your problem with this sample table, tblPurchaseOrder - PurchaseOrderDate & GrandTotal

  here it is-

  select weekno,newdate,sum(grandtotal) as grandtotal from

  (select datepart(wk,purchaseorderdate) as weekno

  ,dateadd(dd,0,datediff(dd,0,purchaseorderdate-(datepart(weekday,purchaseorderdate)-1)+1)) as newdate,grandtotal

  from tblpurchaseorder where purchaseorderdate<=getdate()) as tbl group by weekno,newdate

  Here I have subtracted 1 from (weekday,purchaseorderdate) to

  make it start from monday (default for monday will be 2).

  and the whole equation gives monday of that week.

  please try it and tell me if that is what you need.

• balars_2000

  Right there with Babe

  Points: 717

  More actions

  June 9, 2009 at 7:26 pm

  #1007150

  hi mate

  thanks for ur response; appreciate it.

  sorry if i wasnt clear, i need week numbers and week start dates for any given date range or a year. if it was a year, for example 2009, the output would be

  week # week start date

  1 05/01

  2 12/01

  3 19/01

  and so on till current date. if it was for a date range for example between 19/01 and 2/02 (for 2009) then it should be

  week # week start date

  1 19/01

  2 26/01

  3 02/02

  thanks again for ur time 🙂

• Jeff Moden

  SSC Guru

  Points: 1004356

  More actions

  June 9, 2009 at 8:56 pm

  #1007165

  balars_2000 (6/9/2009)

  ---

  hi mate

  thanks for ur response; appreciate it.

  sorry if i wasnt clear, i need week numbers and week start dates for any given date range or a year. if it was a year, for example 2009, the output would be

  week # week start date

  1 05/01

  2 12/01

  3 19/01

  and so on till current date. if it was for a date range for example between 19/01 and 2/02 (for 2009) then it should be

  week # week start date

  1 19/01

  2 26/01

  3 02/02

  thanks again for ur time 🙂

  What do you want if data is missing for a whole week during the year?

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• balars_2000

  Right there with Babe

  Points: 717

  More actions

  June 9, 2009 at 9:33 pm

  #1007175

  I would display 0's to indicate there was no data during that particular week.

  --Ta

• Jeff Moden

  SSC Guru

  Points: 1004356

  More actions

  June 9, 2009 at 10:34 pm

  #1007184

  balars_2000 (6/9/2009)

  ---

  I would display 0's to indicate there was no data during that particular week.

  --Ta

  Then you need a complete list of dates to join against for any given year...

  [font="Courier New"]DECLARE @FirstDate DATETIME

   SELECT @FirstDate = '20090104'

   SELECT v.Number AS Week,

          DATEADD(wk, v.Number   ,@FirstDate) AS StartDate,

          DATEADD(wk, v.Number+1 ,@FirstDate) AS NextDate

     FROM Master.dbo.spt_Values v

    WHERE Type = 'P' 

      AND Number BETWEEN 0 AND 51[/font]

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• balars_2000

  Right there with Babe

  Points: 717

  More actions

  June 10, 2009 at 8:06 pm

  #1007749

  thanks jeff.

• balars_2000

  Right there with Babe

  Points: 717

  More actions

  June 10, 2009 at 8:07 pm

  #1007750

  hi hitendar

  any comments?

  cheers

• Jeff Moden

  SSC Guru

  Points: 1004356

  More actions

  June 10, 2009 at 8:39 pm

  #1007755

  balars_2000 (6/10/2009)

  ---

  thanks jeff.

  You bet. Thanks for the feedback. So, are you all set or do you have a remaining question?

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• balars_2000

  Right there with Babe

  Points: 717

  More actions

  June 10, 2009 at 11:38 pm

  #1007788

  Jeff, I have a problem.

  The query is working fine and listing all week numbers and dates.

  However, I am interested in week day starting Mondays which is 05/01 for current year and subsequently 12/01, 19/01 etc..

  Alternatively I should also be able to specify a date range and get the week start from there as well.

  I have posted in detail in my previous messages.

  Thanks for asking 🙂

  Cheers

• balars_2000

  Right there with Babe

  Points: 717

  More actions

  June 11, 2009 at 6:57 pm

  #1008335

  Thanks Hitendar n Jeff for your inputs.

  I have modified my query to my needs:

  SET DATEFIRST 1 --Monday to Sunday week

  DECLARE @FirstDate DATETIME

  /* For any year start add Jan 1 + 7 to get the next week start date

  and find out the beginning of the week starting Monday */

  SELECT @FirstDate = DATEADD(DD, 1 - DATEPART(DW, '08-Jan-09'), '08-Jan-09')

  SELECT v.Number AS Week,

  DATEADD(wk, v.Number ,@FirstDate) AS StartDate,

  DATEADD(wk, v.Number+1 ,@FirstDate) AS NextDate

  FROM Master.dbo.spt_Values v

  WHERE Type = 'P'

  AND Number BETWEEN 0 AND 51

• Bruce W Cassidy

  SSChampion

  Points: 12668

  More actions

  June 11, 2009 at 8:33 pm

  #1008357

  [font="Verdana"]This is the sort of thing I use a Calendar table for. I build a permanent table with the list of dates for the year (or next ten years) and put things like week numbers, month numbers, financial periods and so on against the dates.

  Makes it easier going forward.

  [/font]

• Jeff Moden

  SSC Guru

  Points: 1004356

  More actions

  June 11, 2009 at 9:00 pm

  #1008362

  balars_2000 (6/10/2009)

  ---

  Jeff, I have a problem.

  The query is working fine and listing all week numbers and dates.

  However, I am interested in week day starting Mondays which is 05/01 for current year and subsequently 12/01, 19/01 etc..

  Alternatively I should also be able to specify a date range and get the week start from there as well.

  I have posted in detail in my previous messages.

  Thanks for asking 🙂

  Cheers

  So change the date in the code I posted. 😛

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• balars_2000

  Right there with Babe

  Points: 717

  More actions

  June 11, 2009 at 10:22 pm

  #1008381

  Thanks Jeff.

  How can I avoid a cross join for the following query?

  SELECT a.Week, a.StartDate,

  CASE WHEN purchasedate BETWEEN a.startdate AND a.nextdate THEN 'Yes' ELSE 'No' END AS [Purchase Done]

  FROM dbo.tblPurchase CROSS JOIN

  (SELECT number AS Week, DATEADD(wk, number, DATEADD(DD, 1 - DATEPART(DW, '08-Jan-09'), '08-Jan-09')) AS StartDate, DATEADD(wk,

  number + 1, DATEADD(DD, 1 - DATEPART(DW, '08-Jan-09'), '08-Jan-09')) AS NextDate

  FROM master.dbo.spt_values AS v

  WHERE (type = 'P') AND (number BETWEEN 0 AND 51)) AS a

  All I am trying to get is, for all the weeks starting on Mondays, I am trying to figure out if a purchase has been made during that week.

  The outer query returns the week numbers and start week of the dates for this year.

  But since it is a cross join, it is returning a cartesian product and I just want week numbers, week start date and whether or not a purchase has been made.

  The output should be something like

  Week # Week Start Purchase Done

  1 05/01

• Jeff Moden

  SSC Guru

  Points: 1004356

  More actions

  June 12, 2009 at 12:33 pm

  #1008851

  First, do not use BETEEN for this. It will give the incorrect answer.

  Second, the following code is totally untested because you've provided no test data. If you're interested, see the article at the link in my signature below for how to do that correctly.

  Here's the code...

  [font="Courier New"] SELECT a.Week AS [Week #], 

          CAST(CHAR(5),a.StartDate,101) AS [Week Start],

          CASE 

              WHEN p.PurchaseDate IS NOT NULL

              THEN 'Yes' 

              ELSE 'No' 

          END AS [Purchase Done]

     FROM dbo.tblPurchase p

    RIGHT OUTER JOIN

          (

           SELECT v.Number + 1 AS Week, 

                  DATEADD(wk, v.Number    + DATEDIFF(wk,-1,'08-Jan-09'), -1) AS StartDate, 

                  DATEADD(wk, v.Number +1 + DATEDIFF(wk,-1,'08-Jan-09'), -1) AS NextDate

             FROM Master.dbo.spt_Values AS v

            WHERE v.Type = 'P' 

              AND v.Number BETWEEN 0 AND 51

          ) a

       ON p.PurchaseDate >= a.StartDate 

      AND p.PurchaseDate  < a.Nextdate 

  [/font]

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)