Capturing count of records for a date between two dates

  • 9 December 2019
  • 3 replies

Hello awesome people,

I am stuck with this problem where I have records of hotel bookings, where I need to find the bookings that happened for a particular day. For eg, A booked hotel from Jan 1 - Jan 5, B booked the same hotel from Jan 3 - Jan5 and C booked the hotel for Jan 3. I would like to know the number of bookings for Jan 3 and the count should be Count = 3.

The table has a booking id, from date to date as columns. What do you think is the best way to approach this problem to find out the count for January 3rd?

3 replies

Userlevel 5

Welcome on, Sandy!

One easy way is to create a yesno field with SQL logic to only include records fitting within a date range (say, “is_within_date_range”). At that point, you could create a specific count measure that automatically filters on “is_within_date_range”. I’ll try to look around on here for a good reference post that spells it out in more detail 🙂

Userlevel 2

You could create a derived table for “booking-days”, basically duplicating the booking record for each day it included. Something like:

select * from date_tbl
left join ${bookings.SQL_TABLE_NAME} bookings
on >= bookings.from_date and < bookings.to_date

Then your date filter would just be the column, and your count would be a distinct count of booking IDs.

This would require creating a date table if you haven’t already ( shows one way to do it in Redshift).

Userlevel 7

Sorry to revive an older post, but just wanted to make sure that if anyone is looking for an answer to this, I have shared an approach here: