January 17, 2019 at 8:20 pm
Hi, I have a dataset that looks like this:
CREATE TABLE [dbo].[PivotData](
[PayPlan] [nvarchar](255) NULL,
[CategorySubGroupCode] [nvarchar](255) NULL,
[GradeType] [nvarchar](255) NULL,
[GradeLevel] [float] NULL,
[inventory] [float] NULL,
[ParentCategory] [nvarchar](10) NULL
) ON [PRIMARY]
GO
INSERT [dbo].[PivotData] ([PayPlan], [CategorySubGroupCode], [GradeType], [GradeLevel], [inventory], [ParentCategory]) VALUES (N'AE', N'11B', N'E', 7, 3207, N'11Z')
GO
INSERT [dbo].[PivotData] ([PayPlan], [CategorySubGroupCode], [GradeType], [GradeLevel], [inventory], [ParentCategory]) VALUES (N'AE', N'11B', N'E', 6, 7004, N'11Z')
GO
INSERT [dbo].[PivotData] ([PayPlan], [CategorySubGroupCode], [GradeType], [GradeLevel], [inventory], [ParentCategory]) VALUES (N'AE', N'11B', N'E', 5, 7482, N'11Z')
GO
INSERT [dbo].[PivotData] ([PayPlan], [CategorySubGroupCode], [GradeType], [GradeLevel], [inventory], [ParentCategory]) VALUES (N'AE', N'11B', N'E', 4, 14489, N'11Z')
GO
INSERT [dbo].[PivotData] ([PayPlan], [CategorySubGroupCode], [GradeType], [GradeLevel], [inventory], [ParentCategory]) VALUES (N'AE', N'11B', N'E', 3, 6655, N'11Z')
GO
INSERT [dbo].[PivotData] ([PayPlan], [CategorySubGroupCode], [GradeType], [GradeLevel], [inventory], [ParentCategory]) VALUES (N'AE', N'11B', N'E', 2, 3283, N'11Z')
GO
INSERT [dbo].[PivotData] ([PayPlan], [CategorySubGroupCode], [GradeType], [GradeLevel], [inventory], [ParentCategory]) VALUES (N'AE', N'11B', N'E', 1, 3502, N'11Z')
GO
INSERT [dbo].[PivotData] ([PayPlan], [CategorySubGroupCode], [GradeType], [GradeLevel], [inventory], [ParentCategory]) VALUES (N'AE', N'11C', N'E', 7, 390, N'11Z')
GO
INSERT [dbo].[PivotData] ([PayPlan], [CategorySubGroupCode], [GradeType], [GradeLevel], [inventory], [ParentCategory]) VALUES (N'AE', N'11C', N'E', 6, 669, N'11Z')
GO
INSERT [dbo].[PivotData] ([PayPlan], [CategorySubGroupCode], [GradeType], [GradeLevel], [inventory], [ParentCategory]) VALUES (N'AE', N'11C', N'E', 5, 851, N'11Z')
GO
INSERT [dbo].[PivotData] ([PayPlan], [CategorySubGroupCode], [GradeType], [GradeLevel], [inventory], [ParentCategory]) VALUES (N'AE', N'11C', N'E', 4, 1877, N'11Z')
GO
INSERT [dbo].[PivotData] ([PayPlan], [CategorySubGroupCode], [GradeType], [GradeLevel], [inventory], [ParentCategory]) VALUES (N'AE', N'11C', N'E', 3, 1029, N'11Z')
GO
INSERT [dbo].[PivotData] ([PayPlan], [CategorySubGroupCode], [GradeType], [GradeLevel], [inventory], [ParentCategory]) VALUES (N'AE', N'11C', N'E', 2, 526, N'11Z')
GO
INSERT [dbo].[PivotData] ([PayPlan], [CategorySubGroupCode], [GradeType], [GradeLevel], [inventory], [ParentCategory]) VALUES (N'AE', N'11C', N'E', 1, 491, N'11Z')
GO
INSERT [dbo].[PivotData] ([PayPlan], [CategorySubGroupCode], [GradeType], [GradeLevel], [inventory], [ParentCategory]) VALUES (N'AE', N'11X', N'E', 5, 7, N'11X')
GO
INSERT [dbo].[PivotData] ([PayPlan], [CategorySubGroupCode], [GradeType], [GradeLevel], [inventory], [ParentCategory]) VALUES (N'AE', N'11X', N'E', 4, 20, N'11X')
GO
INSERT [dbo].[PivotData] ([PayPlan], [CategorySubGroupCode], [GradeType], [GradeLevel], [inventory], [ParentCategory]) VALUES (N'AE', N'11X', N'E', 3, 23, N'11X')
GO
INSERT [dbo].[PivotData] ([PayPlan], [CategorySubGroupCode], [GradeType], [GradeLevel], [inventory], [ParentCategory]) VALUES (N'AE', N'11X', N'E', 2, 85, N'11X')
GO
INSERT [dbo].[PivotData] ([PayPlan], [CategorySubGroupCode], [GradeType], [GradeLevel], [inventory], [ParentCategory]) VALUES (N'AE', N'11X', N'E', 1, 403, N'11X')
GO
INSERT [dbo].[PivotData] ([PayPlan], [CategorySubGroupCode], [GradeType], [GradeLevel], [inventory], [ParentCategory]) VALUES (N'AE', N'11Z', N'E', 9, 548, N'11Z')
GO
INSERT [dbo].[PivotData] ([PayPlan], [CategorySubGroupCode], [GradeType], [GradeLevel], [inventory], [ParentCategory]) VALUES (N'AE', N'11Z', N'E', 8, 1255, N'11Z')
GO
INSERT [dbo].[PivotData] ([PayPlan], [CategorySubGroupCode], [GradeType], [GradeLevel], [inventory], [ParentCategory]) VALUES (N'PP', N'11Q', N'E', 3, 4000, N'11Q')
GO
INSERT [dbo].[PivotData] ([PayPlan], [CategorySubGroupCode], [GradeType], [GradeLevel], [inventory], [ParentCategory]) VALUES (N'PP', N'11Z', N'E', 1, 1000, N'11Z')
GO
INSERT [dbo].[PivotData] ([PayPlan], [CategorySubGroupCode], [GradeType], [GradeLevel], [inventory], [ParentCategory]) VALUES (N'PP', N'11B', N'E', 1, 50, N'11Z')
GO
INSERT [dbo].[PivotData] ([PayPlan], [CategorySubGroupCode], [GradeType], [GradeLevel], [inventory], [ParentCategory]) VALUES (N'PP', N'11C', N'E', 1, 10, N'11Z')
I need to create a "cumulative sum" field that gets added to the dataset in a tabular fashion. However the cumulative sum field has to take into account the following conditions:
In the above example (sample data), 11Z is the parent of 11B and 11C so my cumulative sum needs two improvements:
- The cumulative sum of 11B and 11C should account for the inventory in 11Z
- And to get even more complicated, the cumulative sum in 11B and 11C should get a representative fraction of the 11Z inventory so we don’t over count, since they are both feeding it.
The below snippet of code I created for the cumulative sum but I am having trouble getting this all to come together into one tabular table. I added the parentcategory field to their dataset cause I think I need that in order to group the data
per the instructions above that says the CategorySubGroupCode of 11Z is the parent of 11B and 11C. The second point I am not sure what the "fractional" percentage is that they want, but I think we can use an arbitrary value for now just to get the calc to work properly.
SUM(inventory) OVER (PARTITION BY PayPlan,
CategorySubGroupCode
ORDER BY PayPlan,
CategorySubGroupCode,
GradeLevel DESC
) AS inv_cumulative
Does this make any sense? Honestly, it was kinda boggling my brain a little.
January 17, 2019 at 8:50 pm
Wait, if you want a cumulative sum, your formula is wrong. There's no ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW in there... Is there are hierarchy in this? Can you explain it? (I'd rather not go down some rabbit hole where I make some dumb incorrect assumption)
Thanks!
January 17, 2019 at 8:51 pm
amy26 - Thursday, January 17, 2019 8:20 PMHi, I have a dataset that looks like this:
CREATE TABLE [dbo].[PivotData](
[PayPlan] [nvarchar](255) NULL,
[CategorySubGroupCode] [nvarchar](255) NULL,
[GradeType] [nvarchar](255) NULL,
[GradeLevel] [float] NULL,
[inventory] [float] NULL,
[ParentCategory] [nvarchar](10) NULL
) ON [PRIMARY]GO
INSERT [dbo].[PivotData] ([PayPlan], [CategorySubGroupCode], [GradeType], [GradeLevel], [inventory], [ParentCategory]) VALUES (N'AE', N'11B', N'E', 7, 3207, N'11Z')
GO
INSERT [dbo].[PivotData] ([PayPlan], [CategorySubGroupCode], [GradeType], [GradeLevel], [inventory], [ParentCategory]) VALUES (N'AE', N'11B', N'E', 6, 7004, N'11Z')
GO
INSERT [dbo].[PivotData] ([PayPlan], [CategorySubGroupCode], [GradeType], [GradeLevel], [inventory], [ParentCategory]) VALUES (N'AE', N'11B', N'E', 5, 7482, N'11Z')
GO
INSERT [dbo].[PivotData] ([PayPlan], [CategorySubGroupCode], [GradeType], [GradeLevel], [inventory], [ParentCategory]) VALUES (N'AE', N'11B', N'E', 4, 14489, N'11Z')
GO
INSERT [dbo].[PivotData] ([PayPlan], [CategorySubGroupCode], [GradeType], [GradeLevel], [inventory], [ParentCategory]) VALUES (N'AE', N'11B', N'E', 3, 6655, N'11Z')
GO
INSERT [dbo].[PivotData] ([PayPlan], [CategorySubGroupCode], [GradeType], [GradeLevel], [inventory], [ParentCategory]) VALUES (N'AE', N'11B', N'E', 2, 3283, N'11Z')
GO
INSERT [dbo].[PivotData] ([PayPlan], [CategorySubGroupCode], [GradeType], [GradeLevel], [inventory], [ParentCategory]) VALUES (N'AE', N'11B', N'E', 1, 3502, N'11Z')
GO
INSERT [dbo].[PivotData] ([PayPlan], [CategorySubGroupCode], [GradeType], [GradeLevel], [inventory], [ParentCategory]) VALUES (N'AE', N'11C', N'E', 7, 390, N'11Z')
GO
INSERT [dbo].[PivotData] ([PayPlan], [CategorySubGroupCode], [GradeType], [GradeLevel], [inventory], [ParentCategory]) VALUES (N'AE', N'11C', N'E', 6, 669, N'11Z')
GO
INSERT [dbo].[PivotData] ([PayPlan], [CategorySubGroupCode], [GradeType], [GradeLevel], [inventory], [ParentCategory]) VALUES (N'AE', N'11C', N'E', 5, 851, N'11Z')
GO
INSERT [dbo].[PivotData] ([PayPlan], [CategorySubGroupCode], [GradeType], [GradeLevel], [inventory], [ParentCategory]) VALUES (N'AE', N'11C', N'E', 4, 1877, N'11Z')
GO
INSERT [dbo].[PivotData] ([PayPlan], [CategorySubGroupCode], [GradeType], [GradeLevel], [inventory], [ParentCategory]) VALUES (N'AE', N'11C', N'E', 3, 1029, N'11Z')
GO
INSERT [dbo].[PivotData] ([PayPlan], [CategorySubGroupCode], [GradeType], [GradeLevel], [inventory], [ParentCategory]) VALUES (N'AE', N'11C', N'E', 2, 526, N'11Z')
GO
INSERT [dbo].[PivotData] ([PayPlan], [CategorySubGroupCode], [GradeType], [GradeLevel], [inventory], [ParentCategory]) VALUES (N'AE', N'11C', N'E', 1, 491, N'11Z')
GO
INSERT [dbo].[PivotData] ([PayPlan], [CategorySubGroupCode], [GradeType], [GradeLevel], [inventory], [ParentCategory]) VALUES (N'AE', N'11X', N'E', 5, 7, N'11X')
GO
INSERT [dbo].[PivotData] ([PayPlan], [CategorySubGroupCode], [GradeType], [GradeLevel], [inventory], [ParentCategory]) VALUES (N'AE', N'11X', N'E', 4, 20, N'11X')
GO
INSERT [dbo].[PivotData] ([PayPlan], [CategorySubGroupCode], [GradeType], [GradeLevel], [inventory], [ParentCategory]) VALUES (N'AE', N'11X', N'E', 3, 23, N'11X')
GO
INSERT [dbo].[PivotData] ([PayPlan], [CategorySubGroupCode], [GradeType], [GradeLevel], [inventory], [ParentCategory]) VALUES (N'AE', N'11X', N'E', 2, 85, N'11X')
GO
INSERT [dbo].[PivotData] ([PayPlan], [CategorySubGroupCode], [GradeType], [GradeLevel], [inventory], [ParentCategory]) VALUES (N'AE', N'11X', N'E', 1, 403, N'11X')
GO
INSERT [dbo].[PivotData] ([PayPlan], [CategorySubGroupCode], [GradeType], [GradeLevel], [inventory], [ParentCategory]) VALUES (N'AE', N'11Z', N'E', 9, 548, N'11Z')
GO
INSERT [dbo].[PivotData] ([PayPlan], [CategorySubGroupCode], [GradeType], [GradeLevel], [inventory], [ParentCategory]) VALUES (N'AE', N'11Z', N'E', 8, 1255, N'11Z')
GO
INSERT [dbo].[PivotData] ([PayPlan], [CategorySubGroupCode], [GradeType], [GradeLevel], [inventory], [ParentCategory]) VALUES (N'PP', N'11Q', N'E', 3, 4000, N'11Q')
GO
INSERT [dbo].[PivotData] ([PayPlan], [CategorySubGroupCode], [GradeType], [GradeLevel], [inventory], [ParentCategory]) VALUES (N'PP', N'11Z', N'E', 1, 1000, N'11Z')
GO
INSERT [dbo].[PivotData] ([PayPlan], [CategorySubGroupCode], [GradeType], [GradeLevel], [inventory], [ParentCategory]) VALUES (N'PP', N'11B', N'E', 1, 50, N'11Z')
GO
INSERT [dbo].[PivotData] ([PayPlan], [CategorySubGroupCode], [GradeType], [GradeLevel], [inventory], [ParentCategory]) VALUES (N'PP', N'11C', N'E', 1, 10, N'11Z')I need to create a "cumulative sum" field that gets added to the dataset in a tabular fashion. However the cumulative sum field has to take into account the following conditions:
In the above example (sample data), 11Z is the parent of 11B and 11C so my cumulative sum needs two improvements:
- The cumulative sum of 11B and 11C should account for the inventory in 11Z
- And to get even more complicated, the cumulative sum in 11B and 11C should get a representative fraction of the 11Z inventory so we don’t over count, since they are both feeding it.The below snippet of code I created for the cumulative sum but I am having trouble getting this all to come together into one tabular table. I added the parentcategory field to their dataset cause I think I need that in order to group the data
per the instructions above that says the CategorySubGroupCode of 11Z is the parent of 11B and 11C. The second point I am not sure what the "fractional" percentage is that they want, but I think we can use an arbitrary value for now just to get the calc to work properly.
SUM(inventory) OVER (PARTITION BY PayPlan,
CategorySubGroupCode
ORDER BY PayPlan,
CategorySubGroupCode,
GradeLevel DESC
) AS inv_cumulativeDoes this make any sense? Honestly, it was kinda boggling my brain a little.
So... for your 11Z example and using the data you posted (thanks for that, by the way), what exactly are you looking for as an output? I ask because I have no clue as to how to interpret your statement of "The cumulative sum of 11B and 11C should account for the inventory in 11Z" because there's more than one way to interpret it given the data you posted. The same holds true for your statement about somehow distributing the inventory of 11Z to 11A and 11B especially with the additional confusion factor of 11A and 11B already being partially accounted for in 11Z (or is it vice versa).
It's just not clear what you're asking. Either that or my brain is more fried than yours. 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
January 18, 2019 at 7:23 am
Ok thank you, its not just me that's hella confused by the request. The comments I added for what was required came from the user that requested it. I wasn't sure if what he asked me was over my head or if it indeed didn't make a lot of sense. 😀 I need to focus on some other stuff this morning but I will try to rephrase and/or get more details. 🙂
January 18, 2019 at 8:01 am
pietlinden - Thursday, January 17, 2019 8:50 PMWait, if you want a cumulative sum, your formula is wrong. There's no ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW in there... Is there are hierarchy in this? Can you explain it? (I'd rather not go down some rabbit hole where I make some dumb incorrect assumption)Thanks!
It will use the default RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, which always performs worse than ROWS. RANGE is deterministic, which is why it's the default, ROWS may not be deterministic if the ORDER BY isn't completely specified.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
January 18, 2019 at 8:05 am
amy26 - Friday, January 18, 2019 7:23 AMOk thank you, its not just me that's hella confused by the request. The comments I added for what was required came from the user that requested it. I wasn't sure if what he asked me was over my head or if it indeed didn't make a lot of sense. 😀 I need to focus on some other stuff this morning but I will try to rephrase and/or get more details. 🙂
No... not just you. 😀 I understand the Grade_Level thing as being an indicator of the item that should be at the top of the hierarchy (although the data you provided is a bit short there... it doesn't contain all the top level rows for all the child rows represented for 11Z as the parent.)
If I had better data to test with, I think the following article would be really useful to solving this problem. To summarize, it calculates the rollup totals for all levels below any given node and for all nodes and stores them for some really nasty fast queries. We just need for the people driving the problem to more adequately explain the problem.
Hierarchies on Steroids #2: A Replacement for Nested Sets Calculations
--Jeff Moden
Change is inevitable... Change for the better is not.
January 18, 2019 at 9:28 am
I came up with my best guess at what they are asking for. Here it is:
; WITH SubCategoryDistribution AS
(
SELECT ParentCategory, CategorySubGroupCode, SUM(Inventory)/SUM(SUM(Inventory)) OVER(PARTITION BY ParentCategory) AS SubcategoryPercent
FROM #PivotData
WHERE CategorySubGroupCode <> ParentCategory
GROUP BY CategorySubGroupCode, ParentCategory
)
SELECT *, SUM(pd.inventory * COALESCE(scd.SubcategoryPercent, 1)) OVER(PARTITION BY pd.PayPlan, COALESCE(scd.CategorySubGroupCode, pd.CategorySubGroupCode) ORDER BY GradeLevel DESC ROWS UNBOUNDED PRECEDING) AS CumulativeInventory
FROM #PivotData pd
LEFT OUTER JOIN SubCategoryDistribution scd
ON pd.CategorySubGroupCode = scd.ParentCategory
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
January 28, 2019 at 6:57 am
Ok here is the reply I got from the user... does this help at all?
Sure thing. To clarify,I’m not trying to do a tabular structure. The tabular structure shown isonly for easy of viewing, my desired end result is in the original data format+ an additional cumulative column. Does the following help?
| subgrp
| Grade level
| Eventual parent
| inventory
|
| 11B
| 3
| 11X
| 20
|
| 11B
| 4
| 11X
| 10
|
| 11A
| 3
| 11X
| 5
|
| 11A
| 4
| 11X
| 4
|
| 11X
| 5
|
| 8
|
11Bs and 11As grow up to be11Xs. I want to create a cumulative inventory count with apportionment asfollows:
Note: 11A’s make up 23% of thefeed to the parent 11X [(5+4)/(20+10+5+4)] while 11Bs make up the remaining 77%(20+10)/(20+10+5+4)
| subgrp
| Grade level
| Eventual parent
| inventory
| Cumulative inventory occurring in the current grade level and all subsequent grade levels
|
| 11B
| 3
| 11X
| 20
| 20 +10+8*77% = 36
|
| 11B
| 4
| 11X
| 10
| 10 + 8*77% = 16
|
| 11A
| 3
| 11X
| 5
| 5 + 4 + 8*23% = 11
|
| 11A
| 4
| 11X
| 4
| 4 + 8*23% = 6
|
| 11X
| 5
|
| 8
| 8 (there are no grade levels after this so cumulative inventory = inventory)
|
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply