[RESOLVED] Make a calculation based on value in pr

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

[RESOLVED] Make a calculation based on value in pr

Author

Topic

jmiller121
Starting Member

8 Posts

Posted - 2008-11-07 : 08:52:26

Original Subject: "Make a calculation based on value in previous row"

Not really sure how to word this, so I'll tell you what I'm try to accomplish...

Fire Department.... every truck is always assigned a status, it could be IN QUARTERS (sitting in the station), ENROUTE (to a run), ON SCENE (at a run), ON THE AIR (out driving around but not on a run), plus various others. Whenever the truck's status changes, an entry is made in the database like this...

TruckID, DateTime, Status
E121, 1-1-08 13:00, InQuarters
E121, 1-1-08 15:25, Dispatched
E121, 1-1-08 15:27, Enroute
E121, 1-1-08 15:34, OnScene
E121, 1-1-08 15:50, InService
E121, 1-1-08 16:00, InQuarters

What I want to do is take every row above and add a column that represents the time difference between the current row and the next row. So my results would look like this...

TruckID, DateTime, Status, NumOfSecondsUntilNextStatusChange
E121, 1-1-08 13:00, InQuarters, 8700
E121, 1-1-08 15:25, Dispatched, 120
E121, 1-1-08 15:27, Enroute, 420
E121, 1-1-08 15:34, OnScene, 960
E121, 1-1-08 15:50, InService, 600
E121, 1-1-08 16:00, InQuarters, <can't calculate yet>

Then I'll be able to take a particular truck and see how much time spends InQuarters, OnScene, etc. over a period of time (like 3 months or so...)

SELECT TruckID, Status, Sum(NumOfSecondsUntilNextStatusChange)
FROM MyTable
GROUP BY TruckID, Status

A workaround to doing this is to export all the rows out to Excel, doing a calculation such as B2-A2, and then reimporting the rows back into another table. Something I'm trying to avoid doing...

Any help or suggestions is appreciated....Thanks!
Jason

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-07 : 09:23:03

[code]DECLARE @Sample TABLE
(
TruckID CHAR(4),
Time DATETIME,
Status VARCHAR(20),
seq INT,
PRIMARY KEY CLUSTERED
(
TruckID,
Time
)
)

INSERT @Sample
(
TruckID,
Time,
Status
)
SELECT 'E121', '1-1-08 13:00', 'InQuarters' UNION ALL
SELECT 'E121', '1-1-08 15:25', 'Dispatched' UNION ALL
SELECT 'E121', '1-1-08 15:27', 'Enroute' UNION ALL
SELECT 'E121', '1-1-08 15:34', 'OnScene' UNION ALL
SELECT 'E121', '1-1-08 15:50', 'InService' UNION ALL
SELECT 'E121', '1-1-08 16:00', 'InQuarters' UNION ALL
SELECT 'E721', '1-1-08 13:00', 'InQuarters' UNION ALL
SELECT 'E721', '1-1-08 15:25', 'Dispatched' UNION ALL
SELECT 'E721', '1-1-08 15:27', 'Enroute' UNION ALL
SELECT 'E721', '1-1-08 15:34', 'OnScene' UNION ALL
SELECT 'E721', '1-1-08 15:50', 'InService' UNION ALL
SELECT 'E721', '1-1-08 16:00', 'InQuarters'

SELECT *
FROM @Sample
ORDER BY TruckID,
Time

DECLARE @Seq INT,
@Truck CHAR(4)

UPDATE @Sample
SET @Seq = seq = CASE
WHEN TruckID = @Truck THEN @Seq + 1
ELSE 1
END,
@Truck = TruckID

SELECT s1.TruckID,
s1.Time,
s1.Status,
DATEDIFF(SECOND, s1.Time, s2.Time) AS NumOfSecondsUntilNextStatusChange
FROM @Sample AS s1
LEFT JOIN @Sample AS s2 ON s2.TruckID = s1.TruckID
AND s2.seq = s1.seq + 1
ORDER BY s1.TruckID,
s1.Time[/code]

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-07 : 09:35:46

[code]SELECT TruckID,
DateTime,
Status,
DATEDIFF(ss,DateTime,NextDateTime) AS NumOfSecondsUntilNextStatusChange
FROM
(
SELECT TruckID,
DateTime,
Status,
(SELECT TOP 1 DateTime FROM YourTable WHERE TruckID=t.TruckID AND DateTime>t.DateTime ORDER BY DateTime ASC)AS NextDateTime
FROM YourTable
)t
[/code]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-07 : 09:46:07

Will not run at all. See fix below in red.

quote:

Originally posted by visakh16

SELECT TruckID,
DateTime,
Status,
DATEDIFF(ss,DateTime,NextDateTime) AS NumOfSecondsUntilNextStatusChange
FROM
(
SELECT TruckID,
DateTime,
Status,
(SELECT TOP 1 DateTime FROM YourTable WHERE TruckID=x.TruckID AND DateTime>x.DateTime  ORDER BY DateTime ASC)AS NextDateTime
FROM YourTable as x
)t

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-07 : 10:04:22

ah my bad...
thanks for the spot

jmiller121
Starting Member

8 Posts

Posted - 2008-11-07 : 15:52:13

THANK YOU!!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources