CTE to returning WAC price
-
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
--recursivestock_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 -
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 -
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
--recursivestock_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,
LSResult that i was expect:
Actual result:
-
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
--recursivestock_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 aboveunion 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
-
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) -
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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply