Solved

sql query by weeks

Posted on 2015-02-05
8
95 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 46

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
 
LVL 69

Expert Comment

by:ScottPletcher
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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 

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 46

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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

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 we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…
Internet Business Fax to Email Made Easy - With  eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, f…

911 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now