Solved

# sql query by weeks

Posted on 2015-02-05
Medium Priority
102 Views
I have a table  called Document with the following fields

DocumentName
DocumentCreatedDate

I need to create a sql statement that will list the following

Week1 | Count of documents created that week
Week2 | Count of documents created that week
etc all the way to 52 weeks
0
Question by:vbnetcoder
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points

LVL 24

Expert Comment

ID: 40590841
Assuming you have SQL Server 2008R2 or later, and assuming that DocumentCreatedDate only has dates, and not time:

``````Select DATEADD(DAY,-weekday(DocumentCreatedDate,3),DocumentCreatedDate) as WeekStart,
Count(DocumentName) as NumberofDocuments
From Document
``````
0

LVL 51

Expert Comment

ID: 40590902
This query will return what you want for the current year. You can substitute the YEAR(GETDATE()) for any year that you wish:
``````SELECT DATEPART(week,DocumentCreationDate), COUNT(1) NumDocsCreated
FROM Document
WHERE YEAR(DocumentCreationDate)=YEAR(GETDATE())
GROUP BY DATEPART(week,DocumentCreationDate)
ORDER BY 1
``````
0

LVL 1

Expert Comment

ID: 40591242
Use your aggregates like you normally would, but group by week.

``````SELECT DATEPART(WEEK, DocumentCreatedDate), COUNT(1) AS NumOfDocs -- Or other aggregates
GROUP BY DATEPART(WEEK, DocumentCreatedDate)
``````
0

LVL 69

Expert Comment

ID: 40591698
Adjust the code below to match which day you want the "week" to start on.

DECLARE @start_date datetime
DECLARE @number_of_weeks int
DECLARE @week_start_day tinyint --1=Mon;2=Tues;...7=Sun.

SET @start_date = '20140101'
SET @number_of_weeks = 52
SET @week_start_day = 1 --Mon

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

--adjust @start_date to insure it matches @week_start_day
SET @start_date = DATEADD(DAY, -DATEDIFF(DAY, @week_start_day - 1, @start_date) % 7, @start_date)
SELECT @start_date

SELECT
DATEDIFF(DAY, @start_date, DocumentCreatedDate) / 7 AS week#,
COUNT(*) AS Document_Count,
CONVERT(varchar(10), DATEADD(DAY, DATEDIFF(DAY, @start_date, DocumentCreatedDate) / 7 * 7, @start_date), 101) AS Week_Start
FROM (
SELECT 'A' AS DocumentName, CAST('20140323' AS datetime) AS DocumentCreatedDate UNION ALL
SELECT 'B', '20140324' UNION ALL
SELECT 'C', '20140611'
) AS Document
WHERE
DocumentCreatedDate >= @start_date AND
DocumentCreatedDate < DATEADD(DAY, @number_of_weeks * 7 + 1, @start_date)
GROUP BY
DATEDIFF(DAY, @start_date, DocumentCreatedDate) / 7,
DATEADD(DAY, DATEDIFF(DAY, @start_date, DocumentCreatedDate) / 7 * 7, @start_date)
0

Author Comment

ID: 40591930
Victor,

How would i modifiy you code to show also months that have zero documents?

SELECT DATEPART(week,DocumentCreationDate), COUNT(1) NumDocsCreated
FROM Document
WHERE YEAR(DocumentCreationDate)=YEAR(GETDATE())
GROUP BY DATEPART(week,DocumentCreationDate)
ORDER BY 1
0

LVL 49

Expert Comment

ID: 40592300
To list rows for weeks when there are no documents for that week requires a list of the weeks then using an outer join to the documents. Below I have used a simple recursive CTE for the 52 rows as an example:

DECLARE @start_date datetime
DECLARE @number_of_weeks int

SET @start_date = '20140101'
SET @number_of_weeks = 52

;WITH W52 (wknum, yrnum)
AS (
SELECT
1 AS WKNUM
, YEAR(@start_date) AS YRNUM
UNION ALL
SELECT
wknum + 1
, YEAR(@start_date)
FROM w52
WHERE wknum < @number_of_weeks
)

SELECT
W52.wknum
, W52.yrnum
, COUNT(D.DocumentCreatedDate) AS DOCUMENT_COUNT
FROM W52
LEFT JOIN Document D ON W52.wknum = DATEPART(week, d.DocumentCreatedDate)
AND W52.yrnum = YEAR(D.DocumentCreatedDate)
GROUP BY
W52.wknum
, W52.yrnum
;
0

LVL 51

Accepted Solution

Vitor Montalvão earned 2000 total points
ID: 40593253
@vbnetcoder:
For that you need to create a table and populate all weeks of the year so you can join with the documents. Try the following code (I didn't test it):
``````CREATE TABLE #WeekYear
(WeekNumber INT)

;WITH Nbrs_3( n ) AS ( SELECT 1 UNION SELECT 0 ),
Nbrs_2( n ) AS ( SELECT 1 FROM Nbrs_3 n1 CROSS JOIN Nbrs_3 n2 ),
Nbrs_1( n ) AS ( SELECT 1 FROM Nbrs_2 n1 CROSS JOIN Nbrs_2 n2 ),
Nbrs_0( n ) AS ( SELECT 1 FROM Nbrs_1 n1 CROSS JOIN Nbrs_1 n2 ),
Nbrs ( n ) AS ( SELECT 1 FROM Nbrs_0 n1 CROSS JOIN Nbrs_0 n2 )
INSERT INTO #WeekYear
SELECT n
FROM ( SELECT ROW_NUMBER() OVER (ORDER BY n)
FROM Nbrs ) D ( n )
WHERE n <= 52 ; -- number of weeks

SELECT #WeekYear.WeekNumber, COUNT(1) NumDocsCreated
FROM #WeekYear
LEFT JOIN Document ON #WeekYear.WeekNumber = DATEPART(week,DocumentCreationDate)
WHERE YEAR(DocumentCreationDate)=YEAR(GETDATE())
GROUP BY #WeekYear.WeekNumber
ORDER BY #WeekYear.WeekNumber

DROP TABLE #WeekYear
``````

NOTE: Credits for the number generator code should go to Itzik Ben-Gan: http://www.projectdmx.com/tsql/tblnumbers.aspx#Recur
0

Author Closing Comment

ID: 40603923
ty
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
###### Suggested Courses
Course of the Month9 days, 19 hours left to enroll