November 25, 2006 at 5:42 pm
Hi,
Let say, i've these following table:-
licenses
TransID | VehicleID | DateApproved
----------------------------------------
1 | AU2985 | 2004-10-13
2 | BX3290 | 2005-09-28
3 | UJ5345 | 2006-10-13
* The above VehicleID is unique.
licenses_renew
TransID | VehicleID | RenewDate
----------------------------------------
1 | AU2985 | 2005-10-18
2 | BX3290 | 2006-09-29
3 | AU2985 | 2006-10-19
DateApproved will expires after 1 year. RenewDate also will expires after 1 year.
Assume, current system date is 2005-11-26. The expected result shown these following:-
VehicleID | DateApproved_RenewDate | ExpiredDate | Status
----------------------------------------------------------------------
AU2985 | 2004-10-13 | 2005-10-13 | Need to renew
AU2985 | 2005-10-18 | 2006-10-18 | Need to renew
AU2985 | 2006-10-19 | 2007-10-19 | Still Valid
BX3290 | 2005-09-28 | 2006-09-28 | Need to renew
BX3290 | 2006-09-29 | 2007-10-29 | Still Valid
UJ5345 | 2006-10-13 | 2007-10-13 | Still Valid
How to query that?
Please help me.
November 27, 2006 at 8:18 am
How's this?
SELECT
VehicleID,
Date AS DateApproved_RenewDate,
DATEADD(YY, 1, Date) AS ExpiredDate,
CASE
WHEN GETDATE() > DATEADD(YY, 1, Date) THEN 'Need to renew'
ELSE 'Still Valid'
END AS Status
FROM ( SELECT VehicleID,
DateApproved AS Date
FROM licenses
UNION
SELECT VehicleID,
RenewDate AS Date
FROM licenses_renew) Q
ORDER BY VehicleID, Date
November 27, 2006 at 10:58 am
The query posted above by Erik will certainly give you the results that you have posted, but are those the results you really want?
I am thinking that you really only want to get the latest record for each type...is this correct?
If so, just make a small change to Erik's query above, similar to the solution posted for you on your post in the other forum.
SELECT VehicleID,
Date AS DateApproved_RenewDate,
DATEADD(YY, 1, Date) AS ExpiredDate,
CASE
WHEN GETDATE() > DATEADD(YY, 1, Date) THEN 'Need to renew'
ELSE 'Still Valid'
END AS Status
FROM ( SELECT VehicleID,
DateApproved AS Date
FROM licenses
UNION
SELECT VehicleID,
max(RenewDate) AS Date
FROM licenses_renew GROUP BY VehicleID) Q
ORDER BY VehicleID, Date
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply