Question

Capturing count of records for a date between two dates

  • 9 December 2019
  • 3 replies
  • 3378 views

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
Badge

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

Userlevel 6
Badge

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:


Reply