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
HAVING (((TR_SEAT_STATUS.description)='Held'));
SELECT T_PERF.perf_no, Count(TX_PERF_SEAT.seat_num) AS Available
HAVING (((TR_SEAT_STATUS.description)='Available'));
ESP = standing room or empty seat passes.
I’m putting this one in the book.
From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Nick Reilingh Sent: Wednesday, July 06, 2016 3:19 PM To: Dot Krebs Subject: Re: [Tessitura Technical Forum] Case Statement?
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
WHERE ps.seat_status = 4 -- Held
AND ps.perf_no = p.perf_no ) as h
OUTER APPLY ( SELECT COUNT(*) as available
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
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!
On Jul 6, 2016, at 2:51 PM, Dot Krebs <bounce-dotkrebs9366@tessituranetwork.com> wrote: 12.5 baby… I only wanted to count the “reserved” seats in the house and not the ESP seats, so I separated them using zone groups. From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Nick Reilingh Sent: Wednesday, July 06, 2016 1:54 PM To: Dot Krebs Subject: Re: [Tessitura Technical Forum] Case Statement? This looks exactly like something I could use -- and would like to build. What's the T_ZONE.zonegroup = 3 condition for? Are you on version 11? From: Dot Krebs <bounce-dotkrebs9366@tessituranetwork.com> Sent: 7/6/2016 1:01:25 PM 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')); This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you! This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!
On Jul 6, 2016, at 2:51 PM, Dot Krebs <bounce-dotkrebs9366@tessituranetwork.com> wrote:
12.5 baby…
I only wanted to count the “reserved” seats in the house and not the ESP seats, so I separated them using zone groups.
From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Nick Reilingh Sent: Wednesday, July 06, 2016 1:54 PM To: Dot Krebs Subject: Re: [Tessitura Technical Forum] Case Statement?
This looks exactly like something I could use -- and would like to build. What's the T_ZONE.zonegroup = 3 condition for? Are you on version 11?
From: Dot Krebs <bounce-dotkrebs9366@tessituranetwork.com> Sent: 7/6/2016 1:01:25 PM
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)
This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!