Date Range - Weeks

Question

Date Range - Weeks

Ramesh Lende

Ten Centuries

Points: 1034
More actions
August 4, 2008 at 9:04 am

#190645

I have a database which has a field for record created on. There are records in this table for last 1 year on various dates. I want to write a query to get number of records created each week for last 1 year. Is there any simple way to create this other than temp tables and cursors?
e.g.
From ToCount
07/06/2008 07/12/200810
07/13/2008 07/19/200815
07/20/2008 07/26/200816
07/27/2008 08/02/200821
Thanks in advance for your help.

Viewing 4 posts - 1 through 3 (of 3 total)

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

antonio.collins SSCrazy Eights Points: 9600 More actions · Answer 1

group your data based on an anchor date and divide by 7. adjust the anchor date based on what you consider the first day of the week. for example, this query will group data based on a Mon-Sun week:

select (datediff( day, dateColumn, '8/3/2008' ) / 7) as grouping, min(dateColumn), max(dateColumn), count(*)

from [YourTable]

group by (datediff( day, dateColumn, '8/3/2008' ) / 7)

order by (datediff( day, dateColumn, '8/3/2008' ) / 7)

Ramesh Lende Ten Centuries Points: 1034 More actions · Answer 2

Ramesh Lende

Ten Centuries

Points: 1034

August 4, 2008 at 9:52 am

#852764

Thanks... it worked..

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

DROP TABLE #Dates

CREATE TABLE #Dates (CreateDate DATETIME)

INSERT INTO #Dates (CreateDate)

SELECT '01/01/2008' UNION ALL

SELECT '02/01/2008' UNION ALL

SELECT '03/01/2008' UNION ALL

SELECT '04/01/2008' UNION ALL

SELECT '05/01/2008' UNION ALL

SELECT '06/01/2008' UNION ALL

SELECT '07/01/2008' UNION ALL

SELECT '06/07/2008' UNION ALL

SELECT '12/07/2008' UNION ALL

SELECT '13/07/2008' UNION ALL

SELECT '14/07/2008' UNION ALL

SELECT '15/07/2008' UNION ALL

SELECT '19/07/2008' UNION ALL

SELECT '20/07/2008' UNION ALL

SELECT '26/07/2008' UNION ALL

SELECT '27/07/2008' UNION ALL

SELECT '02/08/2008'

-- week is sunday to saturday

SET DATEFIRST 7

DECLARE @AnchorDate DATETIME

SET @AnchorDate = '30/12/2007'

SELECT DATEPART(wk, CreateDate) AS WeekNumber,

DATEADD(day, (DATEPART(wk, CreateDate)-1)*7, @AnchorDate) AS StartRange,

DATEADD(day, ((DATEPART(wk, CreateDate)-1)*7)+6, @AnchorDate) AS EndRange,

COUNT(*) AS RowsForWeek

FROM #Dates

group by DATEPART(wk, CreateDate),

DATEADD(day, (DATEPART(wk, CreateDate)-1)*7, @AnchorDate),

DATEADD(day, ((DATEPART(wk, CreateDate)-1)*7)+6, @AnchorDate)

Results:

WeekNumber StartRange EndRange RowsForWeek

----------- ---------------------------------------------------------

1 2007-12-30 00:00:00.0002008-01-05 00:00:00.0005

2 2008-01-06 00:00:00.0002008-01-12 00:00:00.0002

28 2008-07-06 00:00:00.0002008-07-12 00:00:00.0002

29 2008-07-13 00:00:00.0002008-07-19 00:00:00.0004

30 2008-07-20 00:00:00.0002008-07-26 00:00:00.0002

31 2008-07-27 00:00:00.0002008-08-02 00:00:00.0002

(6 row(s) affected)

^{“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