Solved

for each week - count items

Posted on 2013-12-24
4
389 Views
Last Modified: 2013-12-26
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?
0
Comment
Question by:johnson1
[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
4 Comments
 
LVL 13

Assisted Solution

by:Jesus Rodriguez
Jesus Rodriguez earned 167 total points
ID: 39738274
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
 
LVL 69

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 166 total points
ID: 39738529
--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
 
LVL 16

Accepted Solution

by:
Surendra Nath earned 167 total points
ID: 39738664
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
 

Author Closing Comment

by:johnson1
ID: 39739820
Thank you.
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
union query column need default text 2 22
SQL Server Express or Standard? 5 63
How do I partition this table on date? 5 54
SQL Server how to create a DYNAMIC TABLE? 11 51
Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…

738 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