Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

for each week - count items

Posted on 2013-12-24
4
Medium Priority
?
394 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 668 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 70

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 664 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 668 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

Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

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

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…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

705 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