July 4, 2007 at 10:12 am
Wasn't sure how to word the Subject Line, but here is my question...
(Using SQL Server 2000)
There is a table in our Fire Department database that stores our trucks' status (Dispatched, Enroute, On Scene, etc...) The format of the data is as below...
Truck Status StatusDateTime IncdNo Dispatcher
M121 Dispatched 12:30:30 1234 13-1290
E121 Dispatched 12:30:30 1234 13-1290
L121 Dispatched 12:30:30 1234 13-1290
M121 Enroute 12:32:32 1234 14-0001
M121 On Scene 12:38:38 1234 15-0054
E121 Enroute 12:34:24 1234 15-0054
E121 In Service 12:40:00 1234 13-1290
L121 Cancelled 12:43:22 0000 13-1290
What I want is a query that shows each unique Truck and what their last Status, IncdNo, and Dispatcher were based on the latest StatusDateTime. I want the results from the above table to look like this..
Truck Status StatusDateTime IncdNo Dispatcher
M121 On Scene 12:38:38 1234 15-0054
E121 In Service 12:40:00 1234 13-1290
L121 Cancelled 12:43:22 0000 13-1290
How do I write a query that gets me these results? A GroupBy query doesn't seem to work because the only fields I want to group are Truck and get the Max(StatusDateTime), but how do I show what the Status, IncdNo, and Dispatcher were without grouping them?
Thanks for your input!
Jason Miller
July 4, 2007 at 11:14 am
Try joining to a derived table.
SELECT T.*
FROM YourTable T
JOIN (
SELECT T1.Truck, MAX(T1.StatusDateTime) AS StatusDateTime
FROM YourTable T1
GROUP T1.Truck
) D
ON T.Truck = D.Truck
AND T.StatusDateTime = D.StatusDateTime
July 4, 2007 at 11:23 am
USE SSC
GO
IF EXISTS (SELECT * FROM dbo.SysObjects WHERE Name = 'Demo' AND XType = 'U' AND USER_NAME(uid) = 'dbo')
DROP TABLE dbo.Demo
GO
CREATE TABLE dbo.Demo
(
TruckID INT NOT NULL
, StatusDate DATETIME NOT NULL
, Status VARCHAR(25) NOT NULL
, OperatorID INT NOT NULL
)
GO
CREATE CLUSTERED INDEX CIX_Demo_StatusDate ON dbo.Demo (StatusDate, TruckID)
GO
INSERT INTO dbo.Demo (TruckID, StatusDate, Status, OperatorID) VALUES (1, '2007-07-07 13:13:13.333', 'Dispatched', 7)
INSERT INTO dbo.Demo (TruckID, StatusDate, Status, OperatorID) VALUES (2, '2007-07-07 13:20:58.345', 'Dispatched', 8)
INSERT INTO dbo.Demo (TruckID, StatusDate, Status, OperatorID) VALUES (1, '2007-07-07 13:14:00.000', 'En route', 9)
INSERT INTO dbo.Demo (TruckID, StatusDate, Status, OperatorID) VALUES (2, '2007-07-07 13:25:20.765', 'Dispatched', 10)
INSERT INTO dbo.Demo (TruckID, StatusDate, Status, OperatorID) VALUES (2, '2007-07-07 13:35:22.678', 'On scene', 11)
INSERT INTO dbo.Demo (TruckID, StatusDate, Status, OperatorID) VALUES (3, '2007-07-07 14:50:12.250', 'Dispatched', 12)
GO
SELECT Trucks.* FROM dbo.Demo Trucks INNER JOIN
(SELECT TruckID, MAX(StatusDate) AS CurrDate FROM dbo.Demo GROUP BY TruckID) dtLastInfo
ON Trucks.TruckID = dtLastInfo.TruckID AND Trucks.StatusDate = dtLastInfo.CurrDate
ORDER BY Trucks.TruckID
GO
IF EXISTS (SELECT * FROM dbo.SysObjects WHERE Name = 'Demo' AND XType = 'U' AND USER_NAME(uid) = 'dbo')
DROP TABLE dbo.Demo
GO
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply