how to Flag

Question

Hi everyone,

Movie table have the following attributes:
• Title
• DirectorId
• YearOfRelease

I want know how can I flag movies that are no longer available to view.
I do not want the movie removed from the table but rather that it is flagged as being unavailable.

Many thanks

Answer 1

Hi @Ambro duh
Since you don't want to add an extra column to the table,maybe you could have a try on CASE WHEN sentence,like this:

SELECT Title,DirectorId,YearOfRelease,  
       CASE WHEN YEAR(GETDATE())-YearOfRelease <= 2  --Modify the boolean expression here to suit your needs  
	        THEN 'Yes'  
			ELSE 'No'   
			END AS [If Available]  
FROM Movie_Table  

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

As Naomi says, you will need to add a column to hold the information on whether the movie is available or not.

A simple way is to just add a bit column:

   ALTER TABLE movies ADD available bit NOT NULL DEFAULT 1  

And then you need to implement a process to mark movies as unavailable:

   UPDATE movies  
   SET     available = 0  
   WHERE Titlle = [@](/users/na/?userId=17da8bdc-bffd-0003-0000-000000000000)  
        AND YearOfRelease = @year  

I'm here assuming that Title and YearOfRelease is your primary key.

In a system I worked, we did not use bit columns, but rather we had a datetime2(3) column called deregdate, so we could track when a row was inactivated, but this is not always needed.

Answer 3

Your question is unclear. Are you asking of adding an extra column to the table (say, bit type) with the notAvailableForView name? Or do you want to select rows and certain criteria will tell you if the movie is avaiable or not?