July 18, 2010 at 9:34 pm
Hi all,
I have a problem to write this query, anyone can give me any ideas ? 🙂
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 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
second table will hold the details of releases that happened in different cities.
Table: MovieReleasedIn
City MovieNames ReleaseDate
-------- ---------- -----------
UK Movie1 2010-01-22
UK Movie2 2010-02-15
UK Movie3 2010-02-04
UK Movie4 2010-04-05
UK Movie5 2010-04-16
The challenge is to find out which movies are released on which dates and in which cities.
Expected Output:
City Sun Mon Tue Wed Thu Fri Sat
--------- --- ------------ --- --- ---------- --- ------------- ---
UK NA NA NA NA NA Movie1(01/22) NA
UK NA Movie2(02/15) NA NA NA NA NA
UK NA NA NA NA Movie3(02/04) NA NA
UK NA Movie4(04/05) NA NA NA NA NA
UK NA NA NA NA NA Movie5(04/16) NA
Note:
1)The days when the movies are NOT AVAILABLE(NA) for the respected cities, should be marked with NA
2)The program has to be done by a single query and should begin either with a SELECT or WITH statement
Using follow script to generate table:
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 'UK','Movie1','01/22/2010' UNION ALL
SELECT 'UK','Movie2','02/15/2010' UNION ALL
SELECT 'UK','Movie3','02/04/2010' UNION ALL
SELECT 'UK','Movie4','04/05/2010' UNION ALL
SELECT 'UK','Movie5','04/16/2010'
SELECT * FROM @tblMovieReleasedIn
July 18, 2010 at 10:01 pm
Based on your requirements, this seems like homework?
If so - are you looking for the answer, or are you looking for ideas?
--------------------------
I long for a job where my databases dont have any pesky users accessing them 🙂
July 18, 2010 at 10:08 pm
Hi, this not a home work... I took from a blog... I'm looking for any ideas to do it 🙂
July 18, 2010 at 10:29 pm
ah i see..
well..
datename(dw, ReleaseDate) will get you the day of the week..
you could perhaps return the values then PIVOT?
Based on the expected results..
1.City Sun Mon Tue Wed Thu Fri Sat
2.--------- --- ------------ --- --- ------------- ------------- ---
3.Bangalore NA NA NA NA NA Movie1(01/22) NA
4.Bangalore NA Movie2(02/15)NA NA NA NA NA
5.Bangalore NA NA NA NA Movie3(02/04) NA NA
6.Bangalore NA Movie4(04/05)NA NA NA NA NA
7.Bangalore NA NA NA NA NA Movie5(04/16) NA
I'm having a hard time seeing what the @tblMovieReleaseDates table is for..
all the information in the expected results in @tblMovieReleasedIn table.
i may look at this more tomorrow.. pretty late here - but good luck with it.. if you come up with a solution, I'd be interested to see it
--------------------------
I long for a job where my databases dont have any pesky users accessing them 🙂
July 19, 2010 at 12:18 am
I'm able to get the day of the week, but how to pivot the data ?
July 19, 2010 at 12:56 am
I'm able to pivot the data by this query:
select City,Sun,Mon,Tue,Wed,Thu,Fri,Sat
from
(select City, MovieNames, ReleaseDate, substring(datename(dw,ReleaseDate),1,3) As DayName
from tblMovieReleasedIn) As a
pivot
( count (ReleaseDate)
for DayName in ([Sun],[Mon],[Tue],[Wed],[Thu],[Fri],[Sat]))
As b
order by City
Now my problem is how to replace those values, so I can get the expected output ?
July 19, 2010 at 3:26 am
Hi, this is the solution that i managed to come out
-----------------------------------------------------
select City,
Case When Sun = 0 Then 'NA' ELSE MovieNames + ' (' + substring(ReleaseDate1,6,5) + ')'
END Sun,
Case When Mon = 0 Then 'NA' ELSE MovieNames + ' (' + substring(ReleaseDate1,6,5) + ')'
END Mon,
Case When Tue = 0 Then 'NA' ELSE MovieNames + ' (' + substring(ReleaseDate1,6,5) + ')'
END Tue,
Case When Wed = 0 Then 'NA' ELSE MovieNames + ' (' + substring(ReleaseDate1,6,5) + ')'
END Wed,
Case When Thu = 0 Then 'NA' ELSE MovieNames + ' (' + substring(ReleaseDate1,6,5) + ')'
END Thu,
Case When Fri = 0 Then 'NA' ELSE MovieNames + ' (' + substring(ReleaseDate1,6,5) + ')'
END Fri,
Case When Sat = 0 Then 'NA' ELSE MovieNames + ' (' + substring(ReleaseDate1,6,5) + ')'
END Sat
from
(select City, MovieNames, ReleaseDate, substring(datename(dw,ReleaseDate),1,3) As DayName,
convert(varchar(25),ReleaseDate,111) as ReleaseDate1
from tblMovieReleasedIn) As a
pivot
( count (ReleaseDate)
for DayName in ([Sun],[Mon],[Tue],[Wed],[Thu],[Fri],[Sat]))
As b
order by City
---------------------------------------------------------------------------------
I'm now looking for other method which can gave me the most cost effective way ... Anyone can help 🙂
July 20, 2010 at 8:08 am
Hi at1155..
please execute the below script and look at the output..
later, add your latest query at the last and execute the whole script..
DECLARE @tblMovieReleasedIn TABLE(
City VARCHAR(20),
MovieNames VARCHAR(20),
ReleaseDate Datetime
)
INSERT INTO @tblMovieReleasedIn
SELECT 'Mumbai','Movie1','01/22/2010' UNION ALL
SELECT 'Mumbai','Movie2','02/15/2010' UNION ALL
SELECT 'Mumbai','Movie3','02/04/2010' UNION ALL
SELECT 'Mumbai','Movie4','04/05/2010' UNION ALL
SELECT 'Mumbai','Movie5','04/16/2010'
INSERT INTO @tblMovieReleasedIn
SELECT 'Hyderabad','Movie6','05/06/2010' UNION ALL
SELECT 'Hyderabad','Movie7','05/14/2010' UNION ALL
SELECT 'Hyderabad','Movie8','05/23/2010' UNION ALL
SELECT 'Hyderabad','Movie9','05/25/2010' UNION ALL
SELECT 'Hyderabad','Movie10','07/16/2010'
SELECT * FROM @tblMovieReleasedIn
Select city,Replace(Sun,0,'NA') Sun, Replace(Mon,0,'NA') Mon, Replace(Tue,0,'NA') Tue, Replace(Wed,0,'NA') Wed
, Replace(Thu,0,'NA') Thu, Replace(Fri,0,'NA') Fri, Replace(Sat,0,'NA') Sat
From
(
select city, Substring(datename(dw,ReleaseDate),1,3) d, MovieNames
from @tblMovieReleasedIn
) a
pivot
( count(MovieNames) for d in([Sun],[Mon],[Tue],[Wed],[Thu],[Fri],[Sat])
) y
My intention is to make you to identify what you are missing..
Hope you will understand..
[font="Comic Sans MS"]Praveen Goud[/font]
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply