Finding the first date that occurs after another determined date.

Database
Fan

Finding the first date that occurs after another determined date.

Hi

I currently have an SQL code which determines the most recent time that an item was made serviceable.

What I'm now trying to do is to find the first date in which a notification was raised against that item that occured after the serviceable date, and then count the number of days in between.

For example:

If the item was made serviceable on 12/08/2012, from looking at another table and filtering on the same item I can see that the first notification raised after that date was on 13/08/2012, but I can't find a way of linking so that I can get that date back in the same query.

So the code I need is to bring back the first notification date that occurs after the serviceable date.

Thanks in advance.

Tags (2)
2 REPLIES
Fan

Re: Finding the first date that occurs after another determined date.

I have now managed to bring back all notification dates raised after the serviceable date.

I now need the query to bring only the first notification date after the serviceable date.

The query has brought back a serviceable date of 11/08/2012 and notifcations raised on 12th, 13th and 14th. I need to isolate the notifications on the 12th as they were the first to be raised.

Senior Apprentice

Re: Finding the first date that occurs after another determined date.

You need some variation of RANK/ROW_NUMBER:

select item, service_date, notification_date - service_date 
from ...
qualify riow_number() over (partition by item order by service_date desc) = 1

When you need more advice you should post your current query.

Dieter