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'));
Fancy.
Any suggestions on getting the held and the available on the same line?
From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Nick Reilingh Sent: Wednesday, July 06, 2016 2:49 PM To: Dot Krebs Subject: Re: [Tessitura Technical Forum] Case Statement?
Oh, nevermind -- it's just from TR_ZONE_GROUP.
In starting to look over your code, it does strike me immediately that this is the strangest join syntax I've ever seen. Consider something like this as being far more readable/idiomatic:
SELECT p.perf_no, Count(ps.seat_num) AS Ticketed
FROM dbo.T_PERF p
JOIN dbo.T_INVENTORY i
ON i.inv_no = p.perf_no
JOIN dbo.TX_PERF_SEAT ps
ON ps.perf_no = p.perf_no AND ps.zmap_no = p.zmap_no
JOIN dbo.TR_SEAT_STATUS ss
ON ss.id = ps.seat_status
JOIN dbo.T_ZONE z
ON z.zmap_no = ps.zmap_no AND z.zone_no = ps.zone_no
WHERE ps.seat_status <> 6
GROUP BY p.perf_no, ss.description, p.perf_dt
HAVING ss.description = 'Ticketed';
On Jul 6, 2016, at 1:57 PM, Nick Reilingh <bounce-nicholasreilingh4883@tessituranetwork.com> wrote: 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 1:57 PM, Nick Reilingh <bounce-nicholasreilingh4883@tessituranetwork.com> wrote:
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!