CASE statement in a View
Posted on 2011-05-06
I have a rather extensive view, that joins on multiple tables. This is an old view, and cannot be significantly altered, because it needs to be in production at all times, and powers our ASPX-powered CMS UI.
I have a table that contains thousands and thousands of different types of entertainment titles, from movies, video games, tv shows, and music videos.
I have just implemented a pair of Release Dates, one "Theatrical" and one "Home Video".
These mirror our yet-to-be-released CMS effectually. The issue is, each 'clip', identified by a unique id, is a fraction of a greater 'title', and in our new system, which MUST be replicated, the clips contain only a "Theatrical" OR "Home Video" release date.
However (and here's the reason everything must remain the same in the database), XML feeds are generated from a view that only recognizes the first ReleaseDate listed, because it's referencing only one or the other Release Date, whichever has the numerically higher ClipId. I need my XML to populate BOTH TheatricalReleaseDate and HomeVideoReleaseDate from all clips with the unique title ID, assuming the title ID exists on this clip (older clips are missing them) and that if the TheatrcialReleaseDate or HomeVideoReleaseDate does not exist, then keep the value NULL.
in pseudo SQL, I want a case statement that essentially does this:
case when c.TheatricalReleaseDate IS NULL Then
LOOK TO THE TitleID
FIND ALL CLIPS WITH THE SAME TitleID
ARE THERE ANY THEATRICALRELEASEDATES? IF YES: USE THAT ONE, IF NO: NULL
Of course, that's pseudo, and my question is how to render this logically in SQL MSSQL 2005
For example, the table may have these values:
|ClipID | TitleID | Name | TheatricalReleaseDate | HomeVideoReleaseDate|
|0001 | 5501 | Star Wars: A New Hope | 1977-05-25 | NULL |
|0002 | 5501 | Star Wars: A new Hope | NULL | 1985-12-25|
( I know Star Wars wasn't released on video on that date, it's an example ;) )
I want my XML to reference this Clip table, where the TitleId is used to leverage the view to populate both TheatricalReleaseDate and HomeVideoReleaseDate in the same line.
I'm not sure if all information presented is complete to answer this question, so some back-and-forth may be necessary.