SQL Server query duplicating rows based on inventory locations that have been claused out

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

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).