sql query by weeks

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)

Select allOpen in new window

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

Select allOpen in new window

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)

Select allOpen in new window

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)

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

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
;

ty