Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

There are two tables involved in this challenge. The first table is MovieReleaseDates which will contain the names of movies along with their release dates.

TSQL Beginners Challenge 8 - Identify the release dates of movies on different cities

TSQL Beginners Challenge 8 - Identify the release dates of movies on different cities

May 24 2010 7:27AM by Niladri Biswas   

Introduction

Let us one again play with a date related problem. The challenge is to identify the various movies released in cities.

There are two tables involved in this challenge. The first table is MovieReleaseDates which will contain the names of movies along with their release dates.

Sample Data

Table: MovieReleaseDates

Movie1	    Movie2		Movie3		Movie4		Movie5
----------  ----------	----------	----------	----------
2010-01-20  2010-02-15 	2010-02-02 	2010-03-31 	2010-04-16 
2010-01-22  2010-02-16 	2010-02-04 	2010-04-05 	2010-04-18 

The second table will hold the details of releases that happened in different cities. Here is how the table looks like:

Table: MovieReleasedIn

City	  MovieNames ReleaseDate
--------- ---------- -----------
Bangalore Movie1	 2010-01-22 
Bangalore Movie2	 2010-02-15 
Bangalore Movie3	 2010-02-04 
Bangalore Movie4	 2010-04-05 
Bangalore Movie5	 2010-04-16     

The challenge is to find out which movies are released on which dates and in which cities.

Expected Results

City	  Sun Mon		   Tue Wed Thu			 Fri		   Sat		
--------- --- ------------ --- --- ------------- ------------- ---		
Bangalore NA  NA		   NA  NA  NA			 Movie1(01/22) NA	
Bangalore NA  Movie2(02/15)NA  NA  NA			 NA		       NA	
Bangalore NA  NA		   NA  NA  Movie3(02/04) NA		       NA	
Bangalore NA  Movie4(04/05)NA  NA  NA			 NA		       NA	
Bangalore NA  NA		   NA  NA  NA			 Movie5(04/16) NA 	

Rules

  1. The program should run in SQL SERVER 2005+.
  2. Output must be sorted in ASCENDING ORDER of City.
  3. The days when the movies are NOT AVAILABLE(NA) for the respected cities, should be marked with NA.
  4. Column names should respect the desired output shown.

Sample Script

Use the following script to generate the sample data.

DECLARE @tblMovieReleaseDates TABLE(
	Movie1 DATETIME,
	Movie2 DATETIME,
	Movie3 DATETIME,
	Movie4 DATETIME,
	Movie5 DATETIME
)
INSERT INTO @tblMovieReleaseDates 
SELECT '01/20/2010','02/15/2010','02/02/2010','03/31/2010','04/16/2010' 
UNION ALL 
SELECT '01/22/2010','02/16/2010','02/04/2010','04/05/2010','04/18/2010'
SELECT * FROM @tblMovieReleaseDates
GO

DECLARE @tblMovieReleasedIn TABLE(
	City VARCHAR(20), 
	MovieNames VARCHAR(20),
	ReleaseDate Datetime
)
INSERT INTO @tblMovieReleasedIn
SELECT 'Bangalore','Movie1','01/22/2010' UNION ALL
SELECT 'Bangalore','Movie2','02/15/2010' UNION ALL
SELECT 'Bangalore','Movie3','02/04/2010' UNION ALL
SELECT 'Bangalore','Movie4','04/05/2010' UNION ALL
SELECT 'Bangalore','Movie5','04/16/2010' 
SELECT * FROM  @tblMovieReleasedIn

Restrictions

  1. The solution should be a single query that starts with a "SELECT" or “;WITH”.

Notes

Tags:Puzzles, TSQL Beginners Challenge, TC, TSQL Beginners Challenge 8

Niladri Biswas
7 · 21% · 6720
TSQL Beginners Challenge 8 - Identify the release dates of movies on different cities , 5.0 out of 5 based on 1 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]