using MAX is working fine until I add QuantityShipped and then it gives every order and if the order did not have the item it gives 0

Question

using MAX is working fine until I add QuantityShipped and then it gives every order and if the order did not have the item it gives 0

kat35601

SSC Eights!

Points: 825
More actions
October 5, 2016 at 10:38 am

#321985

I want to solve for every item I have I want to see when a customer last order a item, order id and the qty shipped.
when i add the qty shipped I get every order with every item and if that order did not have an item on it I get 0 qty.
again I just want to see last order for each item a customer has ever order with the qty
select ompClosedDate,omlSalesOrderID
,omlPartid
,omlQuantityShipped
from
(SELECT omlSalesOrderID
,omlPartid
,omlQuantityShipped
,max(ompClosedDate) as ompClosedDate
FROM SalesOrders
LEFT JOIN SalesOrderLines ON omlSalesOrderID = ompSalesOrderID
WHERE ompCustomerOrganizationID = '10011'
group by omlSalesOrderID
,omlPartid
,omlQuantityShipped
) as ts
order by omlPartid

Viewing 3 posts - 1 through 2 (of 2 total)

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

kat35601 (10/5/2016)
I want to solve for every item I have I want to see when a customer last order a item, order id and the qty shipped.
when i add the qty shipped I get every order with every item and if that order did not have an item on it I get 0 qty.
again I just want to see last order for each item a customer has ever order with the qty
select ompClosedDate,omlSalesOrderID
,omlPartid
,omlQuantityShipped
from
(SELECT omlSalesOrderID
,omlPartid
,omlQuantityShipped
,max(ompClosedDate) as ompClosedDate
FROM SalesOrders
LEFT JOIN SalesOrderLines ON omlSalesOrderID = ompSalesOrderID
WHERE ompCustomerOrganizationID = '10011'
group by omlSalesOrderID
,omlPartid
,omlQuantityShipped
) as ts
order by omlPartid

Would help if you could post the DDL for the tables involved. Help more if you could provide sample data for the tables and expected results based on the sample data.

Looking at the code itself, I am not sure what columns come from which tables since you don't use table aliases to help others looking at the code to know this without knowing the table schemas. Pretty sure that this is an easy problem to solve with a little more information.

drew.allen SSC Guru Points: 76985 More actions · Answer 2

Usually when people need information associated with a last instance of something, the correct method is to use a CTE that includes ROW_NUMBER with the proper partition and order and then to select rows where the row number = 1. Without sample data and expected results, this is as close as I can get.

WITH ts AS (

SELECT omlSalesOrderID

,omlPartid

,omlQuantityShipped

,ompClosedDate

,ROW_NUMBER() OVER(PARTITION BY ampCustomerOrganizationID, omlPartID ORDER BY ompClosedDate DESC) AS rn

FROM SalesOrders

LEFT JOIN SalesOrderLines ON omlSalesOrderID = ompSalesOrderID

WHERE ompCustomerOrganizationID = '10011'

)

SELECT ompClosedDate,omlSalesOrderID

,omlPartid

,omlQuantityShipped

FROM ts

WHERE rn = 1

ORDER BY omlPartid

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA