Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

t-sql Cursor help

Posted on 2016-09-15
24
Medium Priority
?
108 Views
Last Modified: 2016-10-14
I  have created BOM tree,after that i need different calculations
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_cursor

Open in new window

if you cheked my attached  Excel sheet you can understand what iam expecting

Expected Results in "I" Column
Results.xlsx
0
Comment
Question by:ukerandi
  • 12
  • 6
  • 2
  • +1
24 Comments
 
LVL 41

Expert Comment

by:Sharath
ID: 41800735
Can you explain what you are trying to achieve? May be, you don't need the cursor.
0
 
LVL 10

Author Comment

by:ukerandi
ID: 41800743
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,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
0
 
LVL 41

Expert Comment

by:Sharath
ID: 41800752
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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 10

Author Comment

by:ukerandi
ID: 41801082
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
0
 
LVL 10

Author Comment

by:ukerandi
ID: 41801122
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

Open in new window

0
 
LVL 35

Expert Comment

by:Pawan Kumar
ID: 41801178
Can you try this .. ? You many need to modify few things in this I guess

--

;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


--

Open in new window


--

Pawan Kumar Khowal
0
 
LVL 10

Author Comment

by:ukerandi
ID: 41801233
This 2008 R2 Server ,i don't think LAG function supporting for this
0
 
LVL 35

Expert Comment

by:Pawan Kumar
ID: 41801239
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!
0
 
LVL 10

Author Comment

by:ukerandi
ID: 41801259
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
0
 
LVL 35

Expert Comment

by:Pawan Kumar
ID: 41801260
Wait.. I shall change my code and update you in sometime.
0
 
LVL 35

Expert Comment

by:Pawan Kumar
ID: 41801419
Okay update , apart from last 2 rows, below solution will work..in SQL 2008 R2


--

;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




--

Open in new window


--
Pawan Kumar Khowal
0
 
LVL 35

Expert Comment

by:Pawan Kumar
ID: 41801420
Working for last 2 rows :)
0
 
LVL 35

Expert Comment

by:Pawan Kumar
ID: 41801422
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




--

Open in new window

0
 
LVL 10

Author Comment

by:ukerandi
ID: 41802946
Any updates?
0
 
LVL 35

Expert Comment

by:Pawan Kumar
ID: 41802947
Yes , I am working on it. Yesterday didn't get time :)
0
 
LVL 35

Assisted Solution

by:Pawan Kumar
Pawan Kumar earned 2000 total points
ID: 41803468
Here you go bro ! complete solution ..

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'),	
(4,	11	,'P'),	
(2,	11	,'P'),	
(3,	90	,'P')	
GO


/*
** SOLUTION 1
*/

;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
)
,CTE3 AS
(
	SELECT * FROM CTE2 ert1
	OUTER APPLY
	(	
		SELECT TOP 1 ert2.rnk rty
		FROM CTE2 ert2
		WHERE ert1.Result = 'P' and (ert2.rnk+1) = (ert1.rnk) 
		AND ert2.Result = ert1.Result			
	)rert
)
,CTE4 AS 
(	
	SELECT MIN(rnk) startrnk , MAX(rnk) mrnk ,newrnk, 'P' as Result FROM 
	(
		SELECT Result , rnk , rnk - ROW_NUMBER() OVER (ORDER BY rnk) newrnk FROM CTE3
		WHERE Result = 'P'	
	)weq GROUP BY newrnk
	HAVING COUNT(*) > 1
)
,CTE5 AS
(
	SELECT * FROM CTE3 ct3
	OUTER APPLY
	(
		SELECT startrnk , mrnk,newrnk,Result Result1 FROM CTE4 ct4
		WHERE ct3.rnk BETWEEN ct4.startrnk AND ct4.mrnk
	)nm
)
SELECT ott.Level , ott.Type ,ott.Result , ISNULL( NewType + ISNULL(CASE WHEN rty IS NOT NULL THEN ( SELECT SUM(c5.Type) FROM CTE5 c5 WHERE ( c5.rnk BETWEEN ott.newrnk AND ott.startrnk ) 
AND c5.Level < ott.Level
AND c5.Result = 'M' ) ELSE NULL END,0) , '') Results
FROM CTE5 ott


--

Open in new window



--


ENJOY

Pawan Khowal
MSBISKILLS.Com
0
 
LVL 35

Expert Comment

by:Pawan Kumar
ID: 41804225
@Author - Do you time to look into the solution I gave?
Thanks !
0
 
LVL 35

Expert Comment

by:Pawan Kumar
ID: 41804638
@Author - Any update on this ?
Thanks !
0
 
LVL 35

Accepted Solution

by:
Pawan Kumar earned 2000 total points
ID: 41806439
One more elegant solution for this..

;With  cte1 as(
Select *,sum(IsP_Single) OVER (ORDER BY (select ID) ROWS BETWEEN 1 preceding  and CURRENT ROW ) NoOFLevelLookBack from
(
       Select *, iif(count(1) over(partition by PorM)>1,0,1) IsP_Single 
              from (
                     select *, sum(case when ch='Z' then 1 else 0 end ) OVER (ORDER BY (select ID) ROWS BETWEEN CURRENT ROW and unbounded following) as PorM
                     from 
                     (
                           select row_number() over (order by (select null)) as ID,*  from testLogic1
                     ) t1
              )t2
       )t3
)

Select z.*,sum(z.Val) over (partition by z.PorM) + t.aaa as sol
from cte1  z left join(
              select max(a.ID) id,Sum( IsNull(b.Val,0))aaa 
                     from cte1 a 
                     left join cte1 b on (a.PorM+ a.NoOFLevelLookBack)= b.PorM and a.lev>b.lev and a.NoOFLevelLookBack<>0
              group by a.PorM
       ) t on z.ID=t.id
order by z.id

Open in new window

0
 
LVL 52

Expert Comment

by:Vitor Montalvão
ID: 41831590
Points are achieved by presenting a correct solution and not for the time spent.
0
 
LVL 35

Expert Comment

by:Pawan Kumar
ID: 41831616
I object.

Disagree with Vitor.  2 perfect solutions have been given.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

886 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question