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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jesus RodriguezIT 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)
Scott PletcherSenior 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
Surendra NathTechnology 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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
johnson1Author Commented:
Thank you.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.