We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

CTE PROBLEM

Medium Priority
339 Views
Last Modified: 2012-05-11
Hi All,

I have query below


; WITH CTEA (Tipe, Produk, Qty, Cost ) AS  (
SELECT Tipe, Produk, Qty, Cost FROM
(
SELECT 'I1' AS Tipe, 'A' Produk, 2 AS Qty, 10 AS Cost
UNION ALL
SELECT 'I2' AS Tipe, 'A' Produk, 2 AS Qty, 10 AS Cost
UNION ALL
SELECT 'O1' AS Tipe, 'A' Produk, 3 AS Qty, 0 AS Cost
UNION ALL
SELECT 'O2' AS Tipe, 'A' Produk, 1 AS Qty, 0 AS Cost
) A )  
 
; WITH CTEB AS (
SELECT Tipe, Qty, Cost FROM CTEA
WHERE LEFT(Tipe, 1) = 'I'

UNION ALL
SELECT Tipe, Qty, Cost FROM CTEA

INNER JOIN CTEB
ON CTEA.Produk = CTEB.Produk
WHERE LEFT(Tipe, 1) = 'O'
)

Reported error :

Msg 102, Level 15, State 1, Line 13
Incorrect syntax near ';'.
Msg 102, Level 15, State 1, Line 23
Incorrect syntax near ')'.

What's wrong with the code ?

Thank you.
Comment
Watch Question

sarabhaiDeveloper
CERTIFIED EXPERT

Commented:
CTE statement finished with semicolon. (;) not start with semicolon.
sarabhaiDeveloper
CERTIFIED EXPERT

Commented:
WITH CTEA (Tipe, Produk, Qty, Cost ) AS  (
SELECT Tipe, Produk, Qty, Cost FROM
(
SELECT 'I1' AS Tipe, 'A' Produk, 2 AS Qty, 10 AS Cost
UNION ALL
SELECT 'I2' AS Tipe, 'A' Produk, 2 AS Qty, 10 AS Cost
UNION ALL
SELECT 'O1' AS Tipe, 'A' Produk, 3 AS Qty, 0 AS Cost
UNION ALL
SELECT 'O2' AS Tipe, 'A' Produk, 1 AS Qty, 0 AS Cost
) A ),  
 
 CTEB AS (
SELECT Tipe, Qty, Cost FROM CTEA
WHERE LEFT(Tipe, 1) = 'I'

UNION ALL
SELECT Tipe, Qty, Cost FROM CTEA

INNER JOIN CTEB
ON CTEA.Produk = CTEB.Produk
WHERE LEFT(Tipe, 1) = 'O'
)


this is for multiple cte defined.

Author

Commented:
Where to put it ?

I have tried several position  and still error.

Thank you.
SharathData Engineer
CERTIFIED EXPERT

Commented:
try this.
;WITH CTEA(Tipe,Produk,Qty,COST) 
     AS (SELECT Tipe, 
                Produk, 
                Qty, 
                COST 
           FROM (SELECT 'I1' AS Tipe, 
                        'A'  Produk, 
                        2    AS Qty, 
                        10   AS COST 
                 UNION ALL 
                 SELECT 'I2' AS Tipe, 
                        'A'  Produk, 
                        2    AS Qty, 
                        10   AS COST 
                 UNION ALL 
                 SELECT 'O1' AS Tipe, 
                        'A'  Produk, 
                        3    AS Qty, 
                        0    AS COST 
                 UNION ALL 
                 SELECT 'O2' AS Tipe, 
                        'A'  Produk, 
                        1    AS Qty, 
                        0    AS COST) A), 
     CTEB 
     AS (SELECT Tipe, 
                Qty, 
                COST 
           FROM CTEA 
          WHERE LEFT(Tipe,1) = 'I' 
         UNION ALL 
         SELECT Tipe, 
                Qty, 
                COST 
           FROM CTEA 
                INNER JOIN CTEB 
                  ON CTEA.Produk = CTEB.Produk 
          WHERE LEFT(Tipe,1) = 'O') 
SELECT * 
  FROM CTEB

Open in new window

sarabhaiDeveloper
CERTIFIED EXPERT

Commented:
can you put your batch/store procedure that uses this statement.

Author

Commented:
Hi Sharath_123,

Why I can not use my code above ?

I have

Msg 530, Level 16, State 1, Line 1
The statement terminated. The maximum recursion 100 has been exhausted before statement completion.

How to solve this ?

Thank you.



;WITH CTEA(Tipe,Produk,Qty,COST) 
     AS (SELECT Tipe, 
                Produk, 
                Qty, 
                COST 
           FROM (SELECT 'I1' AS Tipe, 
                        'A'  Produk, 
                        2    AS Qty, 
                        10   AS COST 
                 UNION ALL 
                 SELECT 'I2' AS Tipe, 
                        'A'  Produk, 
                        2    AS Qty, 
                        10   AS COST 
                 UNION ALL 
                 SELECT 'O1' AS Tipe, 
                        'A'  Produk, 
                        3    AS Qty, 
                        0    AS COST 
                 UNION ALL 
                 SELECT 'O2' AS Tipe, 
                        'A'  Produk, 
                        1    AS Qty, 
                        0    AS COST) A), 
     CTEB 
     AS (SELECT Tipe, 
				Produk, 
                Qty, 
                COST 
           FROM CTEA 
          WHERE Tipe = 'I1' 
         UNION ALL 
         SELECT CTEA.Tipe, 
                CTEA.Produk, 
                CTEA.Qty, 
                CTEA.COST 
           FROM CTEA 
                INNER JOIN CTEB 
                  ON CTEA.Produk = CTEB.Produk  ) 
SELECT * 
  FROM CTEB

Open in new window

Author

Commented:
Hi sarabhai,

I don't get your point.

Thank you.
SharathData Engineer
CERTIFIED EXPERT

Commented:
You are getting the maximum recursion error because you have JOIN with CTEB inside CTEB. I need to understand your logic to correct that.

Author

Commented:
Let's make it simple, could we stop it using number of times recursive it ?

Thank you.
sarabhaiDeveloper
CERTIFIED EXPERT

Commented:
please post your complete SQL statement.
can you show me your complete code.

Author

Commented:
That is the complete code, I just want to learn CTE with my own data.

Thank you.
SharathData Engineer
CERTIFIED EXPERT

Commented:
To stop the max recursion, you can comment out the recursive CTE call.
;WITH CTEA(Tipe,Produk,Qty,COST) 
     AS (SELECT Tipe, 
                Produk, 
                Qty, 
                COST 
           FROM (SELECT 'I1' AS Tipe, 
                        'A'  Produk, 
                        2    AS Qty, 
                        10   AS COST 
                 UNION ALL 
                 SELECT 'I2' AS Tipe, 
                        'A'  Produk, 
                        2    AS Qty, 
                        10   AS COST 
                 UNION ALL 
                 SELECT 'O1' AS Tipe, 
                        'A'  Produk, 
                        3    AS Qty, 
                        0    AS COST 
                 UNION ALL 
                 SELECT 'O2' AS Tipe, 
                        'A'  Produk, 
                        1    AS Qty, 
                        0    AS COST) A), 
     CTEB 
     AS (SELECT Tipe, 
				Produk, 
                Qty, 
                COST 
           FROM CTEA 
          WHERE Tipe = 'I1' 
         UNION ALL 
         SELECT CTEA.Tipe, 
                CTEA.Produk, 
                CTEA.Qty, 
                CTEA.COST 
           FROM CTEA 
                --INNER JOIN CTEB 
                  --ON CTEA.Produk = CTEB.Produk  
                  ) 
SELECT * 
  FROM CTEB maxrecursion;

Open in new window

Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
you need to do:
; WITH CTEA (Tipe, Produk, Qty, Cost ) AS  (
SELECT Tipe, Produk, Qty, Cost FROM 
(
SELECT 'I1' AS Tipe, 'A' Produk, 2 AS Qty, 10 AS Cost
UNION ALL
SELECT 'I2' AS Tipe, 'A' Produk, 2 AS Qty, 10 AS Cost
UNION ALL
SELECT 'O1' AS Tipe, 'A' Produk, 3 AS Qty, 0 AS Cost
UNION ALL
SELECT 'O2' AS Tipe, 'A' Produk, 1 AS Qty, 0 AS Cost 
) A )  
 
, CTEB AS (
SELECT Tipe, Qty, Cost FROM CTEA
WHERE LEFT(Tipe, 1) = 'I'

UNION ALL
SELECT Tipe, Qty, Cost FROM CTEA

INNER JOIN CTEB
ON CTEA.Produk = CTEB.Produk
WHERE LEFT(Tipe, 1) = 'O'
) 
select * from cteB

Open in new window

Author

Commented:
Hi angelIII,

Still problem with max recursion.

Thank you.
Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
can you please show us what you except as output?

Author

Commented:
Ok, please see the revise data.

RowNum      Tipe      Produk      QtyIn      QtyOut      Cost      Balance
1      I1      A      2      0      10      2
2      I2      A      2      0      10      4
3      O1      A      0      3      0      1
4      O2      A      0      1      0      0

Expected Result :


RowNum      Tipe      Produk      QtyIn      QtyOut      Cost      Balance
1      I1      A      2      0      10      2
2      I2      A      2      0      10      4
3      O1      A      0      3      0      1
4      O2      A      0      1      0      0

OR

RowNum      Tipe      Produk      QtyIn      QtyOut      Cost             
3      O1      A      0      3      0      1   9,6667                 ' (2 * 10) + (1 * 9)
4      O2      A      0      1      0      0   9                          ' (1 * 9)

Thank you.


; WITH CTEA (RowNum, Tipe, Produk, QtyIn, QtyOut, Cost ) AS  (
SELECT  Row_Number() Over(Order by Tipe) as RowNum , Tipe, Produk,QtyIn, QtyOut, Cost FROM 
(
SELECT 'I1' AS Tipe, 'A' Produk, 2 AS QtyIn, 0 AS QtyOut, 10 AS Cost
UNION ALL
SELECT 'I2' AS Tipe, 'A' Produk, 2 AS QtyIn, 0 AS QtyOut, 10 AS Cost
UNION ALL
SELECT 'O1' AS Tipe, 'A' Produk, 0 AS QtyIn, 3 AS QtyOut, 0 AS Cost
UNION ALL
SELECT 'O2' AS Tipe, 'A' Produk, 0 AS QtyIn, 1 AS QtyOut, 0 AS Cost 
) A )  
 
, CTEB AS (
 
SELECT CTEA.RowNum, CTEA.Tipe,  CTEA.Produk,  CTEA.QtyIn,  CTEA.QtyOut,  CTEA.Cost 

 FROM CTEA
 
) 
SELECT *

, ( SELECT SUM( CTEA.QtyIn - CTEA.QtyOut ) FROM CTEA WHERE CTEA.RowNum <= CTEB.RowNum) AS Balance

 FROM cteB    ;

Open in new window

Author

Commented:
Hi Sharath_123,

Could we just show the data after max recursion without have to comment the code ?

Thank you.
Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
the first expected result is the same as the input data ... please check

in the second version:
  ' (2 * 10) + (1 * 9)

I presume you wanted to write:   ' ((2 * 10) + (1 * 9)) /  ( 2 + 1 )
but I don't see where the "9" is coming from?

please clarify
Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
btw, we won't need "recursion" here, just plain CTE ...

Author

Commented:
Please see below:


Type   Product   Qty Cost
O1        A             2      10 from I1 Cost
O1        A             1      9   from I2 Cost
O2        A             1      9   from I2 Cost

Thank you.

Author

Commented:
Sorry, the data should be :

RowNum      Tipe      Produk      QtyIn      QtyOut      Cost      Balance
1      I1      A      2      0      10      2
2      I2      A      2      0      9      4
3      O1      A      0      3      0      1
4      O2      A      0      1      0      0

Thank you.

Author

Commented:
Here is the code to generate the data.

Thank you.
; WITH CTEA (RowNum, Tipe, Produk, QtyIn, QtyOut, Cost ) AS  (
SELECT  Row_Number() Over(Order by Tipe) as RowNum , Tipe, Produk,QtyIn, QtyOut, Cost FROM 
(
SELECT 'I1' AS Tipe, 'A' Produk, 2 AS QtyIn, 0 AS QtyOut, 10 AS Cost
UNION ALL
SELECT 'I2' AS Tipe, 'A' Produk, 2 AS QtyIn, 0 AS QtyOut, 9 AS Cost
UNION ALL
SELECT 'O1' AS Tipe, 'A' Produk, 0 AS QtyIn, 3 AS QtyOut, 0 AS Cost
UNION ALL
SELECT 'O2' AS Tipe, 'A' Produk, 0 AS QtyIn, 1 AS QtyOut, 0 AS Cost 
) A )  

SELECT * FROM CTEA

Open in new window

Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
please see if you can continue from here:
; WITH CTEA (RowNum, Tipe, Produk, QtyIn, QtyOut, Cost ) AS  (
SELECT  Row_Number() Over(Order by Tipe) as RowNum , Tipe, Produk,QtyIn, QtyOut, Cost FROM 
(
SELECT 'I1' AS Tipe, 'A' Produk, 2 AS QtyIn, 0 AS QtyOut, 10 AS Cost
UNION ALL
SELECT 'I2' AS Tipe, 'A' Produk, 2 AS QtyIn, 0 AS QtyOut, 9 AS Cost
UNION ALL
SELECT 'O1' AS Tipe, 'A' Produk, 0 AS QtyIn, 3 AS QtyOut, 0 AS Cost
UNION ALL
SELECT 'O2' AS Tipe, 'A' Produk, 0 AS QtyIn, 1 AS QtyOut, 0 AS Cost 
) A )  
, CTEB AS (
  SELECT a.*, left(a.Tipe,1) x1, substring(a.tipe, 2, 100) x2
    FROM CTEA a
)
SELECT b.* , c.*
  FROM CTEB b
  JOIN CTEB c
    ON c.x2 = b.x2
   AND c.x1 = 'I'
 WHERE b.x1 = 'O'

Open in new window

Author

Commented:
Let me try first.

Thank you.

Author

Commented:
With below data does not work.

Result :

3      O1      A      0      3      0      O      1      1      I1      A      4      0      10      I      1
4      O2      A      0      1      0      O      2      2      I2      A      8      0      9      I      2

Missing O3 and O4.

Thank you.
; WITH CTEA (RowNum, Tipe, Produk, QtyIn, QtyOut, Cost ) AS  (
SELECT  Row_Number() Over(Order by Tipe) as RowNum , Tipe, Produk, QtyIn, QtyOut, Cost FROM 
(
SELECT 'I1' AS Tipe, 'A' Produk, 4 AS QtyIn, 0 AS QtyOut, 10 AS Cost
UNION ALL
SELECT 'I2' AS Tipe, 'A' Produk, 8 AS QtyIn, 0 AS QtyOut, 9 AS Cost
UNION ALL
SELECT 'O1' AS Tipe, 'A' Produk, 0 AS QtyIn, 3 AS QtyOut, 0 AS Cost
UNION ALL
SELECT 'O2' AS Tipe, 'A' Produk, 0 AS QtyIn, 1 AS QtyOut, 0 AS Cost 
UNION ALL
SELECT 'O3' AS Tipe, 'A' Produk, 0 AS QtyIn, 2 AS QtyOut, 0 AS Cost 
UNION ALL
SELECT 'O4' AS Tipe, 'A' Produk, 0 AS QtyIn, 1 AS QtyOut, 0 AS Cost 
) A )  
, CTEB AS (
  SELECT a.*, left(a.Tipe,1) x1, substring(a.tipe, 2, 100) x2
    FROM CTEA a
)

SELECT b.* , c.*
  FROM CTEB b
  JOIN CTEB c
    ON c.x2 = b.x2
   AND c.x1 = 'I'
 WHERE b.x1 = 'O'

Open in new window

Author

Commented:
Its mean when In transaction data less than Out transaction data then the query will not work.

b = 1 data
c > 1 data then query failed

 ON c.x2 = b.x2

Thank you.
Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
please change:
SELECT b.* , c.*
  FROM CTEB b
  JOIN CTEB c
    ON c.x2 = b.x2
   AND c.x1 = 'I'
 WHERE b.x1 = 'O'

into:

SELECT b.* , c.*
  FROM CTEB b
  LEFT JOIN CTEB c
    ON c.x2 = b.x2
   AND c.x1 = 'I'
 WHERE b.x1 = 'O'

Author

Commented:
There result problem.

Because the In data just 2 rows.

75      O3      A      0      2      0      O      3      NULL      NULL      NULL      NULL      NULL      NULL      NULL      NULL
6      O4      A      0      1      0      O      4      NULL      NULL      NULL      NULL      NULL      NULL      NULL      NULL

Thank you.
Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
you must have some other conditions:

; WITH CTEA (RowNum, Tipe, Produk, QtyIn, QtyOut, Cost ) AS  (
SELECT  Row_Number() Over(Order by Tipe) as RowNum , Tipe, Produk, QtyIn, QtyOut, Cost FROM 
(
SELECT 'I1' AS Tipe, 'A' Produk, 4 AS QtyIn, 0 AS QtyOut, 10 AS Cost
UNION ALL
SELECT 'I2' AS Tipe, 'A' Produk, 8 AS QtyIn, 0 AS QtyOut, 9 AS Cost
UNION ALL
SELECT 'O1' AS Tipe, 'A' Produk, 0 AS QtyIn, 3 AS QtyOut, 0 AS Cost
UNION ALL
SELECT 'O2' AS Tipe, 'A' Produk, 0 AS QtyIn, 1 AS QtyOut, 0 AS Cost 
UNION ALL
SELECT 'O3' AS Tipe, 'A' Produk, 0 AS QtyIn, 2 AS QtyOut, 0 AS Cost 
UNION ALL
SELECT 'O4' AS Tipe, 'A' Produk, 0 AS QtyIn, 1 AS QtyOut, 0 AS Cost 
) A )  
, CTEB AS (
  SELECT a.*, left(a.Tipe,1) x1, substring(a.tipe, 2, 100) x2
    FROM CTEA a
)

SELECT b.* , c.*
  FROM CTEB b
  LEFT JOIN CTEB c
    ON c.x2 = b.x2
   AND c.x1 = 'I'
 WHERE b.x1 = 'O'

Open in new window

output
RowNum	Tipe	Produk	QtyIn	QtyOut	Cost	x1	x2	RowNum	Tipe	Produk	QtyIn	QtyOut	Cost	x1	x2
3	O1	A	0	3	0	O	1	1	I1	A	4	0	10	I	1
4	O2	A	0	1	0	O	2	2	I2	A	8	0	9	I	2
5	O3	A	0	2	0	O	3	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
6	O4	A	0	1	0	O	4	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL

(4 row(s) affected)

Open in new window


if you have a condition on other fields (for example, QtyOut > 1 )
 ...

SELECT b.* , c.*
  FROM CTEB b
  LEFT JOIN CTEB c
    ON c.x2 = b.x2
   AND c.x1 = 'I'
   AND c.QtyOut > 1
 WHERE b.x1 = 'O'
   AND b.QtyOut > 1

Open in new window

Author

Commented:
Yes, the output like you show it.

The problem is at O3 and O4. They should have cost not null value.

Total qty In = 12 and total qty out = 7

Thank you.

Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
>The problem is at O3 and O4. They should have cost not null value.

based on what exact logic, please?

Author

Commented:
Because qty In > qty out.

I = stock in
O= stock out

I1 = 4 at cost 10, O1= 3 at cost 10, O2 = 1 at cost 10
I2 = 8 at cost 9, O3 = 2 at cost 9, O4 = 1 at cost 9.

Thank you.
Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
but how do you define that I1 is linked to O1 and O2, and I2 to O3 and O4 ?

Author

Commented:
The logic is look at the stock balance

Tipe  QtyIn  QtyOut  Balance Cost
I1      4          0         4            10
I2      8          0        12           9
O1     0          3         9          0
O2     0          1         8          0
O3     0          2         6          0
04      0          1         5         0

If could get output below then it is more easy to handle it.

Tipe  QtyIn  BuyCost   OutTipe QtyOut  SellCost
I1      4          10        O1       3         10
I1      4          10        O2       1         10

