Solved

for each week - count items

Posted on 2013-12-24
4
387 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
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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SSRS  - Dropdown with Null 3 33
Present Absent from working date rage 11 48
shrink table after huge delete 2 27
selective rebuild of SQL Tables in scheduled job 10 36
This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

679 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