CTE PROBLEM

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.
LVL 1
emi_sastraAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
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
 
sarabhaiCommented:
CTE statement finished with semicolon. (;) not start with semicolon.
0
 
sarabhaiCommented:
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
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
emi_sastraAuthor Commented:
Where to put it ?

I have tried several position  and still error.

Thank you.
0
 
SharathData EngineerCommented:
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
 
sarabhaiCommented:
can you put your batch/store procedure that uses this statement.
0
 
emi_sastraAuthor 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

0
 
emi_sastraAuthor Commented:
Hi sarabhai,

I don't get your point.

Thank you.
0
 
SharathData EngineerCommented:
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
 
emi_sastraAuthor Commented:
Let's make it simple, could we stop it using number of times recursive it ?

Thank you.
0
 
sarabhaiCommented:
please post your complete SQL statement.
can you show me your complete code.
0
 
emi_sastraAuthor Commented:
That is the complete code, I just want to learn CTE with my own data.

Thank you.
0
 
SharathData EngineerCommented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
emi_sastraAuthor Commented:
Hi angelIII,

Still problem with max recursion.

Thank you.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
can you please show us what you except as output?
0
 
emi_sastraAuthor 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

0
 
emi_sastraAuthor Commented:
Hi Sharath_123,

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

Thank you.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
btw, we won't need "recursion" here, just plain CTE ...
0
 
emi_sastraAuthor 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.
0
 
emi_sastraAuthor 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.
0
 
emi_sastraAuthor 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

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
emi_sastraAuthor Commented:
Let me try first.

Thank you.
0
 
emi_sastraAuthor 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

0
 
emi_sastraAuthor 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.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
emi_sastraAuthor 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.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
emi_sastraAuthor 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.

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

based on what exact logic, please?
0
 
emi_sastraAuthor 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.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
but how do you define that I1 is linked to O1 and O2, and I2 to O3 and O4 ?
0
 
emi_sastraAuthor 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.



0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
emi_sastraAuthor 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.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
last question: is there no "date/time" information in your system, which would normally "order" the transactions?
0
 
emi_sastraAuthor 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.
 


0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
emi_sastraAuthor Commented:
Yes, it works fine.

Thank you.
0
 
emi_sastraAuthor Commented:
Hi angelIII,

Any solution yet  ?

Thank you.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
sorry for the delay ...
I don't see a really nice way yet to do this with "pure" sql/t-sql
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
emi_sastraAuthor 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.
0
 
emi_sastraAuthor Commented:
It works.

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

Thank you.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
or even remove that part of the condition.
0
 
emi_sastraAuthor 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.

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.