Simple query to return weeks within date range

Question

Post reply

Simple query to return weeks within date range

sirokinl

Old Hand

Points: 305
More actions
September 22, 2008 at 3:26 am

#193145

Hello all,

I have a question that might be very simple, but yet I have not been able to solve. Let me first explain the business case.

Our company has a budget amount of hours which are to be spent in a certain date range, so for example there is a budget of 40 manhours to be spent between january 1st 2008 and february 1st 2008. For simplicity sake, this means 40 hours in 4 weeks, that means 10 hours per week.

What I would like to return is a table that shows the following information:

200801 - 10

200802 - 10

200803 - 10

200804 - 10

Where 200802 for example is week 2 of 2008.

I did solve it through the use of a cursor, but this has serious performance issues. I would like to make one query that returns this information.

So my main question is: how do i return all individual weeks in rows between a certain date range.

Thanks in advance.

Kind regads,

Glen

Viewing 6 posts - 1 through 5 (of 5 total)

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

John H Marsh SSChampion Points: 11671 More actions · Answer 1

Hello,

Not sure if I've understood your question correctly, but using a standard "Between X and Y" in the Where clause gets the records of interest and then using a "Group By" with a DatePart(week, YourDateColumn) would subtotal the rows per week.

You could also use the DatePart function in the list of fields to return.

Is that what you need?

Regards,

John Marsh

www.sql.lu
SQL Server Luxembourg User Group

sirokinl Old Hand Points: 305 More actions · Answer 2

sirokinl

Old Hand

Points: 305

September 22, 2008 at 4:06 am

#874859

Well the thing is, the startdate and enddate are columns of one row.

One row looks like this : startdate...enddate...budget

John H Marsh SSChampion Points: 11671 More actions · Answer 3

Hello Glen,

I guess then Start and End dates of a single record extend across multiple weeks?

In that case, you could create a "Weeks" table containing the Start and End dates of each week, and then join on your table's Start and End dates (using logical "Betweens").

Regards,

John Marsh

www.sql.lu
SQL Server Luxembourg User Group

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

Hi Glen

You will probably find some useful material in the following post:http://qa.sqlservercentral.com/Forums/Topic570892-145-1.aspx

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

John H Marsh SSChampion Points: 11671 More actions · Answer 5

Hello Again,

Thinking about it, you could do the between on the Week-Number easier than the Start and End dates. You would also need to include the year though (please see below).

Regards,

John Marsh

Declare @tblWeek Table

(StartDate DateTime,

EndDate DateTime,

WeekNumber Int,

CalendarYear Int)

Insert Into @tblWeek

(StartDate, EndDate, WeekNumber, CalendarYear) Values ('14 Jan 2008', '20 Jan 2008', 3, 2008)

Insert Into @tblWeek

(StartDate, EndDate, WeekNumber, CalendarYear) Values ('21 Jan 2008', '27 Jan 2008', 4, 2008)

Select

DatePart(week, HoursStartDate) As StartWeek,

DatePart(year, HoursStartDate) As StartYear,

DatePart(week, HoursEndDate) As EndWeek,

DatePart(year, HoursEndDate) As EndYear,

tw.WeekNumber,

tw.CalendarYear

From

db.MyHoursTable dhc

Inner Join @tblWeek tw

On (tw.WeekNumber Between DatePart(week, HoursStartDate) And DatePart(week, HoursEndDate)) And

(tw.CalendarYear Between DatePart(year, HoursStartDate) And DatePart(year, HoursEndDate))

www.sql.lu
SQL Server Luxembourg User Group