how to Flag

Ambro duh 21 Reputation points

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

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
11,601 questions
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,459 questions
0 comments No comments
{count} votes

Accepted answer
  1. LiHong-MSFT 10,031 Reputation points

    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,

    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.

    0 comments No comments

2 additional answers

Sort by: Most helpful
  1. Naomi 7,361 Reputation points

    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?

  2. Erland Sommarskog 93,056 Reputation points

    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.

    0 comments No comments