Solved

sql query by weeks

Posted on 2015-02-05
8
98 Views
Last Modified: 2015-02-11
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
Comment
Question by:vbnetcoder
8 Comments
 
LVL 24

Expert Comment

by:Phillip Burton
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
Group by DATEADD(DAY,-weekday(DocumentCreatedDate,3),DocumentCreatedDate)

Open in new window

0
 
LVL 48

Expert Comment

by:Vitor Montalvão
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

Open in new window

0
 
LVL 1

Expert Comment

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

SELECT DATEPART(WEEK, DocumentCreatedDate), COUNT(1) AS NumOfDocs -- Or other aggregates
FROM [your table]
GROUP BY DATEPART(WEEK, DocumentCreatedDate)

Open in new window

0
Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

 
LVL 69

Expert Comment

by:Scott Pletcher
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

by:vbnetcoder
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 48

Expert Comment

by:PortletPaul
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 48

Accepted Solution

by:
Vitor Montalvão earned 500 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

Open in new window


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

by:vbnetcoder
ID: 40603923
ty
0

Featured Post

How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

Question has a verified solution.

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

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question