Order / Inventory Demand - 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)

Order / Inventory Demand

Author

Topic

ninken
Starting Member

2 Posts

Posted - 2004-04-08 : 10:17:45

I heard great place to post crazy SQL statements.

What I did so far is Created View that inner join
my Master Orders with Detail Order to give me the result
OrderNum - DateToShip - ItemNum - Amount
Also Created a view in Inventory to Sum it's itemnum by Amount

Now here comes the tought part (For me at least) I created
a Store Procedure To let my application pick the date range

Create Procedure DemandDates
@StartDate DateTime, @EndDate DateTime as
If @StartDate is null or @EndDate is null
begin RAISERROR('Missing Start or End Dates', 14,1)
Return
end
select  dpart1.DateShip, 
        do.Ordernum, 
        do.ItemNum, 
        do.Qty as NeedBoxes
From dpart1 inner join detailorder as do
on dpart1.ordernum = do.ordernum
where dateship > @StartDate and dateship < @EndDate

Now I want it to Sum the Amount by ItemNum, and Drop the Dates,
and OrderNum Info Then Inner join The ItemNum To the Inventory
View. Any Ideals or am I Nuts? Thats always questionable!

Thanks
Ken

ninken
Starting Member

2 Posts

Posted - 2004-04-08 : 11:48:04

Figured it out, I was going about it all wrong, Pretty Tricky you don't want to see this Execution Plan!

Create View dpart1 as
select Ordernum, DateShip from MasterOrder
where (sship = 0)

Create View InvAmount as
select Itemnum, Sum(Amount) As InvTotal from MasterInventory
Group by ItemNum


Create Procedure DemandDates
@StartDate DateTime, @EndDate DateTime as
If @StartDate is null or @EndDate is null
begin RAISERROR('Missing Start or End Dates', 14,1)
Return
end
Select do.ItemNum, (mp.Part+ Mp.Color) as ItemInfo, mp.[Print] as Printed, Sum(do.Qty) as NeedBoxes, ai.InvTotal
From dpart1 inner join detailorder as do
on dpart1.ordernum = do.ordernum
inner join Invamount as ai
on do.ItemNum = ai.ItemNum
inner join MasterParts as mp
on do.ItemNum = mp.ItemNum
where dpart1.dateship > @StartDate and dpart1.dateship < @EndDate
Group by do.ItemNum,  mp.Part, mp.Color, mp.[Print], ai.InvTotal

DemandDates @StartDate = '04-16-2004', @EndDate = '05-16-2004'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources