Comparing data for a certain day of the week by month for every year

  • Experts,

    Please help.

    I need to build a report that compares a count on a certain day of the week by month by year by stacks. That is,for first Monday in October 2012 against first Monday in October 2013 for stack DM1 against first Monday in October 2014 stack DM1, same for second Monday, first Tuesday, second Tuesday, ect. Attached is a sample dataset and what I want to achieve.

    Your help will be much appreciated.

    Thanks,

    Jaysen.

  • I would probably do it by using a Calendar table. There are a bunch of articles around here this one: http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-calendar-table.html

    You might have to add the column for Nth WeekDay of Month

    here's the function (from stackOverflow)

    http://stackoverflow.com/questions/13116222/how-to-get-week-number-of-the-month-from-the-date-in-sql-server-2008

  • Thank you for your reply. I was thinking of adding a calculated column with DayName-DayInWeek-Month (e.g.Monday-1-11), then I can group all the first Monday of November for any year at the report level.

    If any of you have a better idea please let me know.

    J.

  • I am not able to make it work. This is the code to create a sample data and attached is the result I want to achieve. Please help me write the T-SQL to achieve this.

    SELECT '2012-11-12' as [Date],'DM1' as Stack,'Monday' as [DayName],750 as ParcelCount

    UNION

    SELECT '2012-11-12' as [Date],'DM4' as Stack,'Monday' as [DayName],76 as ParcelCount

    UNION

    SELECT '2012-11-12' as [Date],'DM5' as Stack,'Monday' as [DayName],501 as ParcelCount

    UNION

    SELECT '2012-11-13' as [Date],'DM1' as Stack,'Tuesday' as [DayName],110 as ParcelCount

    UNION

    SELECT '2012-11-13' as [Date],'DM4' as Stack,'Tuesday' as [DayName],476 as ParcelCount

    UNION

    SELECT '2012-11-13' as [Date],'DM5' as Stack,'Tuesday' as [DayName],1501 as ParcelCount

    UNION

    SELECT '2012-11-29' as [Date],'DM1' as Stack,'Thursday' as [DayName],110 as ParcelCount

    UNION

    SELECT '2012-11-29' as [Date],'DM4' as Stack,'Thursday' as [DayName],476 as ParcelCount

    UNION

    SELECT '2012-11-29' as [Date],'DM5' as Stack,'Thursday' as [DayName],1501 as ParcelCount

    UNION

    SELECT '2013-11-11' as [Date],'DM1' as Stack,'Monday' as [DayName],1542 as ParcelCount

    UNION

    SELECT '2013-11-11' as [Date],'DM4' as Stack,'Monday' as [DayName],12 as ParcelCount

    UNION

    SELECT '2013-11-11' as [Date],'DM2' as Stack,'Monday' as [DayName],235 as ParcelCount

    UNION

    SELECT '2013-11-12' as [Date],'DM1' as Stack,'Tuesday' as [DayName],152 as ParcelCount

    UNION

    SELECT '2013-11-12' as [Date],'DM3' as Stack,'Tuesday' as [DayName],1214 as ParcelCount

    UNION

    SELECT '2013-11-12' as [Date],'DM5' as Stack,'Tuesday' as [DayName],2035 as ParcelCount

    UNION

    SELECT '2013-11-28' as [Date],'DM1' as Stack,'Thursday' as [DayName],1521 as ParcelCount

    UNION

    SELECT '2013-11-28' as [Date],'DM3' as Stack,'Thursday' as [DayName],4021 as ParcelCount

    UNION

    SELECT '2013-11-28' as [Date],'DM4' as Stack,'Thursday' as [DayName],25 as ParcelCount

  • -- Get all dates from two years ago (rolled back to the first of the month)

    -- up to and including today. Include the day name and the relative number

    -- of that day in the month.

    -- This could be converted very easily into an inline table-valued function

    -- to encapsulate the code and use it in your query just like a table.

    DECLARE @Enddate DATE;

    SET @Enddate = GETDATE();

    WITH

    e1 AS (SELECT d.n FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) d (n)),

    e2 AS (SELECT a.n FROM e1 a, e1 b),

    iTally AS (SELECT n = -1+ROW_NUMBER() OVER(ORDER BY (SELECT NULL))FROM e2 a, e2 b)

    SELECT TheDate, TheDay,

    nthday = ROW_NUMBER() OVER(PARTITION BY YEAR(TheDate), MONTH(TheDate), TheDay ORDER BY TheDate)

    FROM ( -- f

    SELECT TheDate, TheDay = DATENAME(WEEKDAY,TheDate)

    FROM ( -- e

    SELECT TheDate = DATEADD(day,n,Startdate)

    FROM ( -- d

    SELECT

    Startdate = DATEADD(YEAR,-2,DATEADD(MONTH,DATEDIFF(MONTH,0,@Enddate),0)),

    Enddate = CAST(@Enddate AS DATE)

    ) d

    CROSS APPLY (SELECT TOP(1+DATEDIFF(day,Startdate,Enddate)) n FROM iTally) x

    ) e

    ) f

    ORDER BY TheDate;

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • Hi Chris,

    Thank you. If I understood correctly, the piece of code will generate all the dates, 2 years back from today, with the day name and the week they fall in the month and this should be used as the base of my query left join to this my dataset. I'm still not sure how is this going to group all the parcelcount for all Mondays, all Tuesdays, etc by Stacks by year?

    Please excuse my ignorance but I may be missing something. What I'm finding difficult is how to group first Monday of November 2012 with the first Monday of November 2013 with first Monday November 2014. Do the same for Second Monday, etc and for Tuesdays ect...

    J.

  • No problem. First, put the calendar code into a function:

    CREATE FUNCTION [dbo].[IF_Calendar] (@Enddate DATE)

    RETURNS TABLE AS RETURN

    WITH

    e1 AS (SELECT d.n FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) d (n)),

    e2 AS (SELECT a.n FROM e1 a, e1 b),

    iTally AS (SELECT n = -1+ROW_NUMBER() OVER(ORDER BY (SELECT NULL))FROM e2 a, e2 b)

    SELECT TheDate, TheDay,

    nthday = ROW_NUMBER() OVER(PARTITION BY YEAR(TheDate), MONTH(TheDate), TheDay ORDER BY TheDate)

    FROM ( -- f

    SELECT TheDate, TheDay = DATENAME(WEEKDAY,TheDate)

    FROM ( -- e

    SELECT TheDate = CAST(DATEADD(day,n,Startdate) AS DATE)

    FROM ( -- d

    SELECT

    Startdate = DATEADD(YEAR,-2,DATEADD(MONTH,DATEDIFF(MONTH,0,@Enddate),0)),

    Enddate = CAST(@Enddate AS DATE)

    ) d

    CROSS APPLY (SELECT TOP(1+DATEDIFF(day,Startdate,Enddate)) n FROM iTally) x

    ) e

    ) f

    Test the function does what you want by calling it like this:

    FROM [dbo].[IF_Calendar] (GETDATE()) c

    Test it against your data like this:

    ;WITH MyData AS (SELECT * FROM (VALUES

    ('2012-11-12', 'DM1', 'Monday', 750),

    ('2012-11-12', 'DM4', 'Monday', 76),

    ('2012-11-12', 'DM5', 'Monday', 501),

    ('2012-11-13', 'DM1', 'Tuesday', 110),

    ('2012-11-13', 'DM4', 'Tuesday', 476),

    ('2012-11-13', 'DM5', 'Tuesday', 1501),

    ('2012-11-29', 'DM1', 'Thursday', 110),

    ('2012-11-29', 'DM4', 'Thursday', 476),

    ('2012-11-29', 'DM5', 'Thursday', 1501),

    ('2013-11-11', 'DM1', 'Monday', 1542),

    ('2013-11-11', 'DM4', 'Monday', 12),

    ('2013-11-11', 'DM2', 'Monday', 235),

    ('2013-11-12', 'DM1', 'Tuesday', 152),

    ('2013-11-12', 'DM3', 'Tuesday', 1214),

    ('2013-11-12', 'DM5', 'Tuesday', 2035),

    ('2013-11-28', 'DM1', 'Thursday', 1521),

    ('2013-11-28', 'DM3', 'Thursday', 4021),

    ('2013-11-28', 'DM4', 'Thursday', 25)

    ) d ([Date], Stack, [DayName], ParcelCount)

    )

    SELECT

    m.Stack,

    --c.nthday,

    [DayName] = c.TheDay,

    ParcelCount2012 = SUM(CASE WHEN YEAR(c.TheDate) = 2012 THEN m.ParcelCount ELSE 0 END),

    ParcelCount2013 = SUM(CASE WHEN YEAR(c.TheDate) = 2013 THEN m.ParcelCount ELSE 0 END),

    ParcelCount2014 = SUM(CASE WHEN YEAR(c.TheDate) = 2014 THEN m.ParcelCount ELSE 0 END)

    FROM [dbo].[IF_Calendar] (GETDATE()) c

    INNER JOIN MyData m ON m.[Date] = c.TheDate

    GROUP BY

    c.nthday,

    c.TheDay,

    m.Stack

    ORDER BY

    c.nthday,

    c.TheDay,

    m.Stack

    If you use this on a broader dataset crossing months, it will aggregate ignoring month breaks - so don't forget to account for this. Alternatively add a few more values to your sample data and describe what you want to do.

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • Hi Chris,

    Thank you so much, seems to be doing the job however, the will be a broader dataset and more months. I would these records to the sample dataset

    ('2012-12-03','DM1','Monday',441),

    ('2012-12-03','DM3','Monday',10),

    ('2012-12-10','DM4','Monday',25),

    ('2013-12-02','DM1','Monday',1521),

    ('2013-12-03','DM4','Monday',4021),

    ('2013-12-03','DM4','Monday',25),

    ('2014-12-03','DM1','Thursday',1521),

    ('2014-12-03','DM3','Thursday',4021),

    ('2014-12-03','DM4','Thursday',25)

  • vee_jess (10/22/2014)


    Hi Chris,

    Thank you so much, seems to be doing the job however, the will be a broader dataset and more months. I would these records to the sample dataset

    ('2012-12-03','DM1','Monday',441),

    ('2012-12-03','DM3','Monday',10),

    ('2012-12-10','DM4','Monday',25),

    ('2013-12-02','DM1','Monday',1521),

    ('2013-12-03','DM4','Monday',4021),

    ('2013-12-03','DM4','Monday',25),

    ('2014-12-03','DM1','Thursday',1521),

    ('2014-12-03','DM3','Thursday',4021),

    ('2014-12-03','DM4','Thursday',25)

    You don't have "month" anywhere on your report. Should it appear as a column in the output or as part of the title? It could be implicit - you could include the date in the title somewhere, and the users know that the report is for October 2014/2013/2012.

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • Chris,

    Absolutely. A Month (and probably a year) on the report will be good. The dataset will contain October, November, December data for 2012,2013 and 2014 so it will probably be a good idea to have this is. Now, presenting that on a chart maybe a bit challenging but getting the dataset for the report correct is more important at this point.

    J.

  • vee_jess (10/22/2014)


    Chris,

    Absolutely. A Month (and probably a year) on the report will be good. The dataset will contain October, November, December data for 2012,2013 and 2014 so it will probably be a good idea to have this is. Now, presenting that on a chart maybe a bit challenging but getting the dataset for the report correct is more important at this point.

    J.

    If you can decide where you want the month name to appear, the coding should follow easily.

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • Hi Cris,

    As a title will be best please.

    Also, please note that I need to report on data October,November and December 2012,2013 and 2014. Now, I will get

    4 Mondays in October 2012 against 4 Mondays in October 2013 against 4 Mondays in October 2014. Same pattern for the 7 days of the week and for November and December.

    Thanks

  • vee_jess (10/22/2014)


    Hi Cris,

    As a title will be best please.

    Also, please note that I need to report on data October,November and December 2012,2013 and 2014. Now, I will get

    4 Mondays in October 2012 against 4 Mondays in October 2013 against 4 Mondays in October 2014. Same pattern for the 7 days of the week and for November and December.

    Thanks

    How do you want the data laid out? The months could be column-wise or row-wise. Begin with the spreadsheet snapshot you've already posted and adjust it to show where the values for the different months should appear.

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • Hi Chris,

    Please find attached an excel sheet with the way I want the report to look like. I think removing 'Stack' will make it simpler, but it would be good if we can have it in. The sample data I provided should be enough but if you need more please let me know and thanks.

    J.

  • Some more sample data would probably make sense. Have a play with this and see how you get on:

    -- Amended function

    ALTER FUNCTION [dbo].[IF_Calendar] (@Enddate DATE)

    RETURNS TABLE AS RETURN

    WITH

    e1 AS (SELECT d.n FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) d (n)),

    e2 AS (SELECT a.n FROM e1 a, e1 b),

    iTally AS (SELECT n = -1+ROW_NUMBER() OVER(ORDER BY (SELECT NULL))FROM e2 a, e2 b)

    SELECT TheDate, [DayName],

    DayNo = 1+(DATEPART(dw, TheDate)+@@DATEFIRST-2)%7,

    WeekNo = ROW_NUMBER() OVER(PARTITION BY YEAR(TheDate), MONTH(TheDate), [DayName] ORDER BY TheDate)

    FROM ( -- f

    SELECT TheDate, [DayName] = DATENAME(WEEKDAY,TheDate)

    FROM ( -- e

    SELECT TheDate = CAST(DATEADD(day,n,Startdate) AS DATE)

    FROM ( -- d

    SELECT

    Startdate = DATEADD(YEAR,-2,DATEADD(MONTH,DATEDIFF(MONTH,0,@Enddate),0)),

    Enddate = CAST(@Enddate AS DATE)

    ) d

    CROSS APPLY (SELECT TOP(1+DATEDIFF(day,Startdate,Enddate)) n FROM iTally) x

    ) e

    ) f

    GO

    -- Amended query

    ;WITH MyData AS (SELECT * FROM (VALUES

    ('2012-11-12', 'DM1', 'Monday', 750),

    ('2012-11-12', 'DM4', 'Monday', 76),

    ('2012-11-12', 'DM5', 'Monday', 501),

    ('2012-11-13', 'DM1', 'Tuesday', 110),

    ('2012-11-13', 'DM4', 'Tuesday', 476),

    ('2012-11-13', 'DM5', 'Tuesday', 1501),

    ('2012-11-29', 'DM1', 'Thursday', 110),

    ('2012-11-29', 'DM4', 'Thursday', 476),

    ('2012-11-29', 'DM5', 'Thursday', 1501),

    ('2013-11-11', 'DM1', 'Monday', 1542),

    ('2013-11-11', 'DM4', 'Monday', 12),

    ('2013-11-11', 'DM2', 'Monday', 235),

    ('2013-11-12', 'DM1', 'Tuesday', 152),

    ('2013-11-12', 'DM3', 'Tuesday', 1214),

    ('2013-11-12', 'DM5', 'Tuesday', 2035),

    ('2013-11-28', 'DM1', 'Thursday', 1521),

    ('2013-11-28', 'DM3', 'Thursday', 4021),

    ('2013-11-28', 'DM4', 'Thursday', 25),

    ('2012-12-03','DM1','Monday',441),

    ('2012-12-03','DM3','Monday',10),

    ('2012-12-10','DM4','Monday',25),

    ('2013-12-02','DM1','Monday',1521),

    ('2013-12-03','DM4','Monday',4021),

    ('2013-12-03','DM4','Monday',25),

    ('2014-12-03','DM1','Thursday',1521),

    ('2014-12-03','DM3','Thursday',4021),

    ('2014-12-03','DM4','Thursday',25)

    ) d ([Date], Stack, [DayName], ParcelCount)

    )

    SELECT

    [Month] = DATENAME(MONTH,c.TheDate),

    m.Stack,

    c.WeekNo,

    c.[DayName],

    ParcelCount2012 = SUM(CASE WHEN YEAR(c.TheDate) = 2012 THEN m.ParcelCount ELSE 0 END),

    ParcelCount2013 = SUM(CASE WHEN YEAR(c.TheDate) = 2013 THEN m.ParcelCount ELSE 0 END),

    ParcelCount2014 = SUM(CASE WHEN YEAR(c.TheDate) = 2014 THEN m.ParcelCount ELSE 0 END)

    FROM [dbo].[IF_Calendar] (GETDATE()) c

    inner JOIN MyData m

    ON m.[Date] = c.TheDate

    WHERE MONTH(c.TheDate) IN (11,12)

    GROUP BY

    MONTH(c.TheDate),

    DATENAME(MONTH,c.TheDate),

    c.WeekNo,

    c.DayNo,

    c.[DayName],

    m.Stack

    ORDER BY

    MONTH(c.TheDate),

    c.WeekNo,

    c.DayNo,

    c.[DayName],

    m.Stack

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Viewing 15 posts - 1 through 15 (of 26 total)

You must be logged in to reply to this topic. Login to reply