CODEWITHSUNDEEP
sql-serverjoincount
Mike
Mike

Reputation: 567

Cross joining to count values for a dates

I'm on my 3rd attempt at solving this one now!

Trying to show how many holidays are 'live' on each calendar date.

At the moment I have a table with calendar dates and another with holidays. I've joined them using an outer as there's nothing to join the two tables on. So that's 4000 dates x 200,000 holidays, ouch!

The below runs for ages when top 10 selected so is not the way forward... (SQL Server)

SELECT top 10

C.CalendarDate,

Case when B.Depart <= C.CalendarDate and vwR.ReturnDate > C.CalendarDate then Count (B.ID) end as 'count'

FROM Calendar C 
CROSS JOIN Booking B -- my issue!!
LEFT join vwReturnDate vwR on vwR.ID=B.ID -- bring in the return date
AND C.CalendarDate > '2013-10-01'
AND C.CalendarDate < '2013-10-30' -- narrow to October only

Group by C.CalendarDate, B.ID, B.depart, vwR.ReturnDate

order by c.CalendarDate

Upvotes: 0

Views: 93

Answers (1)

podiluska
podiluska

Reputation: 51494

Something like this?

select 
    calendarDate, COUNT(distinct bookings.id)
from calendarDate
    left join 
        (
          select 
            booking.id, 
            booking.depart,
            vwReturnDate.returndate
          from booking
            left join vwReturnDate on booking.id = vwReturnDate.id
        ) bookings 
             on calendarDate.calendarDate between bookings.depart and bookings.returndate
 where calendarDate between '2013-10-01' and '2013-10-31'
group by calendarDate

Upvotes: 1

Related Questions