none
SQL - Count vents by week on 2 differents years

    Question

  • Hello everybody,

    In my application, I use a SQL query that counts a number of events on a specific period (refer to this post : http://social.technet.microsoft.com/Forums/en-US/00282c24-0eb5-457e-9c00-61508d79dbe3/how-to-count-events-by-week-on-a-specific-period-?forum=transactsql ).

    I have a table named superweeks that contains only one column (an interger) and a record for each week number of the year (from 1 to 52). If I run the following query to list all week numbers from December 1st to December 31th :

    SELECT * FROM superweeks sw
    WHERE
     sw.numweek BETWEEN (DATEPART(week, '2013-12-01')) AND (DATEPART(week, '2013-12-31'))

    Here is the result :

    numweek
    49
    50
    51
    52
    53

    But if I want to do the same thing from a day of this year to the day of the next year (from December 2013 1st to January 2014 31th)

    SELECT * FROM superweeks sw
    WHERE
     sw.numweek BETWEEN (DATEPART(week, '2013-12-01')) AND (DATEPART(week, '2014-01-31'))

    I have no result. How can I have all week numbers for a specific period with a start date and a end date on two differents years ?

    Thanks in advance for your suggestions and have a great week-end.

    Saturday, November 02, 2013 12:51 PM

Answers

  • Why do you treat SQL like a computational language? It is a database language that uses tables and predicates. 

    I would build a calendar table with a ISO-8601 week-within-year format column in addition to the usual Common Era date. This format is 'yyyyWww-d' where yyyy is the year, W is a separator token, ww is (01-53) week number and d is (1-7) day of the week.

    You input any calendar date, find the week-within-year column and return the dates that match on a LIKE predicate.
    WHERE sale_day LIKE '2012W26-[67]'

    There are several websites with calendars you can cut & paste, but you can start your search with: http://www.calendar-365.com/week-number.html 

    CREATE TABLE Calendar
    (cal_date DATE NOT NULL PRIMARY KEY,
     week_date CHAR(10) NOT NULL
       CHECK (week_date LIKE '[12][0-9][0-9][0-9]W[0-5][0-9]-[1-7]')
    );

    Your sample dates would be:

    INSERT INTO Calendar
    VALUES 
    ('2013-12-01', '2013W49-7'),
    ('2014-01-31', '2014W06-5');

    Use the Substring function to get the week numbers. 


    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    • Marked as answer by NicolasC Sunday, November 03, 2013 10:44 AM
    Saturday, November 02, 2013 1:23 PM

All replies

  • Why do you treat SQL like a computational language? It is a database language that uses tables and predicates. 

    I would build a calendar table with a ISO-8601 week-within-year format column in addition to the usual Common Era date. This format is 'yyyyWww-d' where yyyy is the year, W is a separator token, ww is (01-53) week number and d is (1-7) day of the week.

    You input any calendar date, find the week-within-year column and return the dates that match on a LIKE predicate.
    WHERE sale_day LIKE '2012W26-[67]'

    There are several websites with calendars you can cut & paste, but you can start your search with: http://www.calendar-365.com/week-number.html 

    CREATE TABLE Calendar
    (cal_date DATE NOT NULL PRIMARY KEY,
     week_date CHAR(10) NOT NULL
       CHECK (week_date LIKE '[12][0-9][0-9][0-9]W[0-5][0-9]-[1-7]')
    );

    Your sample dates would be:

    INSERT INTO Calendar
    VALUES 
    ('2013-12-01', '2013W49-7'),
    ('2014-01-31', '2014W06-5');

    Use the Substring function to get the week numbers. 


    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    • Marked as answer by NicolasC Sunday, November 03, 2013 10:44 AM
    Saturday, November 02, 2013 1:23 PM
  • If you have a week number - say 50 - then there is no way to tell what year it refers to. It could be in any year.

    In your example, you are not focused on the week numbers, which is where it goes wrong. Any week in December will obviously have a higher number than any week in January. When you use BETWEEN the first value has to be smaller or equal to the second value, otherwise you will not get any qualifying rows.

    Your query currently comes down to:

    SELECT ... FROM ... WHERE somevalue BETWEEN 49 AND 5

    This is will result in an empty set (= no results)


    Gert-Jan

    Saturday, November 02, 2013 2:34 PM
  • I agree that a calendar table is the best approach.  This will easily allow you to cross year boundaries.  Below is an example that you can extend as desired.  I should mention that week number may be ambiguous depending on the specified date range.

    CREATE TABLE dbo.Calendar(
    	 CalendarDate date NOT NULL CONSTRAINT PK_Calendar PRIMARY KEY
    	,WeekNumber AS DATEPART(week, CalendarDate)
    	);
    WITH 
    	t4 AS (SELECT n FROM (VALUES(0),(0),(0),(0)) t(n))
    	,t256 AS (SELECT 0 AS n FROM t4 AS a CROSS JOIN t4 AS b CROSS JOIN t4 AS c CROSS JOIN t4 AS d)
    	,t64K AS (SELECT ROW_NUMBER() OVER (ORDER BY (a.n)) AS num FROM t256 AS a CROSS JOIN t256 AS b)
    INSERT INTO dbo.calendar WITH (TABLOCKX) (CalendarDate)
    SELECT DATEADD(day, num, '')
    FROM t64K;
    GO
    
    DECLARE
    	 @StartDate date = '2013-12-01'
    	,@EndDate date = '2014-01-31';
    
    SELECT DISTINCT WeekNumber
    FROM dbo.Calendar
    WHERE
    	CalendarDate BETWEEN @StartDate AND @EndDate;
    


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Saturday, November 02, 2013 2:44 PM
  • In addition to other posts, beware that there are (at least) two ways of numbering weeks.

    The standard defined by ISO, and in use in most countries where they actually care about week numbers I believe, says that week 1 is the week which contains Jan 4th. Another way to describe it, is that for the week that falls over New Year is numbered from the year which has the most days in that week.

    However, SQL Server is a product from the USA, and in the US they don't care too much for international standards, so SQL Server use s different definition, which says that week 1 is the week with Jan 1st. And Dec 31st always falls in week 53 or 54.

    On SQL 2008 or later, you can use ISO_week as a datepart to get the week number according to the ISO standard. There is still the issue whether weeks starts on Sundays or Mondays.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Saturday, November 02, 2013 3:30 PM