SQL query to pull max orderdate and corresponding quantity 1

[dianemarie]

Hello,

This is for a stored procedure I am using for a report. I have a table that has several transactions per order number, and each transaction has an associated quantity and cost (dollars). As an order is revised, so are the dollars and quantities associated with that order. I need all the dollar transactions, but only the most recent quantity. I have created a table variable to pull one record per order with the most recent document date and it's associated quantity. I get the dollar transactions from my main table. I link my main table to the var table, which returns all the dollar transactions per order number with a field from my var table for qty, which just repeats itself. I will deal with that in the report. Because I am grouping by order number, I have to have the quantity be some sort of aggregate. But neither Min nor Max will work, as it pulls the qty information independently of the document date. I don't know if I have to completely rethink this. The way I am declaring the table is below and an example of how records are returned by the stored procedure also. Again, the problem is that the Qty field is pulling the Min or Max independently of the document date and I'm not sure how else to aggregate that field. Any guidance would be greatly appreciated.


DECLARE @AP_Billed_Qty table
(DocumentDate datetime,
OrderNumber varchar (100),
APBilledQty int)

Insert @AP_Billed_Qty

select
max(ap.documentdate),
max(ap.order_id),
max(ap.qty_invoicebill_base)
from nm_ap ap
group by ap.ordernumber

DocDate OrderID DocNumber Dollars Qty
2-1-09 ABC ABC1 $200 10
2-2-09 ABC ABC2 -$200 10
2-2-09 ABC ABC3 $180 10

 

[RiverGuy]

If I understand correctly, you could do something like this:

--This will return one row for each OrderID, with the Qty from the most recent DocDate for that OrderID, provided that the DocDates are unique
SELECT a.OrderID, a.Qty
FROM nm_ap ap
INNER JOIN
  (SELECT OrderID, MAX(DocDate) AS MaxDate FROM nm_ap GROUP BY OrderID) recent
ON ap.OrderID = recent.OrderID AND ap.DocDate = recent.MaxDate

You can then join that query to your complete list of Order transactions

SELECT c.OrderID, c.Qty, full_list.DocNumber, full_list.Dollars, full_list.DocDate
FROM
(SELECT a.OrderID, a.Qty
FROM nm_ap ap
INNER JOIN
  (SELECT OrderID, MAX(DocDate) AS MaxDate FROM nm_ap GROUP BY OrderID) recent
ON ap.OrderID = recent.OrderID AND ap.DocDate = recent.MaxDate) c
INNER JOIN nm_ap full_list
  ON c.OrderID = full_list.OrderID

If DocNumber is part of the logical key you are using, then also include that in the groupings and join criteria.

 

[dianemarie]

Tinkering with this now RG. I'll let you know but I'm feeling confident. You always help me solve my problems!

 

[dianemarie]

Hi RG,thanks for your help. This has gotten me almost there, except I do sometimes have competing max dates. When this occurs, I always want the transaction where ap.documenttype = 1. (They are either 1, 5 or 6) This isn't working for me. It says "incorrect syntax near where". I tried putting a where statement at the end also but it seems to ignore it. Any thoughts? Thanks much.

select
ap.nm_list_order_id,
ap.qty_invoicebill_base
from nm_ap ap
inner join
(select nm_list_order_id, max(documentdate) as MaxDocDate from nm_ap group by nm_list_order_id where ap.documenttype = 1)
recent on ap.nm_list_order_id = recent.nm_list_order_id and ap.documentdate = recent.MaxDocDate

 

[dianemarie]

OK, oops on the Where after the Group By. It didn't like me using documenttype either. Another way to skin this cat is to drop off any transactions where the qty is 0 (5's are always 0 and that's the one giving me trouble). I tried below but it now says The multi-part identifier "ap.qty_invoicebill_base" could not be bound. (twice). Once each apparently for "<> 0" and "is not null".

select
ap.nm_list_order_id,
ap.qty_invoicebill_base
from nm_ap ap
inner join
(select nm_list_order_id, max(documentdate) as MaxDocDate from nm_ap where ap.qty_invoicebill_base <> 0 and ap.qty_invoicebill_base is not null group by nm_list_order_id )
recent on ap.nm_list_order_id = recent.nm_list_order_id and ap.documentdate = recent.MaxDocDate

 

[dianemarie]

I think I got it. I was mistakenly using ap. in the nested select. I took it out and it appears to be returning the results I need. Thanks so much for all your help.

I'll be back....

 

[RiverGuy]

I'm glad you got it. It's sometimes difficult to construct a query when you can have duplicates for "the latest row." Sometimes it requires additional steps -- even up to and including using a temporary table or table variable with a temporary Identity column to arbitrarily pick one of the rows.