SQL Select Last Receipt Statement

30 Sep 2005 1:49 PM

Joe Williams

I have a transaction table that has PartNumber, TransactionType, Date, and
Quantity as fields. Obviously each PartNumber will have many transaction
records with many different types of transactions (shipments, receipts,
inventory moves, etc. ) The transaction type for receipts is "POR"

For each Part Number, I want to return one record that represents the most
recent receipt Date and Quantity received.

How do I structure the SQL query?

Thanks in advance!

Joe

30 Sep 2005 2:09 PM

JT

What you need is a group by query (either a sub-query or temporary table) of
each PartNumber and it's maximum Date. You then inner join the PartTran
table to this result on Date.

select
PT.PartNumber, PT.Date, PT.Quantity
from
PartTran as PT
join
(select PartNumber, max(Date) as MaxDate group by PartNumber) as XT
on PT.Date = XT.MaxDate

Show quote

"Joe Williams" <J**@anywhere.com> wrote in message
news:%23zfFzVcxFHA.3720@TK2MSFTNGP14.phx.gbl...
>I have a transaction table that has PartNumber, TransactionType, Date, and
>Quantity as fields. Obviously each PartNumber will have many transaction
>records with many different types of transactions (shipments, receipts,
>inventory moves, etc. ) The transaction type for receipts is "POR"
>
> For each Part Number, I want to return one record that represents the most
> recent receipt Date and Quantity received.
>
> How do I structure the SQL query?
>
> Thanks in advance!
>
> Joe
>

5 Oct 2005 4:05 AM

Joe Williams

Hi,

This works but if if a part has transactions on the same day then I get
three records instead of one (for instance, a part is received 3 times in
one day, 10pcs, 10 pcs, and 10pcs.)

What I need is one record returned that represents the total pieces received
the LAST day they were received. So, the SUM of the qty for the MAX date
grouped by Part number.

How can I accomplish this?

Thanks for your help!

Joe
Show quote

"JT" <some***@microsoft.com> wrote in message
news:uvJAxjcxFHA.2516@TK2MSFTNGP12.phx.gbl...
> What you need is a group by query (either a sub-query or temporary table)
> of each PartNumber and it's maximum Date. You then inner join the PartTran
> table to this result on Date.
>
> select
> PT.PartNumber, PT.Date, PT.Quantity
> from
> PartTran as PT
> join
> (select PartNumber, max(Date) as MaxDate group by PartNumber) as XT
> on PT.Date = XT.MaxDate
>
> "Joe Williams" <J**@anywhere.com> wrote in message
> news:%23zfFzVcxFHA.3720@TK2MSFTNGP14.phx.gbl...
>>I have a transaction table that has PartNumber, TransactionType, Date, and
>>Quantity as fields. Obviously each PartNumber will have many transaction
>>records with many different types of transactions (shipments, receipts,
>>inventory moves, etc. ) The transaction type for receipts is "POR"
>>
>> For each Part Number, I want to return one record that represents the
>> most recent receipt Date and Quantity received.
>>
>> How do I structure the SQL query?
>>
>> Thanks in advance!
>>
>> Joe
>>
>
>

7 Oct 2005 8:46 PM

Hugo Kornelis

On Wed, 5 Oct 2005 00:05:10 -0400, Joe Williams wrote:

>Hi,
>
>This works but if if a part has transactions on the same day then I get
>three records instead of one (for instance, a part is received 3 times in
>one day, 10pcs, 10 pcs, and 10pcs.)
>
>What I need is one record returned that represents the total pieces received
>the LAST day they were received. So, the SUM of the qty for the MAX date
>grouped by Part number.
>
>How can I accomplish this?

Hi Joe,

Try this:

select
PT.PartNumber, PT.Date, SUM(PT.Quantity)
from
PartTran as PT
join
(select PartNumber, max(Date) as MaxDate group by PartNumber) as XT
on PT.Date = XT.MaxDate

(untested - read www.aspfaq.com/5006 if you prefer a tested reply)

Best, Hugo

--

(Remove _NO_ and _SPAM_ to get my e-mail address)

5 Oct 2005 4:27 AM

--CELKO--

Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.

Let's get back to the basics of an RDBMS. Rows are not records; fields
are not columns; tables are not files. DATE is a reserved word and
shodl NEVER be used as a column name. Wild guess:

SELECT part_nbr, receipt_date, receipt_qty
    FROM PartPurchases AS P1
WHERE receipt_date
          = (SELECT MAX(receipt_date)
                  FROM PartPurchases AS P2
               WHERE P1.part_nbr = P2.part_nbr);

..