purchased a movie earlier than Gladiator date SQL

Question

I needed guidance and help with a question I have never done in SQL SERVER.

I have this data:

Cust_Nr     Date        FilmName
157649306   20150430    Inside Llewyn Davis 
158470722   20150504    Nick Cave: 20,000 Days On Earth
158467945   20150504    Out Of The Furnace 
158470531   20150504    FilmA
157649306   20150510    Gladiator
158470722   20150515    Gladiator

The customer number :158470722 has bought two movies,1):Inside Llewyn Davis and 2) Gladiator on different dates. What I am trying to do in SQL SERVER is to this:

 Cust_Nr    Date        FilmName                  Before Gladiator Purchase
    157649306   20150430    Inside Llewyn Davis              1
    158470722   20150504    Nick Cave: 20,000 Days On Earth  1
    158467945   20150504    Out Of The Furnace               0
    158470531   20150504    FilmA                            0
    157649306   20150510    Gladiator                        0
    158470722   20150515    Gladiator                        0

If we look at the dates for the customer nr:157649306, he purchased a movie on a date ,20150430, and purchased Gladiator on the 20150510. How can I create a column like the one above to show if a customer has purchased a movie on a date earlier than the gladiator date?

Answer 1

You need agregation and details in the same query, so use a Windowed Aggregate Function:

case 
   when Date < min(case when FilmName = 'Gladiator' then Date end)
               over (partition by Cust_Nr)
   then 1
   else 0 
end

Answer 2

You could join the table with a query on the gladiator movie:

SELECT    t.*,
          CASE WHEN g_date IS NULL OR t.[date] > g_date THEN 1 ELSE 0 END
FROM      mytable t
LEFT JOIN (SELECT [date] AS g_date, Cust_Nr
           FROM   mytable
           WHERE  FileName = 'Gladiator') g ON t.Cust_Nr = g.Cust_Nr

Answer 3

You can do this with conditional aggregation and window/analytic functionality:

SELECT *,CASE WHEN [Date] < MIN(CASE WHEN FilmName = 'Gladiator' 
                                     THEN [Date] 
                                END) OVER(PARTITION BY Cust_Nr)  
              THEN 1 
              ELSE 0 
         END AS Before_Gladiator
FROM Table1

Demo: SQL Fiddle