Find the Next Date for a given ID

[shm135]

Hi,

Thanks in advanced for your help on this issue. I have a table with two columns: Location_ID and Outage_Start. I want to create a query that lists each outage_start for a Location_ID and in another column lists the next consecutive outage_start (if there are no more outages for the Location_ID, thenthat new column should be null).

I have the following SQL which lists the next consecutive outage_start, regardless of the location_id. I need it to list the next outage_start of a given location_id.

SELECT Outages.[Location_ID], Outages.Outage_Start, (SELECT MIN(mytablemin.Outage_Start)
FROM Outages AS mytablemin WHERE mytablemin.Outage_Start > Outages.Outage_Start) AS NextDate
FROM Outages;

Please advise.

Thanks,
Samer

 

[shm135]

Hi, Thanks in advanced for your help on this issue. I have a table with two columns: Location_ID and Outage_Start. I want to create a query that lists each outage_start for a Location_ID and in another column lists the nextconsecutive outage_start (if there are no more outages for the Location_ID, then that new column should be null). I have the following SQL which lists the next consecutive outage_start, regardless of the location_id. I need it to list the next outage_start of a given location_id. SELECT Outages.[Location_ID], Outages.Outage_Start, (SELECT MIN(mytablemin.Outage_Start) FROMOutages AS mytablemin WHERE mytablemin.Outage_Start > Outages.Outage_Start) AS NextDate FROM Outages; Please advise. Thanks, Samer

Any help here please?