May 10, 2016 at 6:35 pm
Need query help for the following
I have a sample data, as below on one of my table.
============
IF OBJECT_ID('tempdb..#MovieShows') IS NOT NULL DROP TABLE #MovieShows
GO
Create table #MovieShows
(Id int, Movieid varchar(20), Showtime time)
insert into #MovieShows values (11,'m1','13:00')
insert into #MovieShows values (23,'m2','14:00')
insert into #MovieShows values (34,'m1','15:00')
insert into #MovieShows values (45,'m2','16:00')
insert into #MovieShows values (55,'m2','20:00')
insert into #MovieShows values (64,'m1','16:00')
insert into #MovieShows values (66,'m2','21:00')
insert into #MovieShows values (81,'m1','20:00')
go
select * from #MovieShows order by Movieid, id
===============
Need a query to show the missing rows along with table rows. Desired output should be
Id MovieID Showtime
11m113:00
11m114:00 --New row
34m115:00
64m116:00
64m117:00 --New row
64m118:00 --New row
64m119:00 --New row
81m120:00
23m214:00
23m215:00 --New row
45m216:00
45m217:00 --New row
45m218:00 --New row
45m219:00 --New row
55m220:00
66m221:00
May 10, 2016 at 7:02 pm
you would need to join to a table of hours and outer join the two...
SELECT Hrs.[TheHour]
,Movie.[StartTime]
FROM Hrs LEFT JOIN Movie ON Hrs.TheHour = Movie.StartTime
May 10, 2016 at 7:27 pm
To quote Ned Flanders from the Simpsons, "As melon scratchers go, this one's a real honeydoodle."
There's a better solution out there and I have a few ideas how to speed up what I put together but here goes.
USE tempdb
GO
-- sample data (I need a perm table; my solution uses an inline table valued function)
IF OBJECT_ID('tempdb.dbo.MovieShows') IS NOT NULL DROP TABLE dbo.MovieShows
GO
Create table dbo.MovieShows(Id int, Movieid varchar(20), Showtime time);
insert into dbo.MovieShows
values (11,'m1','13:00'),(23,'m2','14:00'),(34,'m1','15:00'),(45,'m2','16:00'),
(55,'m2','20:00'),(64,'m1','16:00'),(66,'m2','21:00'),(81,'m1','20:00');
GO
-- the function
CREATE FUNCTION dbo.itvfMovieShows (@moveid char(2))
RETURNS TABLE AS RETURN
WITH
E1 AS (SELECT N FROM (VALUES (1),(1),(1),(1),(1))t(N)), -- only 55 values needed (5*5=25)
iTally AS (SELECT N = ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1 FROM E1 a, E1 b),
boundary AS
(
SELECT mn = MIN(Showtime), mx = MAX(Showtime)
FROM dbo.MovieShows WHERE Movieid = @moveid
),
cal AS
(
SELECT hr = DATEADD(HOUR, N, CAST(mn AS Time)), *
FROM boundary
CROSS JOIN iTally
WHERE N <= (DATEDIFF(HOUR,mn,mx))
)
SELECT id = MAX(ID) OVER (ORDER BY N), Movieid = @moveid, Showtime = ISNULL(showtime, hr)
FROM cal c
LEFT JOIN dbo.MovieShows m
ON c.hr = m.Showtime AND m.Movieid = @moveid;
GO
--The solution
WITH ids AS (SELECT DISTINCT mid = movieid FROM dbo.MovieShows)
SELECT id, Movieid, Showtime
FROM ids
CROSS APPLY dbo.itvfMovieShows(mid);
UPDATE: I tested this a bit and found that, if you add the plk/clustered index below my solution performs pretty well.
Create table dbo.MovieShows
(
Id int, Movieid varchar(20), Showtime time,
CONSTRAINT pk_MovieShows PRIMARY KEY(Movieid, Id) -- pk/cluster index for performance
);
-- Itzik Ben-Gan 2001
May 11, 2016 at 7:13 am
Thank you. The solution worked and gave the desired results.
May 11, 2016 at 7:36 am
No problem.
-- Itzik Ben-Gan 2001
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply