Sort of recursive query problem

Question

Post reply

Sort of recursive query problem

Tim Pain

Ten Centuries

Points: 1136
More actions
December 21, 2006 at 6:11 am

#116738

I have the following table (simplified) structures -
POHeader (PO_ID, ReceiptDate)
PODetail (PO_ID, ProductID)
Stock (ProductID, InStock)
Given that I have say 50 units of product1 InStock, of which 10 were received Date 1, 15 on Date 5 and 30 on Date 12.
And assuming the stock is used on a strict FIFO basis, I'm trying to build a query that will give me and AgedStock report.
So in this instance I want to see -
Units Date
30     12
15      5
5        1
I'm stuck to know how to proceed.
Any ideas or pointer please?

Many thanks

Tim

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

ijaz SSCommitted Points: 1912 More actions · Answer 1

Your table stucture does not show any unit information of product. Can you explain how the units stored in these table?

cheers

DaveItz SSCrazy Points: 2146 More actions · Answer 2

Heh. Beat me to it by a half second. Shouldn't have pressed preview.

^_^

"You said they are simplified table structures.

Is there a Quantity field in the PODetail table?"

Tim Pain Ten Centuries Points: 1136 More actions · Answer 3

Sorry I have mixed up field descriptions (case of trying to make the issue simple), so for the purposes of this take InStock = Units.

Sorry about the confusion.

Tim

DaveItz SSCrazy Points: 2146 More actions · Answer 4

I gathered that.

That's the units in stock, at the present time. right?

To trace the quantity back to the the ReceiptDate field in POHeader (could be the same product was received over several POs) we'll need some sort of quantity field in the PODetail table.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

Is it just me or do I see a 4 minutes delay in there ???

Tim Pain Ten Centuries Points: 1136 More actions · Answer 6

That's one of the troubles when you try to simplifiy things, you leave important bits out

So yes units in stock at the present time in the Stock table.

And yes the same product could be purchase and recived many times. In the PODetails table there is a field with the quantity received (can be different from what was ordered - but that's irrelevant for this problem).

Tim

DaveItz SSCrazy Points: 2146 More actions · Answer 7

You can do this:

select
   dtl.ProductID,
   Units=Sum(dtl.qty),
   [Date]=dateadd(day, 0, datediff(day, 0, hdr.ReceiptDate)),  
   UnitsInStock=isnull(s.InStock,0)
from PODetail dtl
inner join POHeader hdr on hdr.PO_ID = dtl.PO_ID
left join Stock s on s.ProductID = dtl.ProductID
group by dtl.ProductID, dateadd(day, 0, datediff(day, 0, hdr.ReceiptDate)), s.InStock

I'm not exactly sure.

This may be what you wanted:

declare @now datetime
select @now=getdate()
select
   dtl.ProductID,
   Units=Sum(dtl.qty),
   DaysAgo=datediff(day, 0, @now - datediff(day,0,hdr.ReceiptDate)),
   UnitsInStock=isnull(s.InStock,0)
from PODetail dtl
inner join POHeader hdr on hdr.PO_ID = dtl.PO_ID
left join Stock s on s.ProductID = dtl.ProductID
group by dtl.ProductID, datediff(day, 0, @now - datediff(day,0,hdr.ReceiptDate)) desc, s.InStock

mynth Valued Member Points: 65 More actions · Answer 8

I think we need more information to answer your question properly but try this.

select ReceiptDate,

(select B.instock

from B.stock B

where B.ProductID in (select distinct C.ProductID

from PODetail C

where C.PO_ID = A.PO_ID))

from POHeader A