• Replies 11 replies
  • Subscribers 338 subscribers
  • Views 81 views
  • Users 0 members are here
Related

Case Statement?

Dot Krebs (Past Member)
$organization

I am trying to create a single line result by performance number with a seat count.  I think it is a candidate for a case statement, but I can't seem to get it right.  What is the right / efficient way?

 

The result should look like:

 

Perf_no   Ticketed      Held       Available

1120             165               20           63

1121              173               5             60                

 

 

 

 This is the code that is able to return 3 separate results.

 

SELECT T_PERF.perf_no, Count(TX_PERF_SEAT.seat_num) AS Ticketed

FROM T_ZONE INNER JOIN (TR_SEAT_STATUS INNER JOIN ((T_PERF INNER JOIN T_INVENTORY ON T_PERF.perf_no = T_INVENTORY.inv_no) INNER JOIN TX_PERF_SEAT ON (T_PERF.perf_no = TX_PERF_SEAT.perf_no) AND (T_PERF.zmap_no = TX_PERF_SEAT.zmap_no)) ON TR_SEAT_STATUS.id = TX_PERF_SEAT.seat_status) ON (T_ZONE.zmap_no = TX_PERF_SEAT.zmap_no) AND (T_ZONE.zone_no = TX_PERF_SEAT.zone_no)

WHERE (((TX_PERF_SEAT.seat_status)<>6) AND ((T_ZONE.zone_group)=3))

GROUP BY T_PERF.perf_no, TR_SEAT_STATUS.description, T_PERF.perf_dt

HAVING (((TR_SEAT_STATUS.description)='Ticketed'));

 

SELECT T_PERF.perf_no, Count(TX_PERF_SEAT.seat_num) AS Held

FROM T_ZONE INNER JOIN (TR_SEAT_STATUS INNER JOIN ((T_PERF INNER JOIN T_INVENTORY ON T_PERF.perf_no = T_INVENTORY.inv_no) INNER JOIN TX_PERF_SEAT ON (T_PERF.perf_no = TX_PERF_SEAT.perf_no) AND (T_PERF.zmap_no = TX_PERF_SEAT.zmap_no)) ON TR_SEAT_STATUS.id = TX_PERF_SEAT.seat_status) ON (T_ZONE.zmap_no = TX_PERF_SEAT.zmap_no) AND (T_ZONE.zone_no = TX_PERF_SEAT.zone_no)

WHERE (((TX_PERF_SEAT.seat_status)<>6) AND ((T_ZONE.zone_group)=3))

GROUP BY T_PERF.perf_no, TR_SEAT_STATUS.description, T_PERF.perf_dt

HAVING (((TR_SEAT_STATUS.description)='Held'));

 

SELECT T_PERF.perf_no, Count(TX_PERF_SEAT.seat_num) AS Available

FROM T_ZONE INNER JOIN (TR_SEAT_STATUS INNER JOIN ((T_PERF INNER JOIN T_INVENTORY ON T_PERF.perf_no = T_INVENTORY.inv_no) INNER JOIN TX_PERF_SEAT ON (T_PERF.perf_no = TX_PERF_SEAT.perf_no) AND (T_PERF.zmap_no = TX_PERF_SEAT.zmap_no)) ON TR_SEAT_STATUS.id = TX_PERF_SEAT.seat_status) ON (T_ZONE.zmap_no = TX_PERF_SEAT.zmap_no) AND (T_ZONE.zone_no = TX_PERF_SEAT.zone_no)

WHERE (((TX_PERF_SEAT.seat_status)<>6) AND ((T_ZONE.zone_group)=3))

GROUP BY T_PERF.perf_no, TR_SEAT_STATUS.description, T_PERF.perf_dt

HAVING (((TR_SEAT_STATUS.description)='Available'));

 

Parents
  • $organization

    What are ESP seats?

    I just finished my version of this actually:
    SELECT  p.perf_no,
            p.perf_code,
            i.description AS perf_desc,
            r.reserved,
            h.held,
            a.available,
            c.capacity
    FROM dbo.T_PERF p
      LEFT JOIN dbo.T_INVENTORY i ON i.inv_no = p.perf_no
      OUTER APPLY ( SELECT COUNT(*) as reserved
                    FROM dbo.TX_PERF_SEAT ps
                    WHERE ps.seat_status IN (7, 8, 13) -- Reserved Paid/Unpaid, Ticketed
                      AND ps.perf_no = p.perf_no ) as r
      OUTER APPLY ( SELECT COUNT(*) as held
                    FROM dbo.TX_PERF_SEAT ps
                    WHERE ps.seat_status = 4 -- Held
                      AND ps.perf_no = p.perf_no ) as h
      OUTER APPLY ( SELECT COUNT(*) as available
                    FROM dbo.TX_PERF_SEAT ps
                    WHERE ps.seat_status IN (0, 3, 18) -- Available, Locked, Donated for Resale
                      AND ps.perf_no = p.perf_no ) as a
      OUTER APPLY ( SELECT COUNT(*) as capacity
                    FROM dbo.TX_PERF_SEAT ps
                    WHERE ps.seat_status <> 6 -- Blacked Out
                      AND ps.perf_no = p.perf_no ) as c
    I think if you want to add back in the zone group filter, you would do that within each outer apply. There’s definitely more than one way to do it, but I find this way to be relatively cleanly organized and easy to see what’s happening. If you wanted to change the status categorization of donated for resale, for example, you could easily move it.
    Hope this helps!


    [edited by: Nick Reilingh at 3:29 PM (GMT -6) on 6 Jul 2016]
Reply
  • $organization

    What are ESP seats?

    I just finished my version of this actually:
    SELECT  p.perf_no,
            p.perf_code,
            i.description AS perf_desc,
            r.reserved,
            h.held,
            a.available,
            c.capacity
    FROM dbo.T_PERF p
      LEFT JOIN dbo.T_INVENTORY i ON i.inv_no = p.perf_no
      OUTER APPLY ( SELECT COUNT(*) as reserved
                    FROM dbo.TX_PERF_SEAT ps
                    WHERE ps.seat_status IN (7, 8, 13) -- Reserved Paid/Unpaid, Ticketed
                      AND ps.perf_no = p.perf_no ) as r
      OUTER APPLY ( SELECT COUNT(*) as held
                    FROM dbo.TX_PERF_SEAT ps
                    WHERE ps.seat_status = 4 -- Held
                      AND ps.perf_no = p.perf_no ) as h
      OUTER APPLY ( SELECT COUNT(*) as available
                    FROM dbo.TX_PERF_SEAT ps
                    WHERE ps.seat_status IN (0, 3, 18) -- Available, Locked, Donated for Resale
                      AND ps.perf_no = p.perf_no ) as a
      OUTER APPLY ( SELECT COUNT(*) as capacity
                    FROM dbo.TX_PERF_SEAT ps
                    WHERE ps.seat_status <> 6 -- Blacked Out
                      AND ps.perf_no = p.perf_no ) as c
    I think if you want to add back in the zone group filter, you would do that within each outer apply. There’s definitely more than one way to do it, but I find this way to be relatively cleanly organized and easy to see what’s happening. If you wanted to change the status categorization of donated for resale, for example, you could easily move it.
    Hope this helps!


    [edited by: Nick Reilingh at 3:29 PM (GMT -6) on 6 Jul 2016]
Children
No Data