0

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).

1
  • Do you mean "for a specific location" i.e. ams or "for each location"? Nov 30, 2015 at 14:33

1 Answer 1

0

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.

5
  • I would have thought this would return two rows for ams and two for sch, which is not what the question lists as desired output. Nov 30, 2015 at 14:31
  • Well.. it shouldn't.. it will take everything on the last date when it had state = 'D' and everything above. It will also take everything on the day of state D itself, but that isnt a problem for me.
    – Mitchell
    Nov 30, 2015 at 14:45
  • Shouldn't or doesn't? I don't have a copy of sql server to hand just now. Nov 30, 2015 at 14:52
  • The inner query will return one value - 2015-11-26. The outer will return rows with a greater or equal value - rows 3, 4, 7 & 8 from the example data. Two for ams and two for sch. Nov 30, 2015 at 14:57
  • You're right.. This won't give the desired output. But it solved my issue. I will modify the question so it will fit in the answer. I have a where clause in the inner query for location so it will only take sch or ams. In that case.. the inner query results in 2015-11-25 for sch and 2015-11-26 for ams.
    – Mitchell
    Nov 30, 2015 at 15:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.