Capturing count of records for a date between two dates

[LookerGuru]

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?

[bens1]

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 🙂

[michael_zearn]

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 date_tbl.date >= bookings.from_date and date_tbl.date < bookings.to_date

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

This would require creating a date table if you haven’t already (https://medium.com/@elliotchance/building-a-date-dimension-table-in-redshift-6474a7130658 shows one way to do it in Redshift).

[fabio1]

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:

SQL Pattern: Summarizing entities with a start/end date over time SQL & Derived Tables

Do you have date tables in your model? If so, you’ve probably noticed performance issues around them, but may not know how to resolve them. There are various problems that tend to crop up when people use date tables, but in this article I’ll focus specifically on the intentional (though unnecessary and problematic) fanning out of records in order to count or summarize by date a number of entities that last for a period of time. Examples of questions that directly follow this pattern would be: …