October 24, 2016 at 3:46 pm
I have a table with sales information that has a month key associated with each record. What I'd like to do is allow the user to dynamically sum the amounts By Period, By Quarter, or By Year. To do this, I'm using the "SUM" function along with the "CASE" statement to sum amounts for specific months using the "IN" clause. See below for example.
Here's my issue:
Rather than explicitly listing each month in the "IN" clause that belongs to, for example, a Quarter... I'd like to come up with a more dynamic/flexible way of doing this. For example, instead of using "IN(1, 2, 3)", I'd like to use something like "In('Q1')" or something like that so the query already knows that Q1 includes "1, 2, 3" and then calculate appropriately.
Make sense? Any ideas?
Here are some sample tables and data:
CREATE TABLE tblMonths (
MonthKey int,
Name varchar(40),
ParentKey int
)
INSERT INTO tblMonths Values(1, 'Total', 0)
INSERT INTO tblMonths Values(2, 'Q1', 1)
INSERT INTO tblMonths Values(3, 'Q2', 1)
INSERT INTO tblMonths Values(4, 'Q3', 1)
INSERT INTO tblMonths Values(5, 'Q4', 1)
INSERT INTO tblMonths Values(6, 'Jan', 2)
INSERT INTO tblMonths Values(7, 'Feb', 2)
INSERT INTO tblMonths Values(8, 'Mar', 2)
INSERT INTO tblMonths Values(9, 'Apr', 3)
INSERT INTO tblMonths Values(10, 'May', 3)
INSERT INTO tblMonths Values(11, 'Jun', 3)
INSERT INTO tblMonths Values(12, 'Jul', 4)
INSERT INTO tblMonths Values(13, 'Aug', 4)
INSERT INTO tblMonths Values(14, 'Sep', 4)
INSERT INTO tblMonths Values(14, 'Oct', 4)
INSERT INTO tblMonths Values(14, 'Nov', 4)
INSERT INTO tblMonths Values(14, 'Dec', 4)
CREATE TABLE tblOrders (
OrderKey int,
MonthKey int,
Amount decimal(18,0)
)
INSERT INTO tblOrders Values(1, 1, 600)
INSERT INTO tblOrders Values(2, 2, 400)
INSERT INTO tblOrders Values(3, 3, 100)
INSERT INTO tblOrders Values(4, 4, 700)
INSERT INTO tblOrders Values(5, 5, 300)
INSERT INTO tblOrders Values(6, 6, 200)
INSERT INTO tblOrders Values(7, 7, 800)
INSERT INTO tblOrders Values(8, 8, 200)
INSERT INTO tblOrders Values(9, 9, 300)
INSERT INTO tblOrders Values(10, 10, 900)
INSERT INTO tblOrders Values(11, 11, 100)
INSERT INTO tblOrders Values(12, 12, 400)
Here's my basic SQL (this is actually part of a more complicated crosstab query, but this is a good sample of what I'm trying to do):
SELECT
SUM(
CASE
WHEN MonthKey In(1,2,3)
THEN Amount
ELSE 0
END
) AS 'Quarter1'
FROM tblOrders
October 25, 2016 at 1:32 am
October 25, 2016 at 8:57 am
Modify tblMonths to be more like a proper calendar table
CREATE TABLE dbo.tblMonths (
MonthKey int,
Month_Name varchar(40),
Quarter_Name varchar(40),
Total_Name varchar(40)
);
GO
INSERT INTO dbo.tblMonths ( MonthKey, Month_Name, Quarter_Name, Total_Name )
VALUES( 1, 'Jan', 'Q1', 'Total' )
, ( 2, 'Feb', 'Q1', 'Total' )
, ( 3, 'Mar', 'Q1', 'Total' )
, ( 4, 'Apr', 'Q2', 'Total' )
, ( 5, 'May', 'Q2', 'Total' )
, ( 6, 'Jun', 'Q2', 'Total' )
, ( 7, 'Jul', 'Q3', 'Total' )
, ( 8, 'Aug', 'Q3', 'Total' )
, ( 9, 'Sep', 'Q3', 'Total' )
, ( 10, 'Oct', 'Q4', 'Total' )
, ( 11, 'Nov', 'Q4', 'Total' )
, ( 12, 'Dec', 'Q4', 'Total' );
GO
Then using your existing tblOrders ...
DECLARE @Period VARCHAR(40) = 'Q1';
SELECT
@Period AS [Period]
, SUM(Amount) AS [Total]
FROM tblOrders
WHERE MonthKey IN (SELECT MonthKey FROM dbo.tblMonths AS tm
WHERE @Period IN (Month_Name, Quarter_Name, Total_Name)
);
October 27, 2016 at 9:42 am
Sorry. This solution would not work for me. I understand there are a lot of ways to setup your solution to make it easy to rollup calendar data. But that's not the only type of data I'm trying to rolluo. The example I used above was just to demonstrate the concept. I used month info because I thought it was best way to describe what I was trying to do.
I'll have to repost with a better example so that it won't lead to people to point to standard calendar type solutions. I need something that would work with any type of data.
Thanks for your response by the way.
October 27, 2016 at 12:21 pm
ptownbro (10/27/2016)
Sorry. This solution would not work for me. I understand there are a lot of ways to setup your solution to make it easy to rollup calendar data. But that's not the only type of data I'm trying to rolluo. The example I used above was just to demonstrate the concept. I used month info because I thought it was best way to describe what I was trying to do.I'll have to repost with a better example so that it won't lead to people to point to standard calendar type solutions. I need something that would work with any type of data.
Thanks for your response by the way.
OK, so let's try something different.
Firstly, your sample data looks like it has some typos, so I fixed it
CREATE TABLE dbo.tblMonths (
MonthKey int,
Name varchar(40),
ParentKey int
);
GO
INSERT INTO tblMonths ( MonthKey, Name, ParentKey )
Values ( 1, 'Total', 0)
, ( 2, 'Q1', 1)
, ( 3, 'Q2', 1)
, ( 4, 'Q3', 1)
, ( 5, 'Q4', 1)
, ( 6, 'Jan', 2)
, ( 7, 'Feb', 2)
, ( 8, 'Mar', 2)
, ( 9, 'Apr', 3)
, (10, 'May', 3)
, (11, 'Jun', 3)
, (12, 'Jul', 4)
, (13, 'Aug', 4)
, (15, 'Sep', 4)
, (16, 'Oct', 5)
, (17, 'Nov', 5)
, (18, 'Dec', 5);
GO
CREATE TABLE dbo.tblOrders (
OrderKey int,
MonthKey int,
Amount decimal(18,0)
);
GO
INSERT INTO dbo.tblOrders ( OrderKey, MonthKey, Amount )
Values ( 1, 6, 600)
, ( 2, 7, 400)
, ( 3, 8, 100)
, ( 4, 9, 700)
, ( 5, 10, 300)
, ( 6, 11, 200)
, ( 7, 12, 800)
, ( 8, 13, 200)
, ( 9, 14, 300)
, (10, 15, 900)
, (11, 16, 100)
, (12, 17, 400);
GO
Now, let's use a recursive CTE to get a hierarchy of periods
DECLARE @PeriodName VARCHAR(40) = 'Q1';
;WITH cteHierarchy ( MonthKey, Name, ParentKey, [Level] ) AS (
SELECT m.MonthKey, m.Name, m.ParentKey, [Level] = 1
FROM dbo.tblMonths as m
WHERE m.Name = @PeriodName
/***/ UNION ALL /***/
SELECT m.MonthKey, m.Name, m.ParentKey, c.[Level] +1
FROM dbo.tblMonths AS m
INNER JOIN cteHierarchy AS c ON m.ParentKey = c.MonthKey
)
SELECT SUM(Amount) AS [Total]
FROM dbo.tblOrders AS o
INNER JOIN cteHierarchy AS cte
ON o.MonthKey = cte.MonthKey
WHERE cte.[Level] = (select max([Level]) from cteHierarchy);
October 27, 2016 at 12:32 pm
Now you can add additional Hierarchies, without changing the code ...
INSERT INTO tblMonths ( MonthKey, Name, ParentKey )
Values (19, 'OddMonths', 1)
, (20, 'EvenMonths', 1)
, ( 6, 'Jan', 19)
, ( 7, 'Feb', 20)
, ( 8, 'Mar', 19)
, ( 9, 'Apr', 20)
, (10, 'May', 19)
, (11, 'Jun', 20)
, (12, 'Jul', 19)
, (13, 'Aug', 20)
, (15, 'Sep', 19)
, (16, 'Oct', 20)
, (17, 'Nov', 19)
, (18, 'Dec', 20);
October 27, 2016 at 5:27 pm
ptownbro (10/27/2016)
Sorry. This solution would not work for me. I understand there are a lot of ways to setup your solution to make it easy to rollup calendar data. But that's not the only type of data I'm trying to rolluo. The example I used above was just to demonstrate the concept. I used month info because I thought it was best way to describe what I was trying to do.I'll have to repost with a better example so that it won't lead to people to point to standard calendar type solutions. I need something that would work with any type of data.
Thanks for your response by the way.
]
Consider what you may find in the following two articles... especially the second one...
[font="Arial Black"]Hierarchies on Steroids #1: Convert an Adjacency List to Nested Sets[/font][/url]
[font="Arial Black"]Hierarchies on Steroids #2: A Replacement for Nested Sets Calculations[/font][/url]
--Jeff Moden
Change is inevitable... Change for the better is not.
October 28, 2016 at 4:38 am
Jeff Moden (10/27/2016)
Consider what you may find in the following two articles... especially the second one...[font="Arial Black"]Hierarchies on Steroids #1: Convert an Adjacency List to Nested Sets[/font][/url]
[font="Arial Black"]Hierarchies on Steroids #2: A Replacement for Nested Sets Calculations[/font][/url]
Thanks for that Jeff.
Will keep me busy this weekend.
November 1, 2016 at 11:26 am
Thanks everyone for your suggestions! I'm going to try them tonight and will report back. Appreciate the help.
November 1, 2016 at 2:53 pm
Thanks guys. I learned a lot from reading Jeff's articles. Interestingly I had already developed Sort path method (which I called a Tree Path) as well as incorporated CTE's before. It's was nice to see areas I could add to my solution and see it validated by the experts. Also, the nested sets was an interesting model.
Anyway, great/brilliant stuff.
Thanks again to both!
November 1, 2016 at 2:59 pm
ptownbro (11/1/2016)
Thanks guys. I learned a lot from reading Jeff's articles. Interestingly I had already developed Sort path method (which I called a Tree Path) as well as incorporated CTE's before. It's was nice to see areas I could add to my solution and see it validated by the experts. Also, the nested sets was an interesting model.Anyway, great/brilliant stuff.
Thanks again to both!
Glad to be of service and thank you very much for taking the time to post your feedback.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 6, 2016 at 12:08 pm
I'm not sure who asked the question today but I got an email from the "webmaster", which means that someone hit "report" instead of "reply. Since I have no way of actually know who posted, I'll repeat the question and then reply because it's a good question and might help others on this thread. Here's the question from the email I got.
Instead of using the bowers methods to find the associated children which below to a selected parent. Why couldn't you just use a filter on the "Sort Path" column you created?
Following your example, if I just filter for Bob's node in the Sort Path.... it gives me records for Bob and his associated employees. You wouldn't need the bower method or all the work needed to maintain it.
I've been using it in my solution and it seems to work. Though I'm not storing the Sort Path as binary. I'm storing it as a string. Not sure if that would make a difference as I haven't tested.
Just wanted to see if I'm missing something.
Thanks again for your help.
References:
Hierarchies on Steroids #1: Convert an Adjacency List to Nested Sets
Hierarchies on Steroids #2: A Replacement for Nested Sets Calculations
You certainly COULD use the SortPath (Hierarchical path) for to do searches. The problem is that if you don't know Bob's level, you would have to do a mid-string search in one form or another. Even if you do know level, you would still have to use a formula on the SortPath criteria and that will be relatively slow, as well, because it won't be able to do anything other than an index scan. It might even be faster just to use an rCTE (Recusive CTE) on some well indexed columns.
But then, and depending on what you need to do, you'll still need to have other columns come into play if you want to do downline-aggregations for the members of Bob's team.
Here's the code and the stats when looking up EmployeeID 100,000 in a particular million node hierarchy using Nested Sets.
SET STATISTICS TIME,IO ON;
WITH cteGetBowers AS
(
SELECT LeftBower, RightBower FROM dbo.Hierarchy WHERE EmployeeID = 100000
)
SELECT h.*
FROM dbo.Hierarchy h
JOIN cteGetBowers b
ON h.LeftBower >= b.LeftBower
AND h.RightBower <= b.RightBower
ORDER BY h.LeftBower
;
SET STATISTICS TIME,IO OFF;
(35 row(s) affected)
Table 'Hierarchy'. Scan count 2, logical reads 554, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 4 ms.
Here's the same lookup for the source path. We have to figure out the level first just like we had to figure out the bowers first, previously.
SET STATISTICS TIME,IO ON;
WITH cteGLevel AS
(
SELECT HLevel FROM dbo.Hierarchy WHERE EmployeeID = 100000
)
SELECT *
FROM dbo.Hierarchy
CROSS APPLY (SELECT HLevel FROM cteGLevel) ca
WHERE SUBSTRING(SortPath,(ca.HLevel-2)*4+1,4) = CONVERT(BINARY(4),100000)
ORDER BY SortPath
SET STATISTICS TIME,IO OFF;
(35 row(s) affected)
Table 'Hierarchy'. Scan count 1, logical reads 17949, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 203 ms, elapsed time = 213 ms.
Look at the differences in reads, CPU, and duration and that's with some tightly packed binaries. Imagine what would happen if they were character based. Although the stats may seem trivial for a single run, those are huge differences percentage-wise. Which would you rather have run a hundred thousand times per day?
If all you want to do is display a simple downline for a node and you don't have many rows, then, yeah... you might want to just use an rCTE and forget the SortPath altogether... I wouldn't use the SortPath, which also has to be maintained and still isn't SARGable.
Wait... were not done yet. Use the SortPath to try to determine what the upline is, figure out per node downline totals, etc, etc. Heh... yeah... I know. That's not in the requirements you've been given. Be prepared because those could change pretty quickly once they figure out you know how to spell "hierarchy".
With all that, my recommendation is to not use SortPath. Either use an rCTE or Nested Sets (which only require an rCTE to be executed once overall). You know which one I'll be using especially when you look into what can actually be done as in the second article you referenced.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply