none
Which Months in Current Year did NOT have delay data compare to last year?

    Question

  • Folks,

    this has to be simple using "NOT, IN and EXISTS" keywords...but don't know what is missing?

    #1.

    Select distinct(month(flight_dly_date) from flight_delay where year(flight_dly_date) = year(now) -1 -> gives me all the last year's months (1 through 12) in which we had delays....

    #2

    Select distinct(month(flight_dly_date) from flight_delay where year(flight_dly_date) = year(now)  -> gives me all the current year's months (1 through 9) in which we had delays....

    how to find current year's months (10,11,12) in which we don't have the delays because it's in future or let's say we decommissioned the aircraft for which we are tracking delays.

    In nut shell combine result of above and find missing months 10,11 and 12 of year 2013.

    Thx..

    Thursday, September 12, 2013 9:11 PM

Answers

  • select * from
    (
    select PreviousYear.MNTH as PrevMonth,CurrentYear.MNTH as CurrMonth from 
    (
    Select distinct(month(flight_dly_date)) as MNTH from flight_delay where year(flight_dly_date) = year(now) -1 
    )PreviousYear
    left outer join 
    (
    Select distinct(month(flight_dly_date)) as MNTH from flight_delay where year(flight_dly_date) = year(now)
    )CurrentYear
    on PreviousYear.MNTH=CurrentYear.MNTH
    )b
    where b.CurrMonth is null

    try above statement.

    I am not sure what language you are using

    but if you are using SQL Server, you should change NOW with GETDATE()


    Regards,

    Reza

    SQL Server MVP

    Blog:   http://rad.pasfu.com  Twitter:   LinkedIn:

    SQL Server Integration Services 2012 Tutorial Videos:     http://www.radacad.com/CoursePlan.aspx?course=1

    Thursday, September 12, 2013 9:20 PM