July 7, 2010 at 7:56 am
I have a table of orders, a table of orderLocationHistory and a table of orderLocations. The orderLocationHistory table is updated with foreign keys from the orders and orderLocations table every time the order moves.
I need to be able to grab a list of orders where the most recent change in the orderLocationHistory matches a value in the orderLocations table.
For example, I need a list of all orders where the most recent location is "not processed".
July 7, 2010 at 8:29 am
How about table layouts, sample data and expected results?
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 7, 2010 at 8:43 am
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#orders','U') IS NOT NULL
DROP TABLE #orders
--===== Create the test table with
CREATE TABLE #orders
(
order_Pk INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
orderID varchar(10)
)
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#orderLocations','U') IS NOT NULL
DROP TABLE #orderLocations
--===== Create the test table with
CREATE TABLE #orderLocations
(
orderLocation_Pk INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
orderLocation varchar(20)
)
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#orderLocationHistory','U') IS NOT NULL
DROP TABLE #orderLocationHistory
--===== Create the test table with
CREATE TABLE #orderLocationHistory
(
orderLocationHistoryTS DATETIME,
orderLocation_Fk int,
order_Fk int
)
--===== All Inserts into the IDENTITY column
SET IDENTITY_INSERT #orders ON
--===== Insert the test data into the test table
INSERT INTO #orders
(orderID, order_Pk)
SELECT 'S200211737','37' UNION ALL
SELECT 'S200211838','38' UNION ALL
SELECT 'S200211940','40' UNION ALL
SELECT 'S200211941','41' UNION ALL
SELECT 'S200211942','42'
--===== All Inserts into the IDENTITY column
SET IDENTITY_INSERT #orderlocations ON
--===== Insert the test data into the test table
INSERT INTO #orderlocations
(orderlocation, orderlocation_Pk)
SELECT 'Not Processed','1' UNION ALL
SELECT 'File','2'
--===== Insert the test data into the test table
INSERT INTO #orderlocationHistory
(order_fk,orderLocationHistoryTS, orderlocation_fk)
SELECT '37','Mar 17 2006 9:53AM','1' UNION ALL
SELECT '38','Mar 17 2006 9:55AM','1' UNION ALL
SELECT '40','Mar 17 2006 9:57AM','1' UNION ALL
SELECT '41','Mar 17 2006 9:59AM','1' UNION ALL
SELECT '42','Mar 17 2006 10:00AM','1' UNION ALL
SELECT '37','Mar 17 2006 10:01AM','1' UNION ALL
SELECT '40','Mar 17 2006 10:03AM','2' UNION ALL
SELECT '42','Mar 17 2006 10:08AM','2'
I am trying to get a list of orderID's from the orders table where the most recent location referenced by the orderLocationHistory table = 'Not Processed'
I have code written for getting the most recent orders that match specific criteria within the order table but I can't figure out how to do something similar where there isn't a matching date to reference in a cross referenced table.
SELECT orderid,orderdate, billtocompany
FROM orders AS o
WHERE orderDate = (
SELECT MAX(orderDate)
FROM orders AS mo join orderstatuses on orderstatus_pk = orderstatus_fk
WHERE mo.User_fk = o.user_fk and pending=0
) and (billtocompany like '%school%')
order by billtocompany
July 7, 2010 at 9:42 am
I think this is what you are looking for
create tables
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#orders','U') IS NOT NULL
DROP TABLE #orders
--===== Create the test table with
CREATE TABLE #orders
(
order_Pk INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
orderID varchar(10)
)
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#orderLocations','U') IS NOT NULL
DROP TABLE #orderLocations
--===== Create the test table with
CREATE TABLE #orderLocations
(
orderLocation_Pk INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
orderLocation varchar(20)
)
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#orderLocationHistory','U') IS NOT NULL
DROP TABLE #orderLocationHistory
--===== Create the test table with
CREATE TABLE #orderLocationHistory
(
orderLocationHistoryTS DATETIME,
orderLocation_Fk int,
order_Fk int
)
Add Sample Data
--===== All Inserts into the IDENTITY column
SET IDENTITY_INSERT #orders ON
--===== Insert the test data into the test table
INSERT INTO #orders
(orderID, order_Pk)
SELECT 'S200211737','37' UNION ALL
SELECT 'S200211838','38' UNION ALL
SELECT 'S200211940','40' UNION ALL
SELECT 'S200211941','41' UNION ALL
SELECT 'S200211942','42'
SET IDENTITY_INSERT #orders OFF
--===== All Inserts into the IDENTITY column
SET IDENTITY_INSERT #orderlocations ON
--===== Insert the test data into the test table
INSERT INTO #orderlocations
(orderlocation, orderlocation_Pk)
SELECT 'Not Processed','1' UNION ALL
SELECT 'File','2'
SET IDENTITY_INSERT #orderlocations OFF
--===== Insert the test data into the test table
INSERT INTO #orderlocationHistory
(order_fk,orderLocationHistoryTS, orderlocation_fk)
SELECT '37','Mar 17 2006 9:53AM','1' UNION ALL
SELECT '38','Mar 17 2006 9:55AM','1' UNION ALL
SELECT '40','Mar 17 2006 9:57AM','1' UNION ALL
SELECT '41','Mar 17 2006 9:59AM','1' UNION ALL
SELECT '42','Mar 17 2006 10:00AM','1' UNION ALL
SELECT '37','Mar 17 2006 10:01AM','1' UNION ALL
SELECT '40','Mar 17 2006 10:03AM','2' UNION ALL
SELECT '42','Mar 17 2006 10:08AM','2'
Retrieve Data
select *
from #orders o
inner join (select orderLocation_FK, order_FK, max(orderLocationHistoryTS) orderLocationHistoryTS
from #orderLocationHistory
group by orderLocation_FK, order_FK) olh
on o.order_pk = olh.order_fk
inner join #orderlocations ol
on olh.orderlocation_FK = ol.orderLocation_PK
where ol.OrderLocation = 'Not Processed'
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 7, 2010 at 9:53 am
Okay, I was wrong. That query pulls any order that has ever had a status of 'Not processed'.
July 8, 2010 at 3:33 pm
Any other ideas?
July 9, 2010 at 6:20 am
What are you looking for? All records where there was a OrderLocation of Not Processed?
select *
from #orders o
inner join #orderLocationHistory olh
on o.order_pk = olh.order_fk
inner join #orderlocations ol
on olh.orderlocation_FK = ol.orderLocation_PK
where ol.OrderLocation = 'Not Processed'
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 9, 2010 at 6:32 am
No, all records where the most recent record is not processed. The results should only have included 37,38,41.
July 9, 2010 at 6:42 am
Are you looking for this? I think there's a better way to write this, but I want to make sure I have the correct result set first
select *
from #orders o
inner join #orderLocationHistory olh
on o.order_pk = olh.order_fk
inner join #orderlocations ol
on olh.orderlocation_FK = ol.orderLocation_PK
where ol.OrderLocation = 'Not Processed'
and o.Order_pk not in
(select o.Order_pk
from #orders o
inner join #orderLocationHistory olh
on o.order_pk = olh.order_fk
inner join #orderlocations ol
on olh.orderlocation_FK = ol.orderLocation_PK
where ol.OrderLocation <> 'Not Processed')
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 9, 2010 at 7:16 am
No, because that doesn't look at the most recent entry for each order. It still returns most orders instead of just the ones that have "not processed" as the most recent status.
July 9, 2010 at 7:21 am
select *
from #orders o
inner join (select orderLocation_FK, order_FK, max(orderLocationHistoryTS) orderLocationHistoryTS
from #orderLocationHistory
group by orderLocation_FK, order_FK) olh
on o.order_pk = olh.order_fk
inner join #orderlocations ol
on olh.orderlocation_FK = ol.orderLocation_PK
where ol.OrderLocation = 'Not Processed'
and o.Order_pk not in
(select o.Order_pk
from #orders o
inner join #orderLocationHistory olh
on o.order_pk = olh.order_fk
inner join #orderlocations ol
on olh.orderlocation_FK = ol.orderLocation_PK
where ol.OrderLocation <> 'Not Processed')
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 9, 2010 at 8:52 am
That did it. Now I have to dissect the code and figure out how it works.
July 16, 2010 at 2:35 pm
In going through the code I realized that the code was still not quite right. Now the code pulls all rows where the ONLY status is 'Not Processed'. If there is an older different status, it still isn't getting selected.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply