Solved

sql query by weeks

Posted on 2015-02-05
8
99 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
[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
  • Learn & ask questions
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 49

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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
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 49

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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

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.
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…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

733 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