Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

CASE statement in a View

Posted on 2011-05-06
4
Medium Priority
?
364 Views
Last Modified: 2012-05-11
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.

Thanks.
0
Comment
Question by:Smittles
  • 2
4 Comments
 
LVL 41

Expert Comment

by:Sharath
ID: 35708477
Can you provide some sample data returned from your query and the expected result set.
0
 
LVL 4

Author Comment

by:Smittles
ID: 35708739
Current data returned is like this:

<title>
<titleName>Star Wars: A New Hope</titleName>
<titleId> 5501</titleId>
<theatricalReleaseDate>1977-05-05 </theatricalReleaseDate>
<homeVideoReleaseDate/> 
<clip>
  <clipId>0001 </clipId>
  <clipDescription>Saber training</clipDescription>
</clip>
<clip>
  <clipId>0002 </clipId>
  <clipDescription>Vader Dual</clipDescription>
</clip>
</title>

Open in new window

And what I want:
<title>
<titleName>Star Wars: A New Hope</titleName>
<titleId> 5501</titleId>
<theatricalReleaseDate>1977-05-05 </theatricalReleaseDate>
<homeVideoReleaseDate> 1985-12-25</homeVideoReleaseDate>
<clip>
  <clipId>0001 </clipId>
  <clipDescription>Saber training</clipDescription>
</clip>
<clip>
  <clipId>0002 </clipId>
  <clipDescription>Vader Dual</clipDescription>
</clip>
</title>

Open in new window


This happens because the view is only reading the theatrical and home video release dates from the first clip, 0001, where the theatrical is not null, and the home video is null.

SO, I want logic that says, if the home  video is null, look to the titleid and see if any clips that share a title id have the home video release id, populate the result with the top 1 date for that titleId.

0
 
LVL 18

Accepted Solution

by:
UnifiedIS earned 2000 total points
ID: 35708766
SELECT...

TheatricalReleaseDate = CASE WHEN c.TheatricalReleaseDate IS NOT NULL THEN C.TheatricalReleaseDate
WHEN MAXTHEA.MaxTheatrical IS NOT NULL Then MAXTHEA.MaxTheatrical
ELSE NULL
END

FROM...

LEFT OUTER JOIN (SELECT MAX(TheatricalReleaseDate) AS MaxTheatrical, TitleID FROM yourtable GROUP BY TitleID) MAXTHEA
ON MAXTHEA.TitleID = C.TitleID
0
 
LVL 4

Author Closing Comment

by:Smittles
ID: 35709067
Excellent, now all I need to do is format the date to be varchar (which I can do) and this absolutely takes care of the issue - and teaches me a valuable lesson about temp tables in a view!  Thanks much
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

571 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question