?
Solved

CTE PROBLEM

Posted on 2011-04-25
50
Medium Priority
?
314 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.
0
Comment
Question by:emi_sastra
  • 25
  • 18
  • 4
  • +1
50 Comments
 
LVL 9

Expert Comment

by:sarabhai
ID: 35464775
CTE statement finished with semicolon. (;) not start with semicolon.
0
 
LVL 9

Expert Comment

by:sarabhai
ID: 35464790
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.
0
 
LVL 1

Author Comment

by:emi_sastra
ID: 35464793
Where to put it ?

I have tried several position  and still error.

Thank you.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 41

Expert Comment

by:Sharath
ID: 35464800
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

0
 
LVL 9

Expert Comment

by:sarabhai
ID: 35464812
can you put your batch/store procedure that uses this statement.
0
 
LVL 1

Author Comment

by:emi_sastra
ID: 35464823
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

0
 
LVL 1

Author Comment

by:emi_sastra
ID: 35464825
Hi sarabhai,

I don't get your point.

Thank you.
0
 
LVL 41

Expert Comment

by:Sharath
ID: 35464841
You are getting the maximum recursion error because you have JOIN with CTEB inside CTEB. I need to understand your logic to correct that.
0
 
LVL 1

Author Comment

by:emi_sastra
ID: 35464849
Let's make it simple, could we stop it using number of times recursive it ?

Thank you.
0
 
LVL 9

Expert Comment

by:sarabhai
ID: 35464855
please post your complete SQL statement.
can you show me your complete code.
0
 
LVL 1

Author Comment

by:emi_sastra
ID: 35464873
That is the complete code, I just want to learn CTE with my own data.

Thank you.
0
 
LVL 41

Expert Comment

by:Sharath
ID: 35464876
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

0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 35464883
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

0
 
LVL 1

Author Comment

by:emi_sastra
ID: 35464903
Hi angelIII,

Still problem with max recursion.

Thank you.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 35465061
can you please show us what you except as output?
0
 
LVL 1

Author Comment

by:emi_sastra
ID: 35465089
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

0
 
LVL 1

Author Comment

by:emi_sastra
ID: 35465102
Hi Sharath_123,

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

Thank you.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 35465320
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
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 35465323
btw, we won't need "recursion" here, just plain CTE ...
0
 
LVL 1

Author Comment

by:emi_sastra
ID: 35465337
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.
0
 
LVL 1

Author Comment

by:emi_sastra
ID: 35465371
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.
0
 
LVL 1

Author Comment

by:emi_sastra
ID: 35465395
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

0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 35465485
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

0
 
LVL 1

Author Comment

by:emi_sastra
ID: 35465491
Let me try first.

Thank you.
0
 
LVL 1

Author Comment

by:emi_sastra
ID: 35465566
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

0
 
LVL 1

Author Comment

by:emi_sastra
ID: 35465574
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.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 35465637
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'
0
 
LVL 1

Author Comment

by:emi_sastra
ID: 35465770
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.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 35465820
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

0
 
LVL 1

Author Comment

by:emi_sastra
ID: 35465865
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.

0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 35465878
>The problem is at O3 and O4. They should have cost not null value.

based on what exact logic, please?
0
 
LVL 1

Author Comment

by:emi_sastra
ID: 35465942
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.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 35465960
but how do you define that I1 is linked to O1 and O2, and I2 to O3 and O4 ?
0
 
LVL 1

Author Comment

by:emi_sastra
ID: 35466005
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.



0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 35466023
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.
0
 
LVL 1

Author Comment

by:emi_sastra
ID: 35466059
-- 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.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 35466073
last question: is there no "date/time" information in your system, which would normally "order" the transactions?
0
 
LVL 1

Author Comment

by:emi_sastra
ID: 35466086
--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.
 


0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 35466194
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
0
 
LVL 1

Author Comment

by:emi_sastra
ID: 35466404
Yes, it works fine.

Thank you.
0
 
LVL 1

Author Comment

by:emi_sastra
ID: 35488279
Hi angelIII,

Any solution yet  ?

Thank you.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 35689184
sorry for the delay ...
I don't see a really nice way yet to do this with "pure" sql/t-sql
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 35689304
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

0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 35689310
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

0
 
LVL 1

Author Comment

by:emi_sastra
ID: 35714083
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.
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 35732390
I see, I put a "cost value" in O% records ... but that's not what you need.
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, 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 
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, i.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

0
 
LVL 1

Author Comment

by:emi_sastra
ID: 35737876
It works.

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

Thank you.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 35739486
that is just a "debugging" failsafe to avoid endless loops in this "untested" code.
you shall increase that value (10) to a higher value ...
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 35739498
or even remove that part of the condition.
0
 
LVL 1

Author Comment

by:emi_sastra
ID: 35783999
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.

0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

839 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