Inventroy Reporting - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Inventroy Reporting

Author

Topic

MojoWares
Starting Member

1 Post

Posted - 2005-08-29 : 20:14:24

I am working producing some reports based on two files we get from warehouses we deal with. I have gotten some help already and it was much appreciated but I am still stuck on a few things.

In my test database (SQL 2000) I have two tables, one that shows items received and one that shows items shipped. I want to look at the quantity and weight based on 15 day intervals from the date it arrived in the warehouse. And I need to multiply the weight by .5

I am new at this still and I am lost on getting two different views of the data

1st view is more detailed:

ReportDate, Consignee, BOL, MARK, QTY, Weight, Weight*.5

2nd is more summarized

ReportDate, Consignee, QTY, Weight

I am then thinking I will export the results out and use a pivit table in excel or may do something in crystal.

Here is sample date and below in the query…

http://www.mojowares.com/testdata2.xls

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

declare @Inventory table (CONSIGNEE nvarchar(255), BOL nvarchar(255), MARK nvarchar(255), Qty float (8), ReceivedWeight float (8), [Date] datetime)

insert @Inventory (CONSIGNEE, BOL, MARK, QTY, ReceivedWeight, [Date])
select CONSIGNEE, BOL, MARK, ReceivedQTY, ReceivedWeight,  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]
group by r.ReportDate, i.BOL, i.MARK

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources