Week numbers and week start dates

Question

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.

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply

t.hitendra SSCommitted Points: 1620 More actions · Answer 1

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 · Answer 2

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 · Answer 3

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 · Answer 4

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

--Ta

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 5

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 · Answer 6

balars_2000

Right there with Babe

Points: 717

June 10, 2009 at 8:06 pm

#1007749

thanks jeff.

balars_2000 Right there with Babe Points: 717 More actions · Answer 7

balars_2000

Right there with Babe

Points: 717

June 10, 2009 at 8:07 pm

#1007750

hi hitendar

any comments?

cheers

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 8

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 · Answer 9

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 · Answer 10

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 · Answer 11

[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 · Answer 12

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 · Answer 13

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 · Answer 14

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)