October 21, 2014 at 11:06 am
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.
October 21, 2014 at 5:16 pm
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)
October 22, 2014 at 1:59 am
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.
October 22, 2014 at 3:10 am
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
October 22, 2014 at 5:24 am
-- 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;
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
October 22, 2014 at 6:35 am
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.
October 22, 2014 at 7:32 am
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.
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
October 22, 2014 at 8:05 am
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)
October 22, 2014 at 8:12 am
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.
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
October 22, 2014 at 8:39 am
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.
October 22, 2014 at 9:42 am
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.
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
October 22, 2014 at 9:52 am
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
October 23, 2014 at 1:37 am
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.
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
October 23, 2014 at 3:05 am
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.
October 23, 2014 at 4:45 am
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
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