Solved

sql query by weeks

Posted on 2015-02-05
8
94 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 45

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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

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 45

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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Suggested Solutions

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

706 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

19 Experts available now in Live!

Get 1:1 Help Now