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.
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.
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.
You need some variation of RANK/ROW_NUMBER:
select item, service_date, notification_date - service_date
qualify riow_number() over (partition by item order by service_date desc) = 1
When you need more advice you should post your current query.