CTE to returning WAC price

• luissantos

  SSCertifiable

  Points: 7391

  More actions

  June 3, 2018 at 4:23 pm

  #413563

  Hello community,

  I have build  a CTE to calculate my WAC price like that:
  
CREATE TABLE stock_table
  (document_type VARCHAR(2), document_date datetime, product_id varchar(18), qty_out NUMERIC(14,3), qty_in NUMERIC(14,3), price NUMERIC(14,3), row_num int,
   stock_balance NUMERIC(14,3))
;
/*COMMENT ON TABLE stock_table
  IS '
  type_document:
  SI: Initial quantity in stock
  LC: Customer delivery
  LF: Supplier delivery
*/  

  -- Now , Inserting some records on them:
INSERT INTO Stock_table (document_type, document_date, product_id, qty_out, qty_in, price, row_num, stock_balance)
VALUES
              ('LF', '2017-03-16', 'MSD 04-050101', 0, 4, 7.895, 1, 4),
              ('LF', '2017-03-16', 'MSD 04-050101', 0, 4, 28.688, 2, 8),
              ('LC', '2017-03-16', 'MSD 04-050101', 4, 0, 28.688, 3, 4),
              ('LC', '2017-03-16', 'MSD 04-050101', 4, 0, 28.688, 4, 0),
              ('LF', '2017-05-04', 'MSD 04-050101', 0, 4, 7.91, 5, 4),
              ('LF', '2017-05-04', 'MSD 04-050101', 0, 1, 33.75, 6, 5),
              ('LC', '2017-05-04', 'MSD 04-050101', 1, 0, 33.75, 7, 4),
              ('LF', '2017-05-15', 'MSD 04-050101', 0, 1, 8.23, 8, 5),
              ('LC', '2017-05-15', 'MSD 04-050101', 1, 0, 8.23, 9, 4),
              ('LF', '2017-07-05', 'MSD 04-050101', 0, 1, 32.06, 10, 5),
              ('LC', '2017-07-05', 'MSD 04-050101', 1, 0, 32.06, 11, 4),
              ('LC', '2017-07-05', 'MSD 04-050101', 1, 0, 32.06, 12, 3),
              ('LF', '2017-07-06', 'MSD 04-050101', 0, 2, 33.75, 13, 5),
              ('LC', '2017-07-06', 'MSD 04-050101', 2, 0, 33.75, 14, 3),
              ('LC', '2017-07-07', 'MSD 04-050101', 2, 0, 33.75, 15, 1),
              ('LF', '2017-08-21', 'MSD 04-050101', 0, 1, 8.26, 16, 2),
              ('LC', '2017-08-21', 'MSD 04-050101', 1, 0, 8.26, 17, 1),
              ('LF', '2017-08-25', 'MSD 04-050101', 0, 1, 33.75, 18, 2),
              ('LC', '2017-08-25', 'MSD 04-050101', 1, 0, 33.75, 19, 1),
              ('LC', '2017-08-25', 'MSD 04-050101', 1, 0, 33.75, 20, 0),
              ('LF', '2017-09-25', 'MSD 04-050101', 0, 10, 7.883, 21, 10)
              ;

  /* Then, i will go to create my CTE
;WITH
--recursive

  stock_temp as (
  select
    *,
    row_number() over(partition by product_id order by row_num) as rn
  from
    stock_table
)

  ,  cte as (
  select
    document_type, document_date,
    product_id, qty_out, qty_in, price,
    row_num,
     stock_balance, rn,
    CAST(price AS NUMERIC(14,3)) as wac
  from
    stock_temp
where document_type = 'SI' (A)

    union all

    select
    sub.document_type, sub.document_date,
    sub.product_id, sub.qty_out,  sub.qty_in, sub.price,
    sub.row_num,
    sub.stock_balance,  sub.rn,
    CAST((case when sub.qty_in = 0 then main.wac else
    ((sub.stock_balance - sub.qty_in) * main.wac + sub.qty_in * sub.price)
      / ((sub.stock_balance - sub.qty_in)  + sub.qty_in) END) AS NUMERIC (14,3))AS wac
 from
  cte as main
  join stock_temp as sub
    on (main.product_id = sub.product_id and main.rn + 1 = sub.rn )
)

  select * from cte  ORDER BY rn asc

  Problems:
  1.       If i dont have document_Type = ‘SI’ , then no values are return.
  2.       If i comment the Where condition on (A)
  My query return a curious result:
  Rn = 1  return  1 line
  Rn = 2   return  2 lines
  Rn = 3  return 3 lines
  Rn = 4  return 4 lines
  And so on.

  Someone could give help to explain why this occur.

  Many thanks,
  Best regards,
  LS

• Thom A

  SSC Guru

  Points: 99126

  More actions

  June 4, 2018 at 4:05 am

  #1992783

  That query you've posted doesn't run (due to the random (A) after document_type = 'SI'. What is the (A) for? If you remove that, it does run.

  As for why no rows return, why would you expect any to? You've filtered your data to only include rows where document_type = 'SI'; if you have no rows fulfilling that requirement of course nothing is going to return. If I asked you to take all the green apples out of a bowl that contains only red apples, you would end up with nothing. You won't produce a Green Apple out of nowhere; the same is true here a row won't be reduced when there is no data.

  What result set are you expecting here?

  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk

• luissantos

  SSCertifiable

  Points: 7391

  More actions

  June 4, 2018 at 5:21 am

  #1992787

  Hello Tom,

  Sorry , my  Coding comments are incorrect, this is the correct code:
  
CREATE TABLE stock_table
 (document_type VARCHAR(2), document_date datetime, product_id varchar(18), qty_out NUMERIC(14,3), qty_in NUMERIC(14,3), price NUMERIC(14,3), row_num int,
  stock_balance NUMERIC(14,3))
;
/*COMMENT ON TABLE stock_table
 IS '
 type_document:
 SI: Initial quantity in stock
 LC: Customer delivery
 LF: Supplier delivery
*/

  -- Now , Inserting some records on them:
INSERT INTO Stock_table (document_type, document_date, product_id, qty_out, qty_in, price, row_num, stock_balance)
VALUES
     ('LF', '2017-03-16', 'MSD 04-050101', 0, 4, 7.895, 1, 4),
     ('LF', '2017-03-16', 'MSD 04-050101', 0, 4, 28.688, 2, 8),
     ('LC', '2017-03-16', 'MSD 04-050101', 4, 0, 28.688, 3, 4),
     ('LC', '2017-03-16', 'MSD 04-050101', 4, 0, 28.688, 4, 0),
     ('LF', '2017-05-04', 'MSD 04-050101', 0, 4, 7.91, 5, 4),
     ('LF', '2017-05-04', 'MSD 04-050101', 0, 1, 33.75, 6, 5),
     ('LC', '2017-05-04', 'MSD 04-050101', 1, 0, 33.75, 7, 4),
     ('LF', '2017-05-15', 'MSD 04-050101', 0, 1, 8.23, 8, 5),
     ('LC', '2017-05-15', 'MSD 04-050101', 1, 0, 8.23, 9, 4),
     ('LF', '2017-07-05', 'MSD 04-050101', 0, 1, 32.06, 10, 5),
     ('LC', '2017-07-05', 'MSD 04-050101', 1, 0, 32.06, 11, 4),
     ('LC', '2017-07-05', 'MSD 04-050101', 1, 0, 32.06, 12, 3),
     ('LF', '2017-07-06', 'MSD 04-050101', 0, 2, 33.75, 13, 5),
     ('LC', '2017-07-06', 'MSD 04-050101', 2, 0, 33.75, 14, 3),
     ('LC', '2017-07-07', 'MSD 04-050101', 2, 0, 33.75, 15, 1),
     ('LF', '2017-08-21', 'MSD 04-050101', 0, 1, 8.26, 16, 2),
     ('LC', '2017-08-21', 'MSD 04-050101', 1, 0, 8.26, 17, 1),
     ('LF', '2017-08-25', 'MSD 04-050101', 0, 1, 33.75, 18, 2),
     ('LC', '2017-08-25', 'MSD 04-050101', 1, 0, 33.75, 19, 1),
     ('LC', '2017-08-25', 'MSD 04-050101', 1, 0, 33.75, 20, 0),
     ('LF', '2017-09-25', 'MSD 04-050101', 0, 10, 7.883, 21, 10)
     ;

  /* Then, i will go to create my CTE */
;WITH
--recursive

  stock_temp as (
 select
  *,
  row_number() over(partition by product_id order by row_num) as rn
 from
  stock_table
)

  , cte as (
 select
  document_type, document_date,
  product_id, qty_out, qty_in, price,
  row_num,
  stock_balance, rn,
  CAST(price AS NUMERIC(14,3)) as wac
 from
  stock_temp
where document_type = 'SI'  --(A)

   union all

   select
  sub.document_type, sub.document_date,
  sub.product_id, sub.qty_out, sub.qty_in, sub.price,
  sub.row_num,
  sub.stock_balance, sub.rn,
  CAST((case when sub.qty_in = 0 then main.wac else
  ((sub.stock_balance - sub.qty_in) * main.wac + sub.qty_in * sub.price)
   / ((sub.stock_balance - sub.qty_in) + sub.qty_in) END) AS NUMERIC (14,3))AS wac
from
 cte as main
 join stock_temp as sub
  on (main.product_id = sub.product_id and main.rn + 1 = sub.rn )
)

  select * from cte ORDER BY rn asc


  The correct resut i expect, is even that i´am not  SI: Initial quantity in stock , that is perfectly normal because my initial stock could be a purchase invoice.
  also, i will make a UNION ALL then in the first query with WHERE Document_Type = 'SI' is only for this type on movments, the second parte
  must include all movments except SI, because my join condition is :
   cte as main
   join stock_temp as sub
    on (main.product_id = sub.product_id and main.rn + 1 = sub.rn )
  not by Document_Type.
  I will go to insert  an image about  what i need.

  Best regards,
  LS

  Result that i was expect:

  

  Actual result:
  

• Bevan Keighley

  SSCommitted

  Points: 1825

  More actions

  June 4, 2018 at 4:11 pm

  #1992911

  Hi,

  I'm not sure if I have worked out your requirements correctly but hopefully this query will get you a step closer....  I have assumed that your first row is not necessarily where document_type = SI (as there might not be an SI row).  If there isn't an SI row the query will just take row_num = 1.

  
;WITH
--recursive

  stock_temp as
(
    select *, --row_number() over(partition by product_id order by row_num) as rn

          --- first row should be either: earliest row_num that is 'SI'; or earliest row_num when there is no SI.
        row_number() over(partition by product_id order by case when document_type = 'SI' then 1 else 2 end , row_num ) as rn
    from stock_table
)

  , cte as
(
    select
        document_type, document_date,
        product_id, qty_out, qty_in, price,
        row_num,
        stock_balance, rn,
        CAST(price AS NUMERIC(14,3)) as wac
    from stock_temp
    where rn = 1
    --where document_type = 'SI' --(A) -- note logic fixed above

      union all

      select
        sub.document_type, sub.document_date,
        sub.product_id, sub.qty_out, sub.qty_in, sub.price,
        sub.row_num,
        sub.stock_balance, sub.rn,
        CAST((case when sub.qty_in = 0 then main.wac else
            ((sub.stock_balance - sub.qty_in) * main.wac + sub.qty_in * sub.price)
                / ((sub.stock_balance - sub.qty_in) + sub.qty_in) END) AS NUMERIC (14,3))AS wac
    from cte as main
        join stock_temp as sub
            on (main.product_id = sub.product_id and main.rn + 1 = sub.rn )
)

  select * from cte ORDER BY rn asc

