Filtering data based on a top row

Question

I have a set of period something like

PERIODID PERIODSTART PERIODEND   PRICE STARTDOW
1        2012-12-01  2012-12-10  10    6
2        2012-12-11  2012-12-20  20    -1
3        2012-12-21  2012-12-30  30    -1

Meaning that reservation in period 1 must start on saturday, but not for periods 2 and 3.

If I have a reservation from 2012-12-10 - 2012-12-15 I want to> - filter periods for days (not a problem) - check if reservation STARTS on saturday. Filter should be only for top (or first row) and I am not sure how to do that. If reservation does not start on saturday none of the rows should be returned.

I tried

select * from periods p
where 
((@ReservationStart between p.periodstart and p.periodend)
or 
(@ReservationEnd between p.periodstart and p.periodend))
and ((select top 1 datepart(weekday, startdow) from periods where p.hotelID = period.hotelID order by period.periodstart) in (@datepart(weekday, @ReservationStart), -1))

Is there a way to do it better, or to optimize code better for large amount of data?

Peter Radocchia · Answer 1 · 2012-12-12T01:12:50.407

Mmm, not clear what you mean by the first row. First period of the reservation? Are you using this to test whether the reservation starts on the right DOW?

set datefirst 1 -- make Monday return 1 when calling datepart(day,...)

-- if reservation starts on STARTDOW, this will return a single row.
-- if not, it will return an empty record set
select top (1) * from periods p
where @ReservationStart between p.periodstart and p.periodend
  and p.STARTDOW in (-1, datepart(day,@ReservationStart))

EDIT

Maybe something like this then?

set datefirst 1 -- make Monday return 1 when calling datepart(day,...)

-- return all periods of the reservation
-- modify as necessary if you only want the first and last periods, as in your example.
select * from periods p
where p.periodend >= @ReservationStart
  and p.periodstart <= @ReservationEnd
  -- but only if the start date falls on an allowed DOW
  and exists (
    select * from periods p2
    where @ReservationStart between p2.periodstart and p2.periodend
      and p2.STARTDOW in (-1, datepart(day,@ReservationStart))
      and p2.hotelID = p.hotelID -- necessary correlation condition
    )

Yes, but I also want to a) return all the rows affected by reservation dates, b) check if the first row corresponding to reservation start date also corresponds to obligatory DOW (datefirst = row.DOW) — mko, Dec 11 '12 at 23:00
ok, so: return all periods of the reservation, but only if the start date falls on an allowed DOW, yes? — Peter Radocchia, Dec 12 '12 at 01:07

Filtering data based on a top row

1 Answers1