Solved

t-sql Cursor help

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

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
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 
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 28

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 28

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 28

Expert Comment

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

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 28

Expert Comment

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

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 28

Expert Comment

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

Assisted Solution

by:Pawan Kumar
Pawan Kumar earned 500 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 28

Expert Comment

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

Expert Comment

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

Accepted Solution

by:
Pawan Kumar earned 500 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 48

Expert Comment

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

Expert Comment

by:Pawan Kumar
ID: 41831616
I object.

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

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
tempdb log keep growing 7 33
Re-appearing SQL Server Agent jobs 7 28
Update one table with results from another table in SQL 6 22
SQL Group By Question 4 18
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

808 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