datediff comparison to previous row in select statement

In a table I have information of a vehicle's status with an appropriate
date, what I am trying to achieve is to display the number of days
between two dates of two different statuses. Problem is that the same
scenario can happen on more than one occasion and there is no data
linking 2 statuses together. The query ran is
select distinct v.[reg no_],vsh.status,convert(char(16),vsh.[from
datetime],20)
from vehicle v
left join [vehicle status history] vsh on v.[vehicle serial no_] =
vsh.[vehicle serial no_]
and vsh.status IN('BOOKING-IN','DESPATCHED')
where v.[reg no_]='R3RTF'
order by 3
reg no_ status
-- -- --
R3RTF BOOKING-IN 2005-01-11 08:22
R3RTF DESPATCHED 2005-02-03 18:34
R3RTF BOOKING-IN 2005-02-04 09:58
R3RTF DESPATCHED 2005-02-04 14:52
R3RTF BOOKING-IN 2005-04-05 14:33
R3RTF DESPATCHED 2005-06-01 17:37
Looking at these results what I need is the date difference between the
BOOKING-IN and DESPATCHED dates. From the result set above the 3
datediff (for days) values would be 23, 0 & 57.
This is all part of a bigger result set and ideally I would like the
sum of the 3 date differences shown in this example. (23 + 0 + 57 = 80)
I am struggling to get this in a select statement, I have sampled using
cursors but I'm not sure this is the way to go. Using a SELECT
statement would be ideal.
Any help would be much appreciated.You can get the time a vehicle was booked in at a certain occasion with:
SELECT vd.[vehicle serial no_], vd.[from datetime], MIN(DATEDIFF(dd,
vb.[from datetime], vd.[from datetime])
FROM [vehicle status history] vd
INNER JOIN [vehicle status history] vb
ON vd.[vehicle serial no_] = vb.[vehicle serial no_]
AND vd.[from datetime] > vb.[from datetime]
WHERE vd.status = 'DESPATCHED'
AND vd.status = 'BOOKING-IN'
GROUP BY vd.[vehicle serial no_], vd.[from datetime]
If you want to have the sum of the times a vehicle spend booked in, you can
SUM over the previous query:
SELECT [vehicle serial no_], SUM(days_spend)
FROM (
SELECT vd.[vehicle serial no_], MIN(DATEDIFF(dd, vb.[from datetime],
vd.[from datetime]) AS days_spend
FROM [vehicle status history] vd
INNER JOIN [vehicle status history] vb
ON vd.[vehicle serial no_] = vb.[vehicle serial no_]
AND vd.[from datetime] > vb.[from datetime]
WHERE vd.status = 'DESPATCHED'
AND vd.status = 'BOOKING-IN'
GROUP BY vd.[vehicle serial no_], vd.[from datetime]
) ds
GROUP BY [vehicle serial no_]
(everything untested)
Jacco Schalkwijk
SQL Server MVP
"robz8701" <robz8701@.hotmail.com> wrote in message
news:1128070087.999572.326490@.g49g2000cwa.googlegroups.com...
> In a table I have information of a vehicle's status with an appropriate
> date, what I am trying to achieve is to display the number of days
> between two dates of two different statuses. Problem is that the same
> scenario can happen on more than one occasion and there is no data
> linking 2 statuses together. The query ran is
> select distinct v.[reg no_],vsh.status,convert(char(16),vsh.[from
> datetime],20)
> from vehicle v
> left join [vehicle status history] vsh on v.[vehicle serial no_] =
> vsh.[vehicle serial no_]
> and vsh.status IN('BOOKING-IN','DESPATCHED')
> where v.[reg no_]='R3RTF'
> order by 3
> reg no_ status
> -- -- --
> R3RTF BOOKING-IN 2005-01-11 08:22
> R3RTF DESPATCHED 2005-02-03 18:34
> R3RTF BOOKING-IN 2005-02-04 09:58
> R3RTF DESPATCHED 2005-02-04 14:52
> R3RTF BOOKING-IN 2005-04-05 14:33
> R3RTF DESPATCHED 2005-06-01 17:37
> Looking at these results what I need is the date difference between the
> BOOKING-IN and DESPATCHED dates. From the result set above the 3
> datediff (for days) values would be 23, 0 & 57.
> This is all part of a bigger result set and ideally I would like the
> sum of the 3 date differences shown in this example. (23 + 0 + 57 = 80)
> I am struggling to get this in a select statement, I have sampled using
> cursors but I'm not sure this is the way to go. Using a SELECT
> statement would be ideal.
> Any help would be much appreciated.
>