STUCK – Need Help With Final Touches Inventory Weight Calculations

[LDR]

Hello All,

I am stuck at this point – and still need some help

I have a working query that shows me stock levels every 15days but I can not seem to get it to show me the consignee or the weight on hand, it does show me the other information I need though.

You can download a sample of the data I am working with from here:

http://www.mojowares.com/MyData.Zip

Or look below –

Thanks!!



tblReceived (sample)
-----------------------------------------------
CONSIGNEE BOL MARK ReceivedQTY ReceivedWeight ReceivedDate
ALJ001 PGA25B INT1246ONE 210 281235 8/12/2004
ALJ001 PGA25B INT1246THREE 210 281235 8/12/2004
ALJ001 PGA25B INT1246TWO 90 120530 8/12/2004

tblShipped (sample)
------------------------------------------------
BOL MARK ShippedQTY ShippedDate
PGA25B INT1246ONE 14 8/19/2004
PGA25B INT1246THREE 15 8/23/2004
PGA25B INT1246TWO 15 8/25/2004
PGA25B INT1246TWO 15 8/25/2004
PGA25B INT1246TWO 15 8/25/2004
PGA25B INT1246THREE 15 8/25/2004
PGA25B INT1246TWO 15 8/26/2004
PGA25B INT1246THREE 15 8/27/2004
PGA25B INT1246THREE 15 8/27/2004
PGA25B INT1246ONE 10 8/27/2004
PGA25B INT1246THREE 2 8/27/2004
PGA25B INT1246TWO 2 8/27/2004
PGA25B INT1246ONE 14 8/30/2004
PGA25B INT1246ONE 10 8/30/2004
PGA25B INT1246THREE 2 8/30/2004
PGA25B INT1246TWO 2 8/30/2004
PGA25B INT1246ONE 14 9/1/2004
PGA25B INT1246ONE 14 9/2/2004
PGA25B INT1246THREE 30 9/2/2004
PGA25B INT1246THREE 15 9/3/2004
PGA25B INT1246TWO 13 9/7/2004
PGA25B INT1246TWO 13 9/9/2004
PGA25B INT1246THREE 15 9/9/2004
PGA25B INT1246ONE 14 9/15/2004
PGA25B INT1246THREE 13 9/21/2004
PGA25B INT1246ONE 14 9/21/2004
PGA25B INT1246THREE 15 9/21/2004
PGA25B INT1246THREE 15 9/22/2004
PGA25B INT1246THREE 15 9/22/2004
PGA25B INT1246ONE 14 9/24/2004
PGA25B INT1246THREE 11 9/27/2004
PGA25B INT1246ONE 14 9/27/2004
PGA25B INT1246ONE 14 9/27/2004
PGA25B INT1246ONE 14 9/29/2004
PGA25B INT1246ONE 14 9/30/2004
PGA25B INT1246ONE 14 10/1/2004
PGA25B INT1246ONE 14 10/7/2004
PGA25B INT1246THREE 14 11/1/2004
PGA25B INT1246ONE 8 11/29/2004
PGA25B INT1246THREE 2 11/29/2004
PGA25B INT1246THREE 1 12/10/2004


Query:

-- ------------------------------------------
-- append both input tables into a single table
-- change quantity to negative for shipped items
-- ------------------------------------------

declare @Inventory table (BOL nvarchar(255), MARK nvarchar(255), Qty int, [Date] datetime)

insert @Inventory (BOL, MARK, Qty, [Date])
select BOL, MARK, ReceivedQTY, ReceivedDate from tblReceived

insert @Inventory (BOL, MARK, Qty, [Date])
select BOL, MARK, -1 * ShippedQTY, ShippedDate from tblShipped

-- ------------------------------------------
-- initialize values
-- ------------------------------------------

declare @InitQty int, @InitDate datetime

select @InitQty = 0 -- assumes initial quantity is zero
select @InitDate = min([Date]) from @Inventory

-- ------------------------------------------
-- creates table with numbers counting by 15
-- alternatively you could have a fixed table
-- in your database with same
-- ------------------------------------------
declare @Digits table (Num int)

insert @Digits
select 0
union all
select 1
union all
select 2
union all
select 3
union all
select 4
union all
select 5
union all
select 6
union all
select 7
union all
select 8
union all
select 9

declare @Days table (ReportDate datetime)

insert @Days
select dateadd(dd, (15 * (100*d3.Num + 10*d2.Num + 1*d1.Num)), @InitDate)
from @Digits d1
cross join @Digits d2
cross join @Digits d3
order by 1

-- select * from @Days
-- ------------------------------------------

select r.ReportDate, i.BOL, i.MARK, @InitQty + sum(i.Qty)as Inventory
from @Days r
left outer join @Inventory i
on r.ReportDate >= i.[Date]
where r.ReportDate <= getdate() 
group by r.ReportDate, i.BOL, i.MARK

Query Results:

Report Date BOL MARK QTY
8/12/2004 PGA25B INT1246ONE 210
8/12/2004 PGA25B INT1246THREE 210
8/12/2004 PGA25B INT1246TWO 90
8/27/2004 PGA25B INT1246ONE 196
8/27/2004 PGA25B INT1246THREE 165
8/27/2004 PGA25B INT1246TWO 30
9/11/2004 PGA25B INT1246ONE 134
9/11/2004 PGA25B INT1246THREE 86
9/11/2004 PGA25B INT1246TWO 0
9/26/2004 PGA25B INT1246ONE 92
9/26/2004 PGA25B INT1246THREE 28
9/26/2004 PGA25B INT1246TWO 0
10/11/2004 PGA25B INT1246ONE 8
10/11/2004 PGA25B INT1246THREE 17
10/11/2004 PGA25B INT1246TWO 0
10/26/2004 PGA25B INT1246ONE 8
10/26/2004 PGA25B INT1246THREE 17
10/26/2004 PGA25B INT1246TWO 0
11/10/2004 PGA25B INT1246ONE 8
11/10/2004 PGA25B INT1246THREE 3
11/10/2004 PGA25B INT1246TWO 0
11/25/2004 PGA25B INT1246ONE 8
11/25/2004 PGA25B INT1246THREE 3
11/25/2004 PGA25B INT1246TWO 0
12/10/2004 PGA25B INT1246ONE 0
12/10/2004 PGA25B INT1246THREE 1
12/10/2004 PGA25B INT1246TWO 0
12/25/2004 PGA25B INT1246ONE 0
12/25/2004 PGA25B INT1246THREE 0
12/25/2004 PGA25B INT1246TWO 0
1/9/2005 PGA25B INT1246ONE 0
1/9/2005 PGA25B INT1246THREE 0
1/9/2005 PGA25B INT1246TWO 0
1/24/2005 PGA25B INT1246ONE 0
1/24/2005 PGA25B INT1246THREE 0
1/24/2005 PGA25B INT1246TWO 0
2/8/2005 PGA25B INT1246ONE 0
2/8/2005 PGA25B INT1246THREE 0
2/8/2005 PGA25B INT1246TWO 0
2/23/2005 PGA25B INT1246ONE 0
2/23/2005 PGA25B INT1246THREE 0
2/23/2005 PGA25B INT1246TWO 0
3/10/2005 PGA25B INT1246ONE 0
3/10/2005 PGA25B INT1246THREE 0
3/10/2005 PGA25B INT1246TWO 0
3/25/2005 PGA25B INT1246ONE 0
3/25/2005 PGA25B INT1246THREE 0
3/25/2005 PGA25B INT1246TWO 0
4/9/2005 PGA25B INT1246ONE 0
4/9/2005 PGA25B INT1246THREE 0
4/9/2005 PGA25B INT1246TWO 0
4/24/2005 PGA25B INT1246ONE 0
4/24/2005 PGA25B INT1246THREE 0
4/24/2005 PGA25B INT1246TWO 0
5/9/2005 PGA25B INT1246ONE 0
5/9/2005 PGA25B INT1246THREE 0
5/9/2005 PGA25B INT1246TWO 0
5/24/2005 PGA25B INT1246ONE 0
5/24/2005 PGA25B INT1246THREE 0
5/24/2005 PGA25B INT1246TWO 0
6/8/2005 PGA25B INT1246ONE 0
6/8/2005 PGA25B INT1246THREE 0
6/8/2005 PGA25B INT1246TWO 0
6/23/2005 PGA25B INT1246ONE 0
6/23/2005 PGA25B INT1246THREE 0
6/23/2005 PGA25B INT1246TWO 0
7/8/2005 PGA25B INT1246ONE 0
7/8/2005 PGA25B INT1246THREE 0
7/8/2005 PGA25B INT1246TWO 0
7/23/2005 PGA25B INT1246ONE 0
7/23/2005 PGA25B INT1246THREE 0
7/23/2005 PGA25B INT1246TWO 0
8/7/2005 PGA25B INT1246ONE 0
8/7/2005 PGA25B INT1246THREE 0
8/7/2005 PGA25B INT1246TWO 0
8/22/2005 PGA25B INT1246ONE 0
8/22/2005 PGA25B INT1246THREE 0
8/22/2005 PGA25B INT1246TWO 0

What I am looking for:

Report Date Consignee BOL MARK QTY WEIGHT
XX XX XX XX XX XX