October 5, 2016 at 10:38 am
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
October 5, 2016 at 10:50 am
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.
October 5, 2016 at 11:10 am
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
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply