t-sql Cursor help
I have created BOM tree,after that i need different calculations
I tried to used following cursor,but not sure how to do
Expected Results in "I" Column
Results.xlsx
I tried to used following cursor,but not sure how to do
DECLARE @Level11 decimal(25,10)
DECLARE @Level22 decimal(25,10)
DECLARE @Level33 decimal(25,10)
DECLARE @Level44 decimal(25,10)
DECLARE db_cursor CURSOR FOR
SELECT tQty,tLevel,ParentItem,tItem
FROM @tempOutput
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @tQty,@tLevel,@ParentItem,@tItem
WHILE @@FETCH_STATUS = 0
BEGIN
IF(@tLevel=1)
BEGIN
SELECT @Level11=tQty FROM @tempOutput WHERE tpmt='M' AND tLevel=1 AND ParentItem=@ParentItem AND tItem=@tItem
END
IF(@tLevel=2)
BEGIN
SELECT @Level22=tQty FROM @tempOutput WHERE tpmt='M' AND tLevel=2 AND ParentItem=@ParentItem AND tItem=@tItem
END
IF(@tLevel=3)
BEGIN
UPDATE @tempOutput
SET SumLbrAmount1=@Level11 ,SumLbrAmount2=@Level22
WHERE tpmt='P' AND tLevel=3 AND ParentItem=@ParentItem AND tItem=@tItem
END
FETCH NEXT FROM db_cursor INTO @tQty,@tLevel ,@ParentItem,@tItem
END
CLOSE db_cursor
DEALLOCATE db_cursorExpected Results in "I" Column
Results.xlsx
Sharath S
Can you explain what you are trying to achieve? May be, you don't need the cursor.
ASKER
I need to calculate each Level quantity (only if 'P')
for example
Level Qty type Results
0 1 M
1 5 M
2 4 M
3 4 P =1+5+4+4
1 8 M
2 6 P =8+6
1 20 M
2 9 M
3 12 M =1+5+4+4
4 11 P =20+9+12+11
2 11 P =20+11
3 90 P =20+90
Then need to results should be if is 'P' in the level need to calculate ,it's only 4 levels
This is final sql in the Stored prcedure
SELECT
tmp.seq,
tmp.seqParentItem,
tmp.ParentItem,
tmp.tLevel,
tmp.tItem,
tmp.tpmt,
tmp.tQty,
tmp.tum,
tmp.tunit,
tmp.tLot,
tmp.tType,
tmp.tMatl,
tmp.tOvhd,
tmp.tDesc,
tmp.tLabor,
tmp.tOuts,
tmp.QtyPerFormat,
tmp.PlacesQtyPer,
tmp.CstPrcFormat,
tmp.PlacesCp
,tmp.TotalLabor
,tmp.TotalOverhead
,ISNULL(tmp.SumLbrAmount0,
,ISNULL(tmp.SumLbrAmount1,
,ISNULL(tmp.SumLbrAmount2,
,ISNULL(tmp.SumLbrAmount3,
,(tmp.TotalLabor *tmp.tQty *ISNULL(tmp.SumLbrAmount0,
,(tmp.TotalOverhead *tmp.tQty *ISNULL(tmp.SumLbrAmount0,
,(CASE WHEN tmp.tpmt='P' THEN (tmp.tQty * tmp.tMatl)*ISNULL(tmp.SumL
FROM @tempOutput tmp
for example
Level Qty type Results
0 1 M
1 5 M
2 4 M
3 4 P =1+5+4+4
1 8 M
2 6 P =8+6
1 20 M
2 9 M
3 12 M =1+5+4+4
4 11 P =20+9+12+11
2 11 P =20+11
3 90 P =20+90
Then need to results should be if is 'P' in the level need to calculate ,it's only 4 levels
This is final sql in the Stored prcedure
SELECT
tmp.seq,
tmp.seqParentItem,
tmp.ParentItem,
tmp.tLevel,
tmp.tItem,
tmp.tpmt,
tmp.tQty,
tmp.tum,
tmp.tunit,
tmp.tLot,
tmp.tType,
tmp.tMatl,
tmp.tOvhd,
tmp.tDesc,
tmp.tLabor,
tmp.tOuts,
tmp.QtyPerFormat,
tmp.PlacesQtyPer,
tmp.CstPrcFormat,
tmp.PlacesCp
,tmp.TotalLabor
,tmp.TotalOverhead
,ISNULL(tmp.SumLbrAmount0,
,ISNULL(tmp.SumLbrAmount1,
,ISNULL(tmp.SumLbrAmount2,
,ISNULL(tmp.SumLbrAmount3,
,(tmp.TotalLabor *tmp.tQty *ISNULL(tmp.SumLbrAmount0,
,(tmp.TotalOverhead *tmp.tQty *ISNULL(tmp.SumLbrAmount0,
,(CASE WHEN tmp.tpmt='P' THEN (tmp.tQty * tmp.tMatl)*ISNULL(tmp.SumL
FROM @tempOutput tmp
Level Qty type Results
0 1 M
1 5 M
2 4 M
3 4 P =1+5+4+4
1 8 M
2 6 P =8+6
1 20 M
2 9 M
3 12 M =1+5+4+4 Why is it calculated for M?
4 11 P =20+9+12+11
2 11 P =20+11
3 90 P =20+90 Why level 2 qty with type M is not added here? i.e. 20+9+90?
0 1 M
1 5 M
2 4 M
3 4 P =1+5+4+4
1 8 M
2 6 P =8+6
1 20 M
2 9 M
3 12 M =1+5+4+4 Why is it calculated for M?
4 11 P =20+9+12+11
2 11 P =20+11
3 90 P =20+90 Why level 2 qty with type M is not added here? i.e. 20+9+90?
ASKER
Yes , see below
Level Qty type Results
0 1 M
1 5 M
2 4 M
3 4 P =1+5+4+4
1 8 M
2 6 P =8+6
1 20 M
2 9 M
3 12 M
4 11 P =20+9+12+11
2 11 P =20+11
3 90 P =20+9+90
Level Qty type Results
0 1 M
1 5 M
2 4 M
3 4 P =1+5+4+4
1 8 M
2 6 P =8+6
1 20 M
2 9 M
3 12 M
4 11 P =20+9+12+11
2 11 P =20+11
3 90 P =20+9+90
ASKER
See below code I wrote
--===============================================================================================================
;With UpdateData As
(
SELECT seq,
ROW_NUMBER() OVER(PARTITION BY ParentItem ORDER BY seq) AS RN
FROM @tempOutput
)
UPDATE tmp SET tmp.seqParentItem = UpdateData.RN
FROM @tempOutput tmp
INNER JOIN UpdateData ON tmp.seq = UpdateData.seq
--===============================================================================================================
--Mat updates
--===============================================================================================================
DECLARE @Level11 decimal(25,10)
DECLARE @Level22 decimal(25,10)
DECLARE @Level33 decimal(25,10)
DECLARE @Level44 decimal(25,10)
DECLARE @ParentItem nvarchar(30)
DECLARE db_cursor CURSOR FOR
SELECT tQty,tLevel,ParentItem,tItem
FROM @tempOutput
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @tQty,@tLevel,@ParentItem,@tItem
WHILE @@FETCH_STATUS = 0
BEGIN
IF(@tLevel=1)
BEGIN
SELECT @Level11=tQty FROM @tempOutput WHERE tpmt='M' AND tLevel=1 AND ParentItem=@ParentItem AND tItem=@tItem
END
IF(@tLevel=2)
BEGIN
SELECT @Level22=tQty FROM @tempOutput WHERE tpmt='M' AND tLevel=2 AND ParentItem=@ParentItem AND tItem=@tItem
END
IF(@tLevel=3)
BEGIN
SELECT @Level33=tQty FROM @tempOutput WHERE tpmt='M' AND tLevel=3 AND ParentItem=@ParentItem AND tItem=@tItem
END
IF(@tLevel=3)
BEGIN
UPDATE @tempOutput
SET SumLbrAmount1=@Level11 ,SumLbrAmount2=@Level22
WHERE tpmt='P' AND tLevel=3 AND ParentItem=@ParentItem AND tItem=@tItem
END
IF(@tLevel=4)
BEGIN
UPDATE @tempOutput
SET SumLbrAmount1=@Level11 ,SumLbrAmount2=@Level22,SumLbrAmount3=@Level33
WHERE tpmt='P' AND tLevel=4 AND ParentItem=@ParentItem AND tItem=@tItem
END
IF(@tLevel=2)
BEGIN
UPDATE @tempOutput
SET SumLbrAmount1=@Level11
WHERE tpmt='P' AND tLevel=2 AND ParentItem=@ParentItem AND tItem=@tItem
END
FETCH NEXT FROM db_cursor INTO @tQty,@tLevel ,@ParentItem,@tItem
END
CLOSE db_cursor
DEALLOCATE db_cursor
--===============================================================================================================
--Results Sql
--===============================================================================================================
SELECT
tmp.seq,
tmp.seqParentItem,
tmp.ParentItem,
tmp.tLevel,
tmp.tItem,
tmp.tpmt,
tmp.tQty,
tmp.tum,
tmp.tunit,
tmp.tLot,
tmp.tType,
tmp.tMatl,
tmp.tOvhd,
tmp.tDesc,
tmp.tLabor,
tmp.tOuts,
tmp.QtyPerFormat,
tmp.PlacesQtyPer,
tmp.CstPrcFormat,
tmp.PlacesCp
,tmp.TotalLabor
,tmp.TotalOverhead
,ISNULL(tmp.SumLbrAmount0,0) AS [SumLbrAmount0]
,ISNULL(tmp.SumLbrAmount1,0) AS [SumLbrAmount1]
,ISNULL(tmp.SumLbrAmount2,0) AS [SumLbrAmount2]
,ISNULL(tmp.SumLbrAmount3,0) AS [SumLbrAmount3]
,(tmp.TotalLabor *tmp.tQty *ISNULL(tmp.SumLbrAmount0,1)*ISNULL(tmp.SumLbrAmount1,1)*ISNULL(tmp.SumLbrAmount2,1)*ISNULL(tmp.SumLbrAmount3,1)) AS LabourCost
,(tmp.TotalOverhead *tmp.tQty *ISNULL(tmp.SumLbrAmount0,1)*ISNULL(tmp.SumLbrAmount1,1)*ISNULL(tmp.SumLbrAmount2,1)*ISNULL(tmp.SumLbrAmount3,1)) AS OverheadCost
,(CASE WHEN tmp.tpmt='P' THEN (tmp.tQty * tmp.tMatl)*ISNULL(tmp.SumLbrAmount0,1)*ISNULL(tmp.SumLbrAmount1,1)*ISNULL(tmp.SumLbrAmount2,1)*ISNULL(tmp.SumLbrAmount3,1) END ) AS Mallt
FROM @tempOutput tmp
ORDER BY seq
Can you try this .. ? You many need to modify few things in this I guess
--
Pawan Kumar Khowal
--
;WITH CTE AS
(
SELECT * , ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) rnk
FROM
testLogic
)
SELECT e.Level,e.Type,e.Result, r.type - ISNULL(CASE WHEN r.Type IS NOT NULL THEN LAG(r.Type) OVER (ORDER BY CASE WHEN r.Type IS NULL THEN -1 ELSE r.Type END) END,0) R
FROM CTE e
CROSS APPLY
(
SELECT SUM(Type) Type FROM CTE z
WHERE z.rnk <= e.rnk AND e.Result = 'P'
)r
ORDER BY e.rnk
----
Pawan Kumar Khowal
ASKER
This 2008 R2 Server ,i don't think LAG function supporting for this
Yes LAG will not work in there. LAG was introduced in 2012.
Do you need any help or you are done. If yes then mark one solution as accepted and close the question.
Thank you!
Do you need any help or you are done. If yes then mark one solution as accepted and close the question.
Thank you!
ASKER
I need help to solve the problem is LAG is not working, so i can't use your code to test.
Looking forward more helpfull comments
Looking forward more helpfull comments
Wait.. I shall change my code and update you in sometime.
Okay update , apart from last 2 rows, below solution will work..in SQL 2008 R2
--
Pawan Kumar Khowal
--
;WITH CTE AS
(
SELECT * , ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) rnk
FROM
testLogic
)
,CTE1 AS
(
SELECT e.Level,e.Type,e.Result, r.type NewType ,e.rnk
FROM CTE e
CROSS APPLY
(
SELECT SUM(Type) Type FROM CTE z
WHERE z.rnk <= e.rnk AND e.Result = 'P'
)r
)
,CTE2 AS
(
SELECT aa.Level,aa.Type,aa.Result,aa.rnk, aa.NewType - ISNULL(CASE WHEN aa.NewType IS NULL THEN NULL ELSE er.Re END,0) NewType
FROM CTE1 aa
OUTER APPLY
(
SELECT TOP 1 NewType Re
FROM CTE1 bb
WHERE bb.rnk < aa.rnk
AND NewType IS NOT NULL
ORDER BY bb.rnk DESC
)er
)
SELECT * FROM CTE2
----
Pawan Kumar Khowal
Working for last 2 rows :)
Table Data
--
CREATE TABLE testLogic
(
Level TINYINT
,Type TINYINT
,Result CHAR(1)
)
GO
INSERT INTO testLogic VALUES
(0, 1 ,'M'),
(1, 5 ,'M'),
(2, 4 ,'M'),
(3, 4 ,'P'),
(1, 8 ,'M'),
(2, 6 ,'P'),
(1, 20 ,'M'),
(2, 9 ,'M'),
(3, 12 ,'M'),
(3, 11 ,'P'),
(2, 11 ,'P'),
(3, 90 ,'P')
GO
--ASKER
Any updates?
Yes , I am working on it. Yesterday didn't get time :)
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
@Author - Do you time to look into the solution I gave?
Thanks !
Thanks !
@Author - Any update on this ?
Thanks !
Thanks !
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Points are achieved by presenting a correct solution and not for the time spent.
I object.
Disagree with Vitor. 2 perfect solutions have been given.
Disagree with Vitor. 2 perfect solutions have been given.