• sgmunson

  SSC Guru

  Points: 110639

  More actions

  June 5, 2018 at 6:37 am

  #1992946

  No CTE is needed for this.   What you appear to be looking for is an average cost per item that comes in the door from a supplier.  There are WINDOW function enhancements to the SUM aggregate function that can help you here.   Try the following code as a test:
  CREATE TABLE #stock_table (
    document_type varchar(2),
    document_date datetime,
    product_id varchar(18),
    qty_out numeric(14,3),
    qty_in numeric(14,3),
    price numeric(14,3),
    row_num int,
    stock_balance numeric(14,3)
);
/*COMMENT ON TABLE #stock_table
IS '
type_document:
SI: Initial quantity in stock
LC: Customer delivery
LF: Supplier delivery
*/

  -- Now , Inserting some records on them:
INSERT INTO #stock_table (document_type, document_date, product_id, qty_out, qty_in, price, row_num, stock_balance)
    VALUES    ('LF', '2017-03-16', 'MSD 04-050101', 0, 4, 7.895, 1, 4),
            ('LF', '2017-03-16', 'MSD 04-050101', 0, 4, 28.688, 2, 8),
            ('LC', '2017-03-16', 'MSD 04-050101', 4, 0, 28.688, 3, 4),
            ('LC', '2017-03-16', 'MSD 04-050101', 4, 0, 28.688, 4, 0),
            ('LF', '2017-05-04', 'MSD 04-050101', 0, 4, 7.91 , 5, 4),
            ('LF', '2017-05-04', 'MSD 04-050101', 0, 1, 33.75 , 6, 5),
            ('LC', '2017-05-04', 'MSD 04-050101', 1, 0, 33.75 , 7, 4),
            ('LF', '2017-05-15', 'MSD 04-050101', 0, 1, 8.23 , 8, 5),
            ('LC', '2017-05-15', 'MSD 04-050101', 1, 0, 8.23 , 9, 4),
            ('LF', '2017-07-05', 'MSD 04-050101', 0, 1, 32.06 , 10, 5),
            ('LC', '2017-07-05', 'MSD 04-050101', 1, 0, 32.06 , 11, 4),
            ('LC', '2017-07-05', 'MSD 04-050101', 1, 0, 32.06 , 12, 3),
            ('LF', '2017-07-06', 'MSD 04-050101', 0, 2, 33.75 , 13, 5),
            ('LC', '2017-07-06', 'MSD 04-050101', 2, 0, 33.75 , 14, 3),
            ('LC', '2017-07-07', 'MSD 04-050101', 2, 0, 33.75 , 15, 1),
            ('LF', '2017-08-21', 'MSD 04-050101', 0, 1, 8.26 , 16, 2),
            ('LC', '2017-08-21', 'MSD 04-050101', 1, 0, 8.26 , 17, 1),
            ('LF', '2017-08-25', 'MSD 04-050101', 0, 1, 33.75 , 18, 2),
            ('LC', '2017-08-25', 'MSD 04-050101', 1, 0, 33.75 , 19, 1),
            ('LC', '2017-08-25', 'MSD 04-050101', 1, 0, 33.75 , 20, 0),
            ('LF', '2017-09-25', 'MSD 04-050101', 0, 10, 7.883, 21, 10);

  SELECT ST.*,
    ROUND(SUM(CASE WHEN ST.qty_in > 0 THEN ST.qty_in * ST.price ELSE 0 END) OVER(PARTITION BY ST.product_id ORDER BY ST.row_num) /
        SUM(CASE WHEN ST.qty_in > 0 THEN ST.qty_in ELSE 0 END) OVER(PARTITION BY ST.product_id ORDER BY ST.row_num), 2) AS WAC
FROM #stock_table AS ST
ORDER BY ST.row_num;

  DROP TABLE #stock_table;

  Steve (aka sgmunson) 🙂 🙂 🙂
  Rent Servers for Income (picks and shovels strategy)

• luissantos

  SSCertifiable

  Points: 7391

  More actions

  June 5, 2018 at 4:30 pm

  #1993041

  Hello,
  Thanks for all your  replies.
  tI have discovered why the CTE repeat the rn , is that because i haven´t document_Type = 'SI'.

  Best regards,
  Luis Santos