Photovor Photovor - 5 months ago 9
SQL Question

Creating SQL view to recursively search a schedule table

I'm trying to come up with a way of creating a view in MS SQL Server to automatically check for schedule conflicts in the same table. I would like to see if it would be possible to create a view that would add a column and list the conflicting reservation_id's

My table looks like this:

reservation_id - integer
reservation_start - datetime
reservation_end - datetime


Here is some example data:

1, 2016-07-21 06:30:00.000, 2016-07-21 07:30:00.000
2, 2016-07-21 07:00:00.000, 2016-07-22 09:00:00.000
3, 2016-08-02 08:45:00.000, 2016-08-03 09:45:00.000
4, 2016-08-02 03:00:00.000, 2016-08-02 09:00:00.000
5, 2016-08-27 02:30:00.000, 2016-08-28 03:30:00.000
6, 2016-08-30 09:00:00.000, 2016-08-30 09:00:00.000


So, I would like to create a view that would then look like this:

reservation_id, reservation_start, reservation_end, conflicts
1, 2016-07-21 06:30:00.000, 2016-07-21 07:30:00.000, [2]
2, 2016-07-21 07:00:00.000, 2016-07-22 09:00:00.000, [1]
3, 2016-08-02 08:45:00.000, 2016-08-03 09:45:00.000, [4]
4, 2016-08-02 03:00:00.000, 2016-08-02 09:00:00.000, [3]
5, 2016-08-27 02:30:00.000, 2016-08-28 03:30:00.000
6, 2016-08-30 09:00:00.000, 2016-08-30 09:00:00.000


Is this even possible, or do I need to create a temporary table to hold this information?

Answer

You need to use Cross Apply and Stuff.

Select 
    reservation_id,
    reservation_start,
    reservation_end,
   '[' + cf.conflicts + ']' as conflicts
FROM yourTableName t1
Cross Apply
(
    SELECT 
       STUFF
       ((
          SELECT ',' + CONVERT(VARCHAR(5), reservation_id)
          FROM yourTableName t2
          WHERE t1.reservation_id <> t2.reservation_id
           AND ((t1.reservation_start BETWEEN t2.reservation_start AND t2.reservation_end) 
             OR (t1.reservation_end BETWEEN t2.reservation_start AND t2.reservation_end))
    FOR XML PATH('')), 1, 1, '') as conflicts 
) cf
-- Where Clause Here like 'WHERE t1.reservation_id <> 1'

And If you want to create view with this query, Then here it is:

CREATE VIEW YourViewName
AS
-- Select Query Here

UPDATE

;WITH CTE_Reservation
AS
(
   Select 
    reservation_id,
    reservation_start,
    reservation_end
  FROM yourTableName
  -- Where Clause Here
)

Select 
    reservation_id,
    reservation_start,
    reservation_end,
   '[' + cf.conflicts + ']' as conflicts
FROM CTE_Reservation t1
Cross Apply
(
    SELECT 
       STUFF
       ((
          SELECT ',' + CONVERT(VARCHAR(5), reservation_id)
          FROM CTE_Reservation t2
          WHERE t1.reservation_id <> t2.reservation_id
           AND ((t1.reservation_start BETWEEN t2.reservation_start AND t2.reservation_end) 
             OR (t1.reservation_end BETWEEN t2.reservation_start AND t2.reservation_end))
    FOR XML PATH('')), 1, 1, '') as conflicts 
) cf
Comments