I2      8           9          O3       2        9
I2      8           9          O4       1        9

I just have no idea how to do it using sql syntax, especially CTE.

Thank you.



Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
so, you want to "assign" the qty out to the qty until "stock empty".
what if a Out is higher than what is left in the "previous" used "in".

for example, if O2 was not 1, but 3...
I see that you want to know what "BuyCost" to show for a given "SellCost" ...

but you have to tell how to "show" this...
if you tell me that this will never be the case in your system, it will be somehow possible to do this ...
please confirm.

Author

Commented:
-- what if a Out is higher than what is left in the "previous" used "in".

Tipe  QtyIn  QtyOut  Balance Cost
I1      4          0         4            10
I2      8          0        12           9
O1     0          3         9          0
O2     0          3         6          0
O3     0          2         4          0
04      0          1         3         0

If could get output below then it is more easy to handle it.

Tipe  QtyIn  BuyCost   OutTipe QtyOut  SellCost
I1      4          10        O1       3         10
I1      4          10        O2       1         10

I2      8          9          O2       2         9
I2      8           9         O3       2         9
I2      8           9         O4       1         9

Thank you.
Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
last question: is there no "date/time" information in your system, which would normally "order" the transactions?

Author

Commented:
--last question: is there no "date/time" information in your system, which would normally "order" the transactions?
Yes, it is. Just to make it simple for you, then you could assume that row number is the sorting date order.

Thank you.
 


Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
first, let's start to get the "balance" field:

; WITH CTEA (RowNum, Tipe, Produk, QtyIn, QtyOut, Cost ) AS  (
SELECT  Row_Number() Over(Order by Tipe) as RowNum , Tipe, Produk, QtyIn, QtyOut, Cost FROM 
(
SELECT 'I1' AS Tipe, 'A' Produk, 4 AS QtyIn, 0 AS QtyOut, 10 AS Cost
UNION ALL
SELECT 'I2' AS Tipe, 'A' Produk, 8 AS QtyIn, 0 AS QtyOut, 9 AS Cost
UNION ALL
SELECT 'O1' AS Tipe, 'A' Produk, 0 AS QtyIn, 3 AS QtyOut, 0 AS Cost
UNION ALL
SELECT 'O2' AS Tipe, 'A' Produk, 0 AS QtyIn, 3 AS QtyOut, 0 AS Cost 
UNION ALL
SELECT 'O3' AS Tipe, 'A' Produk, 0 AS QtyIn, 2 AS QtyOut, 0 AS Cost 
UNION ALL
SELECT 'O4' AS Tipe, 'A' Produk, 0 AS QtyIn, 1 AS QtyOut, 0 AS Cost 
) A )  
, CTEB AS (
  SELECT a.*, left(a.Tipe,1) x1, substring(a.tipe, 2, 100) x2
       , (select sum(x.Qtyin-x.Qtyout) from CTEA x where x.produk = a.produk and x.rownum <= a.rownum) balance
    FROM CTEA a
)
SELECT b.*
  FROM CTEB b
ORDER BY b.rownum

Open in new window

let me continue to work on the other part

Author

Commented:
Yes, it works fine.

Thank you.

Author

Commented:
Hi angelIII,

Any solution yet  ?

Thank you.
Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
sorry for the delay ...
I don't see a really nice way yet to do this with "pure" sql/t-sql
Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
ok, let's see if this works ok for you:
SELECT  Row_Number() Over(Order by Tipe) as RowNum , Tipe, Produk, QtyIn, QtyOut, Cost 
into tmp_data
FROM 
(
SELECT 'I1' AS Tipe, 'A' Produk, 4 AS QtyIn, 0 AS QtyOut, 10 AS Cost
UNION ALL
SELECT 'I2' AS Tipe, 'A' Produk, 8 AS QtyIn, 0 AS QtyOut, 9 AS Cost
UNION ALL
SELECT 'O1' AS Tipe, 'A' Produk, 0 AS QtyIn, 3 AS QtyOut, 12 AS Cost
UNION ALL
SELECT 'O2' AS Tipe, 'A' Produk, 0 AS QtyIn, 3 AS QtyOut, 12 AS Cost 
UNION ALL
SELECT 'O3' AS Tipe, 'A' Produk, 0 AS QtyIn, 2 AS QtyOut, 11 AS Cost 
UNION ALL
SELECT 'O4' AS Tipe, 'A' Produk, 0 AS QtyIn, 1 AS QtyOut, 10 AS Cost 
) A 
go
create function dbo.GetExtract(@produk varchar(100) )
returns @x table 
( r int identity
, inTipe varchar(10)
, inCost int
, inQty int
, outTipe varchar(10)
, outCost int
, outQty int
)
as
begin
  declare @next_in varchar(10)
  declare @next_out varchar(10)

  declare @qty_in int
  declare @qty_out int
  declare @qty_delta int

  declare @loops int

  select @next_in  = min(tipe) from tmp_data where tipe like 'I%' and produk = @produk
  select @next_out = min(tipe) from tmp_data where tipe like 'O%' and produk = @produk

  select @qty_in = qtyIn from tmp_data where produk = @produk and tipe = @next_in
  select @qty_out = qtyOut from tmp_data where produk = @produk and tipe = @next_out

  set @loops = 0
  while @loops <= 10 and ( @next_in is not null or @next_out is not null )
  begin
    set @loops = @loops + 1

    if isnull(@qty_out,0) = 0
    begin
      insert into @x ( inTipe, inCost, inQty )
      select @next_in, cost, @qty_in 
        from tmp_Data
       where produk = @produk 
         and tipe = @next_in
       set @qty_in = 0
    end

    if isnull(@qty_in,0) = 0
    begin
      insert into @x ( outTipe, outCost, outQty )
      select @next_out, cost, @qty_out
        from tmp_Data
       where produk = @produk 
         and tipe = @next_out
       set @qty_out = 0
    end

    if @qty_in > 0 and  @qty_out > 0
    begin
      set @qty_delta = case when @qty_out <= @qty_in then @qty_out else @qty_in end

      insert into @x ( inTipe, inCost, inQty, outTipe, outCost, outQty )
      select @next_in, i.cost, @qty_delta
           , @next_out, o.cost, @qty_delta
        from tmp_Data i
           , tmp_Data o
       where i.produk = @produk 
         and i.tipe = @next_in
         and o.produk = @produk 
         and o.tipe = @next_out

       set @qty_out = @qty_out - @qty_delta
       set @qty_in = @qty_in - @qty_delta
    end
    

    if @qty_in = 0
    begin
      select @next_in = min(tipe) 
        from tmp_data
       where tipe like 'I%' 
         and produk = @produk 
         and tipe > @next_in
      select @qty_in = qtyIn 
        from tmp_data 
       where produk = @produk 
         and tipe = @next_in
    end

    if @qty_out = 0
    begin
      select @next_out = min(tipe) 
        from tmp_data
       where tipe like 'O%' 
         and produk = @produk 
         and tipe > @next_out
      select @qty_out = qtyout
        from tmp_data 
       where produk = @produk 
         and tipe = @next_out
    end

  end

  return 
end
go

select * from dbo.GetExtract('A')

go
drop function dbo.GetExtract

go
drop table tmp_data

Open in new window

Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
and in case you want to see the extract for more than 1 product:
select f.*
  from ( select produk from tmp_data group by produk ) x
  cross apply dbo.GetExtract(x.produk) f

Open in new window

Author

Commented:
Hi angelIII,

Here is the result :

1      I1      10      3      O1      12      3
2      I1      10      1      O2      12      1
3      I2      9      2      O2      12      2
4      I2      9      2      O3      11      2
5      I2      9      1      O4      10      1
6      I2      9      3      NULL      NULL      NULL

I think the out cost is not correct ?

Thank you.
Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview

Author

Commented:
It works.

I have a question, what this  while @loops <= 10  mean ?

Thank you.
Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
that is just a "debugging" failsafe to avoid endless loops in this "untested" code.
you shall increase that value (10) to a higher value ...
Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
or even remove that part of the condition.

Author

Commented:
Hi angelIII,

I am sorry, just back again.

I haven't tested again using real data, but I think it should work.

Thank you very much for your help.

Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.