for each week - count items

Hello,
I have a table with a column of type datetime
I want to find how often a record is in this table per week.
example of the records:
Id=111, DateColumn= '09-12-2013'
Id=112, DateColumn= '10-12-2013'
Id=113, DateColumn= '10-12-2013'
Id=114, DateColumn= '16-12-2013'
Id=115, DateColumn= '17-12-2013'
Id=116, DateColumn= '24-12-2013'
Id=117, DateColumn= '26-12-2013'
Id=118, DateColumn= '26-12-2013'
Id=119, DateColumn= '27-12-2013'

I want to return all weeks from 01-01-2013 to 31-12-2013 and how often an event happens each week. I would like to return it like this:
...
...
StartDate: '08-12-2013', EndDate: '14-12-2013   Count:  3
StartDate: '15-12-2013', EndDate: '21-12-2013   Count:  2
StartDate: '22-12-2013', EndDate: '28-12-2013   Count:  4
,,.

How is best to do this?
johnson1Asked:
Who is Participating?
 
Surendra NathConnect With a Mentor Technology LeadCommented:
A simple solution is to do some thing like this

1. Create two variables @StartDate, @EndDate
2. First assingn @StartDate to be the starting week day of the month
3. Then @endDate will be @StartDate + 7 days
4. Now get the count of events between these two
5. Now assingn @startDate = @EndDate + 1 day
6. @EndDate = @StartDate + 7 days
7. The loop should continue until the year changes in the end date.

now, in the step 4. you can display if the count >0 if that is only your requirement or also display all weeks.
0
 
Jesus RodriguezConnect With a Mentor IT ManagerCommented:
SELECT DATEPART(WEEK,DATECOLUMN) AS WK_NUMBER
      ,DATEADD(dd, -(DATEPART(dw, DATECOLUMN)-1), DATECOLUMN) [WeekStart]
        ,DATEADD(dd, 7-(DATEPART(dw, DATECOLUMN)), DATECOLUMN) [WeekEnd]
      ,COUNT(ID) AS AMT
FROM TBL
GROUP BY DATEPART(WEEK,DATECOLUMN),DATEADD(dd, -(DATEPART(dw, DATECOLUMN)-1), DATECOLUMN),DATEADD(dd, 7-(DATEPART(dw, DATECOLUMN)), DATECOLUMN)
0
 
Scott PletcherConnect With a Mentor Senior DBACommented:
--create test data as specified in original q
IF OBJECT_ID('tempdb..#data_table') IS NOT NULL
    DROP TABLE #data_table
CREATE TABLE #data_table (
    Id int,
    DateColumn datetime
    )
SET DATEFORMAT dmy --standard at my geography is mdy, so I must override
INSERT INTO #data_table
SELECT 111, '09-12-2013' UNION ALL
SELECT 112, '10-12-2013' UNION ALL
SELECT 113, '10-12-2013' UNION ALL
SELECT 114, '16-12-2013' UNION ALL
SELECT 115, '17-12-2013' UNION ALL
SELECT 116, '24-12-2013' UNION ALL
SELECT 117, '26-12-2013' UNION ALL
SELECT 118, '26-12-2013' UNION ALL
SELECT 119, '27-12-2013'
SET DATEFORMAT mdy --change to match your standard DATEFORMAT

--set date range to list------------------------------------------------------------------------------------------------

DECLARE @start_date datetime
DECLARE @end_date datetime

SET @start_date = '20130101'
SET @end_date = '20131231' --YYYYMMDD is 100% safe format for all date settings

--actual processing code------------------------------------------------------------------------------------------------

DECLARE @start_date_adjusted_for_day_of_week datetime
--back @start_date up to previous Sunday (unless it is already Sunday);
--note that this code always works regardless of DATEFIRST and/or language settings in SQL.
SET @start_date_adjusted_for_day_of_week = DATEADD(DAY, DATEDIFF(DAY, 6, @start_date) / 7 * 7, 6)

;WITH
cteDigits AS (
    SELECT 0 AS digit UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL
    SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
),
-- use a cte and tally to generate all the weeks of the specified date range
cteWeeks AS (
    SELECT [10s].digit * 10 + [1s].digit AS week_num
    FROM cteDigits [1s]
    INNER JOIN cteDigits [10s] ON [10s].digit BETWEEN 0 AND 5
    WHERE
        [10s].digit * 10 + [1s].digit BETWEEN 0 AND 52
)
SELECT
    CASE WHEN week_num = 0 THEN @start_date ELSE DATEADD(WEEK, week_num, @start_date_adjusted_for_day_of_week) END AS StartDate,
    DATEADD(DAY, 6, DATEADD(WEEK, week_num, @start_date_adjusted_for_day_of_week)) AS EndDate,
    COUNT(dt.DateColumn) AS Count
FROM cteWeeks
LEFT OUTER JOIN #data_table dt ON
    dt.DateColumn >= @start_date AND
    dt.DateColumn >= DATEADD(WEEK, week_num, @start_date_adjusted_for_day_of_week) AND
    dt.DateColumn < DATEADD(WEEK, week_num + 1, @start_date_adjusted_for_day_of_week)
WHERE
    DATEADD(WEEK, week_num, @start_date_adjusted_for_day_of_week) <= @end_date
GROUP BY
    CASE WHEN week_num = 0 THEN @start_date ELSE DATEADD(WEEK, week_num, @start_date_adjusted_for_day_of_week) END,
    DATEADD(DAY, 6, DATEADD(WEEK, week_num, @start_date_adjusted_for_day_of_week))    
ORDER BY StartDate
0
 
johnson1Author Commented:
Thank you.
0
All Courses

From novice to tech pro — start learning today.