1

I am making a cinema booking aplication. I have to make a schedule that is efficient for the cinema. That means that time(space) between 2 movies should be 0.

What I have to do: I have to make a Functionality that can find the max space of 30 minutes between 2 movies and set it back to 0, so that the movies have a perfect overlap.

So for example:

Movie 1 : 11:00 - 13:00

Movie 2: 13:30 - 15:00

Movie 3: 15:30 - 16:00

With the functionality the result should be:

Movie 1 : 11:00 - 13:00

Movie 2: 13:00 - 15:00

Movie 3: 15:00 - 16:00

Here is my table: ShowTable

Bogdan Sahlean
  • 19,233
  • 3
  • 42
  • 57
Alegou20
  • 60
  • 7
  • Can you provide the table structure of the tables that will be involved with some sample data? – Jason Jun 04 '17 at 20:41
  • I added a picture of the table. The other tables are not relevant. They do not have information that influence the query. ( Like: the table room and film only have a RoomID and a FilmId ) – Alegou20 Jun 04 '17 at 20:49

1 Answers1

1
IF OBJECT_ID('tempdb..#t') IS NOT NULL DROP TABLE #t
CREATE TABLE #t(Movie varchar(10),Starttime TIME,EndTime time)
INSERT INTO #t(Movie,Starttime,EndTime)
SELECT 'Movie 1','11:00','13:00' UNION ALL
SELECT 'Movie 2','13:30','15:00' UNION ALL
SELECT 'Movie 3','15:30','16:00'
UPDATE t SET t.Starttime=p.EndTime 
FROM #t AS t
CROSS APPLY(SELECT MAX(tt.EndTime) AS EndTime FROM #t AS tt WHERE DATEDIFF(MINUTE,tt.EndTime,t.Starttime)>0) p
WHERE p.EndTime IS NOT NULL

SELECT * FROM #t

Movie      Starttime        EndTime
---------- ---------------- ----------------
Movie 1    11:00:00.0000000 13:00:00.0000000
Movie 2    13:00:00.0000000 15:00:00.0000000
Movie 3    15:00:00.0000000 16:00:00.0000000

Which version are you using, If you are using 2012+ you also can you use LAG

SELECT *,LAG(EndTime)OVER(ORDER BY EndTime) AS NewStartTime FROM #t

Movie      Starttime        EndTime          NewStartTime
---------- ---------------- ---------------- ----------------
Movie 1    11:00:00.0000000 13:00:00.0000000 NULL
Movie 2    13:30:00.0000000 15:00:00.0000000 13:00:00.0000000
Movie 3    15:30:00.0000000 16:00:00.0000000 15:00:00.0000000

I add roomid into sample data

IF OBJECT_ID('tempdb..#t') IS NOT NULL DROP TABLE #t
CREATE TABLE #t(ShowID int,RoomID int, Movie varchar(10),Starttime TIME,EndTime time)
INSERT INTO #t(ShowID,RoomId,Movie,Starttime,EndTime)
SELECT 1,1,'Movie 1','11:00','13:00' UNION ALL
SELECT 3,1,'Movie 2','13:30','15:00' UNION ALL
SELECT 4,1,'Movie 3','15:30','16:00' UNION ALL
SELECT 2,2,'Movie 3','12:30','14:30' UNION ALL
SELECT 5,2,'Movie 1','15:30','16:30' 
SELECT *,LAG(EndTime)OVER(partition by RoomId ORDER BY EndTime) AS NewStartTime FROM #t
UPDATE t SET t.Starttime=isnull(p.NewStartTime,t.Starttime) 
FROM #t AS t
inner join(
  SELECT *,LAG(EndTime)OVER(partition by RoomId ORDER BY EndTime) AS NewStartTime FROM #t   
) as p on t.ShowId=p.ShowId

select *  FROM #t AS t

Did the following result is you wanted?


    ShowID  RoomID  Movie   Starttime   EndTime
1   1   1   Movie 1 11:00:00    13:00:00
2   3   1   Movie 2 13:00:00    15:00:00
3   4   1   Movie 3 15:00:00    16:00:00
4   2   2   Movie 3 12:30:00    14:30:00
5   5   2   Movie 1 14:30:00    16:30:00
Nolan Shang
  • 2,312
  • 1
  • 14
  • 10