Running Total's CTE Challenge

Question

Post reply

Running Total's CTE Challenge

Fraggle-805517

SSCrazy Eights

Points: 8907
More actions
April 21, 2011 at 1:18 pm

#147931

Hey everyone,
I have a fun challenge that I could use some help with. I have the following table.
CREATE TABLE #TEMP
(ChildID INT, Parent1Level INT, TopParent INT, Level INT
, Quantity INT, ParentQuantity INT, CalcQuantity INT)
INSERT INTO #TEMP
SELECT 526,0,0,0,1,0,0 UNION ALL
SELECT 706,0,0,0,2,0,0 UNION ALL
SELECT 455,706,706,1,1,0,0 UNION ALL
SELECT 497,706,706,1,1,0,0 UNION ALL
SELECT 507,526,526,1,100,0,0 UNION ALL
SELECT 560,497,706,2,1,0,0 UNION ALL
SELECT 478,497,706,2,2,0,0 UNION ALL
SELECT 496,497,706,2,1,0,0 UNION ALL
SELECT 499,497,706,2,4,0,0 UNION ALL
SELECT 522,497,706,2,1,0,0 UNION ALL
SELECT 504,497,706,2,8,0,0 UNION ALL
SELECT 507,497,706,2,5,0,0
As you can see this is a Parent/Child relationship. Specifically, this is for Inventory, where a Parent can have infiniate levels of other packages. What I need to do is to figure out a kinda running total of inventory. So let me give an example:
Item 706 is a top parent item (0 TopParent). When the order was placed, 2 units where purchased. Items 455 & 497 are child items to Iem 706 1 level down. For each unit of 706 that is purchased they get 1 each of 455 and 497. So I would need 2 of each. 2 should be placed in the ParentQuantity Column for each of these two items as that was the number of units that was purchages for item 706. 2 units should be placed in CalcQuantity as that is the number of units that we need to use to calculate a number of variables.
Now, item 497 as a number of items below it (560,478,496,499,522,504,507). Each of these has a OriginalQuantity attached to it. So if Only 1 unit of 706 was purchased, and only 1 unit of 497 was part of the 706 item, then I get a total of 5 units for item 507 and 8 units for 504. So Since I purchases 2 units of 706, then I have 2 units of 497. This 2 should be placed in the ParentQuantity column. I then need values of 10 and 16 in each of the calculated columns for those two items.
Now to make things more challenging, if 497 has an OriginalQuantity of 2 rather than 1, then I should be updating ParentQuantity with 4 (2units of 706 * 2 units of 497 = 4). I would then use this to multiply by the base requirement of 5 units for item 507, which means my calculated should be 20.
-- So that is the problem. --
Now, I realize I can do it in a loop with ease. However, I dislike loops on principal alone, must less I have only found 1 instance in any type of testing where a loop was faster than a set based operation. With that said, I am looking at wanting to use a CTE Running total or some other set based operation. Now before everyone starts yelling I realize that a CTE Running total is a Quasi-loop as Jeff Modem likes to call them. I also know that I have used a running total CTE in a number of ways and in general they are super fast.
Right now, my starting point is something like this.
-- Now that we have the original data set, we need to do a running total CTE to mutliply going downline, which make sure we are accurately charinging shipping.
-- http://weblogs.sqlteam.com/mladenp/archive/2009/07/28/SQL-Server-2005-Fast-Running-Totals.aspx
; WITH cte_RunningTotal AS
(
SELECT top (2147483647) I.ChildID, I.Parent1Level, I.TopLevel, I.Level, I.OriginalQuantity, I.ParentQuantity, I.CalcQuantity
FROM #InvList as I
ORDER BY Level
)
UPDATE r
set @var1 = ParentQuantity = @var1 * R.OriginalQuantity
OUTPUT INSERTED.*
FROM cte_runningTotal as r
As you can see, it is doing a running total just fine. However, my numbers are not any where near correct.
Can anyone help me with this.
Thanks,
Fraggle

Viewing 9 posts - 1 through 8 (of 8 total)

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

WayneS SSC Guru Points: 95460 More actions · Answer 1

Please read this article[/url] for how to perform running totals.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 2

I don't believe running totals are going to help here, folks. This isn't a flat file... it's a Hierarchy.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 3

Shifting gears, if you still need help with this, let me know and I'll see what I can do over the weekend.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jason Selburg SSC-Insane Points: 24641 More actions · Answer 4

For clarification can you post the expected results. 😀

______________________________________________________________________

