Retrieving previous Date 1

[gavinjb]

Hi,

I am trying to write a report which returns the Current Date and Previous Date of a service due for my Vehicle Database.

The Tables I am using are :

Vehicle:
Vehicle ID
RegNo
FleetNo
...

Service:
Service ID
Date
Vehicle ID
...

I need to return the RegNo, FleetNo, PreviousDate and CurrentDate

both previousDate and currentDate need to be retrieved from the Service.Date field.

Does anyone have any ideas how to achieve this.

Thanks

Gavin,

 

[DanJR]

Here's a query that will retrieve the last service date and the second-to-last service date for each vehicle:

SELECT L.*, (SELECT MAX(A.Date) 
             FROM Service A 
             WHERE A.VehicleID =L.VehicleID 
               AND A.Date<L.LastDate) AS SecondToLastDate
FROM
(SELECT V.VehicleID, V.RegNo, V.FleetNo, Max(S.Date) AS LastDate
 FROM Vehicle AS V, Service AS S 
 WHERE V.VehicleID = S.VehicleID
 GROUP BY V.VehicleID, V.RegNo, V.FleetNo) AS L;

Does this help?

Cheers,
Dan

 

[gavinjb]

DanJR,

Thanks for the SQL, it is close. I forgot to mention on my original Message that you can have any ammount of Records pn Service Table for any vehicle so, in you could have 10 records a any particular Reg, and what I am really after is so show the previous date for each record e.g.

Reg PreviousDate Currentdate
T123GHN NULL 14/12/2003
T123GHN 14/12/2002 18/7/2003
T123GHN 18/7/2003 12/12/2003
T123GHN 12/12/2003 12/04/2004

Any ideas.

Thanks,

Gavin,

 

[DanJR]

Here's an SQL statement which displays the result-set you have shown above. However, the query assumes that a given vehicle does not have more than one service on the same day. Is this assumption ok?

SELECT V.RegNo, 
       S.Date AS CurrentDate, 
       (SELECT MAX(S1.Date) 
         FROM Service S1 
         WHERE S1.VehicleID=S.VehicleID AND S1.Date< S.Date ) AS PreviousDate
FROM Vehicle V, Service S
WHERE V.VehicleID = S.VehicleID

cheers,
Dan

 

[gavinjb]

That worked perfectly thanks for all your help, give your selves all a star