locked
row by row calulations for advance seat availability RRS feed

  • Question

  • Hello,

    I need some advice on a query. From the below screenshot the data that I currently have is the Route,Date,RemainingSeats columns. The query I need, needs to populate the 3&7 day view availability columns.

    Basically the idea is the query needs to go row by row checking for availability in the future and marking yes or no if the condition is met...in this case 3 & 7 days in advance from the current row date

    e.g. 1/2/2017 there is availability for the next 3 days (10 each day) so we put yes. However on 1/3/2017 we put no because there is no availability on 1/5/2017 (the 3rd day in advance)

    I hope this makes sense and any advice on where to start would be great

    Tuesday, September 12, 2017 4:13 PM

Answers

  • What version of SQL Server are you using?

    Try:

    -- SS 2012 or g
    select
        Route, Date, RamainingSeats,
        case
        when min(RemainingSeats) over(
        partition by Route
        order by Date
        rows between current row and 2 following
        ) > 0 then 'Yes'
        else 'No'
        end as  3_day_view_availability,
        case
        when min(RemainingSeats) over(
        partition by Route 
        order by Date 
        rows between current row and 6 following
        ) > 0 then 'Yes'
        else 'No' 
        end as 7_day_view_availability
    from
        T
    order by
        Route, Date;


    AMB

    Some guidelines for posting questions...

    AYÚDANOS A AYUDARTE, guía básica de consejos para formular preguntas

    • Edited by HunchbackMVP Tuesday, September 12, 2017 4:51 PM
    • Marked as answer by kiwiNspain Wednesday, September 13, 2017 7:25 AM
    Tuesday, September 12, 2017 4:49 PM
  • Hi,

    Please check following query, see if it works for you:

    create table #test 
    (
    Route varchar(10),
    Date date,
    RemainingSeats int
    )
    
    insert into #test values
    ('NY-LAX','2017-1-1',10),
    ('NY-LAX','2017-1-2',10),
    ('NY-LAX','2017-1-3',10),
    ('NY-LAX','2017-1-4',10),
    ('NY-LAX','2017-1-5',0),
    ('NY-LAX','2017-1-6',10),
    ('NY-LAX','2017-1-7',10),
    ('NY-LAX','2017-1-8',10),
    ('NY-LAX','2017-1-9',10),
    ('NY-LAX','2017-1-10',10),
    ('NY-LAX','2017-1-11',10),
    ('NY-LAX','2017-1-12',10),
    ('NY-LAX','2017-1-13',10),
    ('NY-LAX','2017-1-14',0)
    
    ;with cte as(
    select *,SUM(Case when RemainingSeats<>10 then 1 else 0 end) Over(Order By date desc) grpn
    from #test
    )
    
    select Route,Date,RemainingSeats,case when lead(RemainingSeats,2)Over(Partition By Route,grpn Order By Date)=10 then 'Yes' else 'No' end as [3_day_view_availability]
    ,case when lead(RemainingSeats,6)Over(Partition By Route,grpn Order By Date)=10 then 'Yes' else 'No' end as [7_day_view_availability]
    from cte
    order by Route,Date
    

    Thanks,
    Xi Jin.


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Proposed as answer by Xi Jin Wednesday, September 13, 2017 7:11 AM
    • Marked as answer by kiwiNspain Wednesday, September 13, 2017 7:24 AM
    Wednesday, September 13, 2017 6:42 AM
  • Hi,

    Yes, you can achieve this by using Row_Number() function. Check this:

    ;with cte as(
    select *,SUM(Case when RemainingSeats<>10 then 1 else 0 end) Over(Order By date desc) grpn
    from #test
    )
    
    select *,ROW_NUMBER()Over(Partition By Route,grpn Order By Date desc)-1 as DaysAvail
    from cte
    order by Route,Date

    Thanks,
    Xi Jin.


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by kiwiNspain Wednesday, September 13, 2017 7:24 AM
    Wednesday, September 13, 2017 7:11 AM

All replies

  • What version of SQL Server are you using?

    Try:

    -- SS 2012 or g
    select
        Route, Date, RamainingSeats,
        case
        when min(RemainingSeats) over(
        partition by Route
        order by Date
        rows between current row and 2 following
        ) > 0 then 'Yes'
        else 'No'
        end as  3_day_view_availability,
        case
        when min(RemainingSeats) over(
        partition by Route 
        order by Date 
        rows between current row and 6 following
        ) > 0 then 'Yes'
        else 'No' 
        end as 7_day_view_availability
    from
        T
    order by
        Route, Date;


    AMB

    Some guidelines for posting questions...

    AYÚDANOS A AYUDARTE, guía básica de consejos para formular preguntas

    • Edited by HunchbackMVP Tuesday, September 12, 2017 4:51 PM
    • Marked as answer by kiwiNspain Wednesday, September 13, 2017 7:25 AM
    Tuesday, September 12, 2017 4:49 PM
  • Hi,

    Please check following query, see if it works for you:

    create table #test 
    (
    Route varchar(10),
    Date date,
    RemainingSeats int
    )
    
    insert into #test values
    ('NY-LAX','2017-1-1',10),
    ('NY-LAX','2017-1-2',10),
    ('NY-LAX','2017-1-3',10),
    ('NY-LAX','2017-1-4',10),
    ('NY-LAX','2017-1-5',0),
    ('NY-LAX','2017-1-6',10),
    ('NY-LAX','2017-1-7',10),
    ('NY-LAX','2017-1-8',10),
    ('NY-LAX','2017-1-9',10),
    ('NY-LAX','2017-1-10',10),
    ('NY-LAX','2017-1-11',10),
    ('NY-LAX','2017-1-12',10),
    ('NY-LAX','2017-1-13',10),
    ('NY-LAX','2017-1-14',0)
    
    ;with cte as(
    select *,SUM(Case when RemainingSeats<>10 then 1 else 0 end) Over(Order By date desc) grpn
    from #test
    )
    
    select Route,Date,RemainingSeats,case when lead(RemainingSeats,2)Over(Partition By Route,grpn Order By Date)=10 then 'Yes' else 'No' end as [3_day_view_availability]
    ,case when lead(RemainingSeats,6)Over(Partition By Route,grpn Order By Date)=10 then 'Yes' else 'No' end as [7_day_view_availability]
    from cte
    order by Route,Date
    

    Thanks,
    Xi Jin.


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Proposed as answer by Xi Jin Wednesday, September 13, 2017 7:11 AM
    • Marked as answer by kiwiNspain Wednesday, September 13, 2017 7:24 AM
    Wednesday, September 13, 2017 6:42 AM
  • hi,

    thanks for the replies. both seem to give me what i want... i will apply it to our data and see the performance.

    one extra question, if i wanted a column with the future days with availability like below... would it be possible to apply something like the above queries?

    Wednesday, September 13, 2017 7:05 AM
  • Hi,

    Yes, you can achieve this by using Row_Number() function. Check this:

    ;with cte as(
    select *,SUM(Case when RemainingSeats<>10 then 1 else 0 end) Over(Order By date desc) grpn
    from #test
    )
    
    select *,ROW_NUMBER()Over(Partition By Route,grpn Order By Date desc)-1 as DaysAvail
    from cte
    order by Route,Date

    Thanks,
    Xi Jin.


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by kiwiNspain Wednesday, September 13, 2017 7:24 AM
    Wednesday, September 13, 2017 7:11 AM