Categories
Development SQL

How do I iterate through and return each available hour of the day in SQL?

I am attempting to query data about open appointment times from a practice management system and have read-only access. There is a column for blocked slots which represents time available for booking and a column for booked slots which represents whether the time has been booked or not. Every 5 minute interval in a 24 hour period is represented as a 0 or a 1. An available time would be represented by a 1 in the blocked slots column and a 0 in the booked slots column. What is the best way to iterate through this and return available date-times?

Thus far, I have tried creating case statements which return the time as the header and the value “OPEN” if the hour is available but I just need to return each date/time for each provider and location only if it is available.

Here is the code that currently returns Date, Location, Department, Resource, Blocked Slots, and Booked Slots:

    Select
      Cast(vwApptSchedAvail.Available_Date As datetime) As [Date],
      vwApptSchedAvail.Sched_Loc_Abbr As [Location],
      vwApptSchedAvail.Sched_Dept_Abbr As [Department],
      vwApptSchedAvail.Resource_Desc As [Resource],
      Available_Days.Blocked_Slots1 + Available_Days.Blocked_Slots2 As [Blocked Slots],
      Available_Days.Booked_Slots1 + Available_Days.Booked_Slots2 As [Booked Slots]
    From
      PM.vwApptSchedAvail vwApptSchedAvail Inner Join
      PM.Available_Days Available_Days On vwApptSchedAvail.Available_Date =
        Available_Days.Available_Date And vwApptSchedAvail.Resource_ID =
        Available_Days.Resource_ID And vwApptSchedAvail.Scheduling_Department_ID =
        Available_Days.Scheduling_Department_ID And
        vwApptSchedAvail.Scheduling_Location_ID =
        Available_Days.Scheduling_Location_ID
    Where
      vwApptSchedAvail.Available_Date Between '2019-10-23' And '2019-10-23' And
      vwApptSchedAvail.Sched_Loc_Abbr = 'N-MG' And
      vwApptSchedAvail.Sched_Dept_Abbr = 'OP' And
      vwApptSchedAvail.Booking_Factor > 0
    Group By
      Cast(vwApptSchedAvail.Available_Date As date),
      vwApptSchedAvail.Sched_Loc_Abbr, vwApptSchedAvail.Sched_Dept_Abbr,
      vwApptSchedAvail.Resource_Desc, vwApptSchedAvail.Activity_Type_Abbr,
      vwApptSchedAvail.Booking_Factor, Available_Days.Blocked_Slots1,
      Available_Days.Blocked_Slots2, Available_Days.Booked_Slots1,
      Available_Days.Booked_Slots2

My query returns something like this:

    +============+============+============+==============+==================================================================================================================================================================================================================================================================================================+==================================================================================================================================================================================================================================================================================================+
    |    Date    |  Location  | Department |   Resource   |                                                                                                                                          Blocked Slots                                                                                                                                           |                                                                                                                                           Booked Slots                                                                                                                                           |
    +============+============+============+==============+==================================================================================================================================================================================================================================================================================================+==================================================================================================================================================================================================================================================================================================+
    | 10/23/2019 | Location A | OP         |   Provider 1 | 000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 | 000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 |
    +------------+------------+------------+--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | 10/23/2019 | Location A | OP         |   Provider 2 | 000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111000000000000000000000000000000000000000000000000000000000000000000000000 | 000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000111111111111111111111111111111111111111111111111000000000000111111111111111111111111000000000000000000000000000000000000000000000000000000000000000000000000 |
    +------------+------------+------------+--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | 10/23/2019 | Location A | OP         |   Provider 3 | 000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000111111111111111111111111111111111111111111111111000000000000111111111111111111111111000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 | 000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000111111111111111111111111111111111111111111111111000000000000000000000000111111111111000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 |
    +------------+------------+------------+--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | 10/23/2019 | Location A | OP         |   Provider 4 | 000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000111111111111111111111111111111111111111111111111000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 | 000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000111111110000222222222222111111112222111111111111000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 |
    +------------+------------+------------+--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

This is how the data returned when I used case statements as mentioned above:

    +============+============+============+==============+=========+=========+=========+=========+=========+=========+=========+=========+=========+=========+==========+==========+==========+==========+==========+==========+==========+==========+==========+==========+==========+==========+==========+==========+
    |    Date    |  Location  | Department |   Resource   | 0:00:00 | 1:00:00 | 2:00:00 | 3:00:00 | 4:00:00 | 5:00:00 | 6:00:00 | 7:00:00 | 8:00:00 | 9:00:00 | 10:00:00 | 11:00:00 | 12:00:00 | 13:00:00 | 14:00:00 | 15:00:00 | 16:00:00 | 17:00:00 | 18:00:00 | 19:00:00 | 20:00:00 | 21:00:00 | 22:00:00 | 23:00:00 |
    +============+============+============+==============+=========+=========+=========+=========+=========+=========+=========+=========+=========+=========+==========+==========+==========+==========+==========+==========+==========+==========+==========+==========+==========+==========+==========+==========+
    | 10/23/2019 | Location A | OP         |   Provider 2 |         |         |         |         |         |         |         |         |         |         | OPEN     |          |          |          |          | OPEN     |          |          |          |          |          |          |          |          |
    +------------+------------+------------+--------------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+

That’s better than the 0’s and 1’s but still not in the format I need.

I would ultimately like to be able to return something like this:

    +==================+============+============+==============+
    |    Date/Time     |  Location  | Department |   Resource   |
    +==================+============+============+==============+
    | 10/23/19 5:00 PM | Location A | OP         |   Provider 1 |
    +------------------+------------+------------+--------------+
    | 10/23/19 6:00 PM | Location A | OP         |   Provider 1 |
    +------------------+------------+------------+--------------+
    | 10/23/19 7:00 PM | Location A | OP         |   Provider 1 |
    +------------------+------------+------------+--------------+
    | 10/23/19 4:00 PM | Location A | OP         |   Provider 2 |
    +------------------+------------+------------+--------------+
    | 10/23/19 5:00 PM | Location A | OP         |   Provider 2 |
    +------------------+------------+------------+--------------+
    | 10/23/19 7:00 PM | Location A | OP         |   Provider 2 |
    +------------------+------------+------------+--------------+

Leave a Reply

Your email address will not be published. Required fields are marked *