Solved

t-sql Cursor help

Posted on 2016-09-15
24
60 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 40

Expert Comment

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

Author Comment

by:ukerandi
Comment Utility
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 40

Expert Comment

by:Sharath
Comment Utility
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
 
LVL 10

Author Comment

by:ukerandi
Comment Utility
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
Comment Utility
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 17

Expert Comment

by:Pawan Kumar Khowal
Comment Utility
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
Comment Utility
This 2008 R2 Server ,i don't think LAG function supporting for this
0
 
LVL 17

Expert Comment

by:Pawan Kumar Khowal
Comment Utility
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
Comment Utility
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 17

Expert Comment

by:Pawan Kumar Khowal
Comment Utility
Wait.. I shall change my code and update you in sometime.
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 17

Expert Comment

by:Pawan Kumar Khowal
Comment Utility
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 17

Expert Comment

by:Pawan Kumar Khowal
Comment Utility
Working for last 2 rows :)
0
 
LVL 17

Expert Comment

by:Pawan Kumar Khowal
Comment Utility
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
Comment Utility
Any updates?
0
 
LVL 17

Expert Comment

by:Pawan Kumar Khowal
Comment Utility
Yes , I am working on it. Yesterday didn't get time :)
0
 
LVL 17

Assisted Solution

by:Pawan Kumar Khowal
Pawan Kumar Khowal earned 500 total points
Comment Utility
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 17

Expert Comment

by:Pawan Kumar Khowal
Comment Utility
@Author - Do you time to look into the solution I gave?
Thanks !
0
 
LVL 17

Expert Comment

by:Pawan Kumar Khowal
Comment Utility
@Author - Any update on this ?
Thanks !
0
 
LVL 17

Accepted Solution

by:
Pawan Kumar Khowal earned 500 total points
Comment Utility
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 45

Expert Comment

by:Vitor Montalvão
Comment Utility
Points are achieved by presenting a correct solution and not for the time spent.
0
 
LVL 17

Expert Comment

by:Pawan Kumar Khowal
Comment Utility
I object.

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

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
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.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

744 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now