• Replies 5 replies
  • Subscribers 338 subscribers
  • Views 161 views
  • Users 0 members are here
Related

Querying Unsold Seats

Sarah Herrera
$organization

Hello,


We are conducting an analysis on sales trends for a large hall scaling project. As part of this analysis, we are interested in also including all of the unsold seat locations. I am wondering if someone has queried unsold seats from the database before, and what were the best tables to approach this. I am leaning towards using the seat status, but wondering if that's the best way to approach it. 

Thank you!

Sarah

Parents
  • $organization

    If you're going to do analysis you will need more than just available seat locations I would think. You will probably want to consider killed, held,and the constant house aggregates too. If so, this will help group by perf in a prod season. It is the engine I use in reports. Note the variable in the where clause and double check you ids for the seat statuses. 

    select g.prod_season_no, i.description as prod_desc, a.perf_no, g.perf_dt,
    sum(case
    when a.seat_status in(1,4) then 1 -- 0 = Available; 1 = Held Breakable; 4 = Held
    else 0
    end
    ) as 'held',
    sum(case
    when a.seat_status in(0,18) then 1 -- 0 = Available; 1 = Held Breakable; 4 = Held
    else 0
    end
    ) as 'available',
    SUM(case
    when ISNULL(e.hc_no,0) = 120 then 0 --K holds Kills are not counted toward the Constant House
    when b.is_seat = 1 then 1
    else 0
    end
    ) as 'constanthouse',
    SUM(case
    when isnull(e.hc_no,0) = 120 then 1
    else 0
    end
    ) as 'killed'
    from TX_PERF_SEAT a
    join T_SEAT b on a.seat_no = b.seat_no
    join TR_SEAT_STATUS c on a.seat_status = c.id
    left join TX_PERF_HC d on a.perf_no = d.perf_no and a.seat_no = d.seat_no and d.priority = 1
    left join T_HC e on d.hc_no = e.hc_no
    join T_PERF g on a.perf_no = g.perf_no
    join T_INVENTORY i on g.prod_season_no = i.inv_no
    where b.is_seat = 1
    and g.prod_season_no = @prod_season
    group by g.prod_season_no, i.description, a.perf_no, g.perf_dt

Reply
  • $organization

    If you're going to do analysis you will need more than just available seat locations I would think. You will probably want to consider killed, held,and the constant house aggregates too. If so, this will help group by perf in a prod season. It is the engine I use in reports. Note the variable in the where clause and double check you ids for the seat statuses. 

    select g.prod_season_no, i.description as prod_desc, a.perf_no, g.perf_dt,
    sum(case
    when a.seat_status in(1,4) then 1 -- 0 = Available; 1 = Held Breakable; 4 = Held
    else 0
    end
    ) as 'held',
    sum(case
    when a.seat_status in(0,18) then 1 -- 0 = Available; 1 = Held Breakable; 4 = Held
    else 0
    end
    ) as 'available',
    SUM(case
    when ISNULL(e.hc_no,0) = 120 then 0 --K holds Kills are not counted toward the Constant House
    when b.is_seat = 1 then 1
    else 0
    end
    ) as 'constanthouse',
    SUM(case
    when isnull(e.hc_no,0) = 120 then 1
    else 0
    end
    ) as 'killed'
    from TX_PERF_SEAT a
    join T_SEAT b on a.seat_no = b.seat_no
    join TR_SEAT_STATUS c on a.seat_status = c.id
    left join TX_PERF_HC d on a.perf_no = d.perf_no and a.seat_no = d.seat_no and d.priority = 1
    left join T_HC e on d.hc_no = e.hc_no
    join T_PERF g on a.perf_no = g.perf_no
    join T_INVENTORY i on g.prod_season_no = i.inv_no
    where b.is_seat = 1
    and g.prod_season_no = @prod_season
    group by g.prod_season_no, i.description, a.perf_no, g.perf_dt

Children
No Data