My current code is duplicating results per item_id. I am summing the previous three months usage per item in two locations. I do have more locations but I limited it in the WHERE clause. I think the query is showing the extra results due to the other locations. Can someone help me clean up my code?
The usage column is YYYYMM format
select distinct
u.location, u.item_id, l.qty_on_hand, u.inv_min, u.usage
from
inv_usage as u
left join
inventory_location as l on u.inv_id = u.inv_id and l.location = u.location
where
u.year_period >= cast(convert(char(6), dateadd(month, -3, cast(convert(char(6), getdate(), 112) + '01' as date)), 112) as int)
and l.location in ('1', '2')
and u.location in ('1', '2')
Table for Inventory Quantity on Hand:
l.location l.inv_id l.qty_on_hand l.inv_min -------------------------------------------------- 1 1 100 100 2 1 10 10 1 2 200 200 2 2 20 20 1 3 300 300 2 3 30 30
Table for Inventory Usage:
u.location u.inv_id u.item_id u.inv_min u.year_period u.usage ----------------------------------------------------------------------- 1 1 item1 100 202002 100 2 1 item1 10 202002 10 1 1 item1 100 202002 100 2 1 item1 10 202002 10 1 1 item1 100 202002 100 2 1 item1 10 202002 10 1 2 item2 200 202003 200 2 2 item2 20 202003 20 1 2 item2 200 202003 200 2 2 item2 20 202003 20 1 2 item2 200 202003 200 2 2 item2 20 202003 20 1 3 item3 300 202004 300 2 3 item3 30 202004 30 1 3 item3 300 202004 300 2 3 item3 30 202004 30 1 3 item3 300 202004 300 2 3 item3 30 202004 30
My desired result sums the usage for the previous three months per location and show the current quantity in inventory.
?.location u.item_id l.qty_on_hand ?.inv_min u.usage -------------------------------------------------------------- 1 1 100 100 300 2 1 10 10 30 1 2 200 200 600 2 2 20 20 60 1 3 300 300 900 2 3 30 30 90
Advertisement
Answer
One option uses a correlated subquery:
select
il.*,
(
select sum(iu.usage)
from inventory_usage iu
where
iu.location = il.location
and iu.inv_id = il.inv_id
and iu.year_period >=
year(dateadd(month, -3, datefromparts(year(getdate()), month(getdate()), 1))) * 100
+ month(dateadd(month, -3, datefromparts(year(getdate()), month(getdate()), 1)))
and iu.year_period <
year(datefromparts(year(getdate()), month(getdate()), 1)) * 100
+ month(datefromparts(year(getdate()), month(getdate()), 1))
) usage
from inventory_location il
The conditions on the date may look lengthy, however they look to me like the most efficient way to filter; they make the query SARGeable, and would take advantage of an index on inventory_usage(location, inv_id, year_period).