Personal Motto: Why push the envelope when you can just open it?

If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

Jason L. Selburg

Fraggle-805517 SSCrazy Eights Points: 8907 More actions · Answer 5

Jeff Moden (4/21/2011)
I don't believe running totals are going to help here, folks. This isn't a flat file... it's a Hierarchy.

You are correct. That is why the running total wouldn't work.

Fraggle.

Fraggle-805517 SSCrazy Eights Points: 8907 More actions · Answer 6

Jeff Moden (4/22/2011)
Shifting gears, if you still need help with this, let me know and I'll see what I can do over the weekend.

Below is what I ended up doing. The base table I provided was achieved through a Recursive CTE to pull that data. To get the final numbers, I ended having to do the following once I got the original dataset I provided. Note that I am providing you with the original CTE as well.

Fraggle

-- Let's get a list of all of the items that have child items

; WITH cte_InvGroups (ChildInvID, ParentUp1Level, ParentTopLevel, Level, QTY) as

(

select distinct ChildInventoryID, ParentInventoryID, ParentInventoryID, 1, IG.Quantity

from InventoryGroups as IG WITH(NOLOCK)

INNER JOIN OnlineOrderDetails as OOD WITH(NOLOCK) ON IG.ParentInventoryID = OOD.InventoryID

WHERE OOD.OnlineOrderID = @OrderID

UNION ALL

SELECT IG.ChildInventoryID, IG.ParentInventoryID, CTE.ParentTopLevel, level + 1, IG.Quantity

FROM cte_InvGroups as CTE

INNER JOIN InventoryGroups as IG WITH(NOLOCK) on CTE.ChildInvID = IG.ParentInventoryID

)

-- Drop information into a temp table to use

select ChildInvID

, ParentUp1Level

, ParentTopLevel

, Level

, qty as OriginalQuantity

, 0 AS ParentQuantity

, 0 AS FinalQuantity

into #InvList

from cte_invgroups

UNION ALL

-- Make sure we get the original items from the query as we didn't get them from the CTE above. Notice that we also need quantity.

SELECT OOD.InventoryID

, 0 as ParentUp1Level

, 0 as ParentTopLevel

, 0 as Level

, ood.quantity

, 0 as ParentQuantity

, ood.Quantity

from OnlineOrderDetails AS OOD

WHERE OOD.OnlineOrderID = @OrderID

ORDER BY Level

-- Now we need to update. This has to be done to correct values from parents. If someone can figure out a way to move this into the CTE that is faster, that would be AWESOME!!!!!!! I was not able.

UPDATE IL

SET IL.ParentQuantity = COALESCE(IL2.OriginalQuantity,1)

FROM #InvList IL

LEFT JOIN #InvList IL2

ON IL.ParentTopLevel = IL2.ChildInvID

-- Now it is time to calculate the final quantities of each unit

;WITH cte_final as

(-- Start at the top again.

SELECT ChildInvID, ParentUp1Level, OriginalQuantity, ParentQuantity, FinalQuantity

FROM #InvList IL

where level = 0

union all

-- Go downline and take the

select I.ChildInvID, I.ParentUp1Level, I.OriginalQuantity, I.ParentQuantity, CTE.FinalQuantity * I.OriginalQuantity

from cte_final as cte

INNER JOIN #InvList as I ON CTE.ChildInvID = I.ParentUp1Level

)

select *

from cte_final

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 7

Fraggle-805517 (4/22/2011)
Jeff Moden (4/22/2011)
Shifting gears, if you still need help with this, let me know and I'll see what I can do over the weekend.
Below is what I ended up doing.

So you're all set then?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

LutzM SSC Guru Points: 107049 More actions · Answer 8

Would the following snippet do the trick?

-- Drop information into a temp table to use

select ChildInvID

, ParentUp1Level

, ParentTopLevel

, Level

-- , qty as OriginalQuantity

, COALESCE(qty,1) as OriginalQuantity

, 0 AS ParentQuantity

, 0 AS FinalQuantity

into #InvList

from cte_invgroups

UNION ALL

-- Make sure we get the original items from the query as we didn't get them from the CTE above. Notice that we also need quantity.

SELECT OOD.InventoryID

, 0 as ParentUp1Level

, 0 as ParentTopLevel

, 0 as Level

-- , ood.quantity

, COALESCE(ood.quantity,1)

, 0 as ParentQuantity

, ood.Quantity

from OnlineOrderDetails AS OOD

WHERE OOD.OnlineOrderID = @OrderID

ORDER BY Level

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]