Query to show the Expiry dates

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 2005 Forums

Transact-SQL (2005)

Query to show the Expiry dates

Author

Topic

sara_23apr
Starting Member

8 Posts

Posted - 2008-09-25 : 02:25:02

Data in the data baseVeh No. Category Payment Date EffectiveDate Expiry Date Amt
KA55 Permit 1-01-06 1-01-06 31-12-06 400
KA55 Permit 24-01-07 25-01-07 31-12-07 500
KA55 Permit 1-01-08 1-01-08 31-12-08 800
KA100 Permit 24-01-07 25-01-07 31-12-07 500
KA100 Permit 1-01-08 1-01-08 31-12-08 800
KA55 Tax 24-01-07 25-01-07 31-12-07 300
KA55 Tax 1-01-08 1-01-08 31-12-08 600
KA55 Insurance 24-01-07 25-01-07 31-12-07 500
KA55 Insurance 1-01-08 1-01-08 2-2-09 700

Result needed
Veh No. Category Payment Date Expiry Date Amount
KA55 Permit 1-01-08 31-12-08 800
KA100 Permit 1-01-08 31-12-08 800
KA55 Tax 1-01-08 31-12-08 600
KA55 Insurance 1-01-08 2-2-09 700

Dear Friends

I Need to show last record ( with highest “Expiry Date”) for each of the Vehicle number and category combination.

Previously I just had to show the vehicle number , category and expiry date. so i had done a group by query

Now I do not know how to show the Payment date, vehicle number , category and expiry date, Amount in the same query Kindly help me

Regards
Sara

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-25 : 02:31:45

[code]SELECT VehNo,
Category,
PaymentDate,
ExpiryDate,
Amount
FROM
(SELECT ROW_NUMBER() OVER(PARTITION BY VehNo,Category ORDER BY ExpiryDate DESC) AS Seq,*
FROM YourTable)t
WHERE t.Seq=1[/code]

or

[code]SELECT t.VehNo,
t.Category,
t.PaymentDate,
t.ExpiryDate,
t.Amount
FROM YourTable t
INNER JOIN (SELECT VehNo,Category,MAX(ExpiryDate) AS MaxExpiry
FROM YourTable
GROUP BY VehNo,Category)tmp
ON t.VehNo=tmp.VehNo
AND t.Category=tmp.Category
AND t.ExpiryDate=tmp.MaxExpiry[/code]

sara_23apr
Starting Member

8 Posts

Posted - 2008-09-25 : 06:28:00

Thank you for the reply
Will try it
Sara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources