SQL Server 2008 – How to Query for Latest State Record After Latest Date

sql-server-2008

I have a table with openingdays, location, state (Open, Day Closed, Shift Closed) and the actual date. I would like to know when the day is closed for the last time (state = D) and everything what happens after that date (state Opened, Shift Closed) for the specific location.

location  day          state  actual_date
-----------------------------------------------------
ams,      2015-11-25,  O      2015-11-25 12:20:59.000
ams,      2015-11-25,  D      2015-11-26 02:10:00.000
ams,      2015-11-26,  O      2015-11-26 12:20:59.000
ams,      2015-11-26,  D      2015-11-27 02:10:00.000
sch,      2015-11-25,  O      2015-11-25 14:12:27.000
sch,      2015-11-25,  D      2015-11-26 03:23:13.000
sch,      2015-11-26,  O      2015-11-26 14:12:27.000
sch,      2015-11-26,  S      2015-11-26 18:26:24.000

What I would like to have is

ams,      2015-11-26,  D      2015-11-27 02:10:00.000
ams,      2015-11-26,  O      2015-11-26 12:20:59.000
sch,      2015-11-26,  S      2015-11-26 18:26:24.000
sch,      2015-11-26,  O      2015-11-26 14:12:27.000
sch,      2015-11-25,  D      2015-11-26 03:23:13.000
sch,      2015-11-25,  O      2015-11-25 14:12:27.000

I already created this:

SELECT k.location, k.day, k.state, k.actual_date
FROM myTable k
INNER JOIN (
    SELECT state, max(day) as MaxDate
    FROM myTable k2
    GROUP BY state
) k2 ON k.state = k2.state AND k.day = k2.MaxDate

Which outputs everything on the latest day, but it wont show me the results on the days after the latest Day Closed (state = D).

Best Answer

Solved it myself.. was actually very easy..

SELECT *
FROM myTable
WHERE day >= ( SELECT max(day) 
            FROM [dbo].[myTable]
            WHERE state = 'D'
            AND location = 'ams'
)
ORDER BY [location] DESC, [actual_date] DESC

Thanks anyway for looking up to my issue.

Related Question