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'));
Oh wow. Just wow.
Pivot… in the book.
From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of David Frederick Sent: Wednesday, July 06, 2016 3:39 PM To: Dot Krebs Subject: RE: [Tessitura Technical Forum] Case Statement?
Hi Dot,
This is potentially a good use for PIVOT. I took Nick’s refactored code and added the pivot. It appears to be accurate, although you’ll want to confirm that as I didn’t spend a lot of time on it:
SELECT
p.perf_no,
Ticketed,
Held,
Available
FROM (SELECT
ss.description,
ps.seat_no
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
AND p.perf_no IN (12631, 12632, 12633)) a
PIVOT
(COUNT(seat_no) FOR a.description IN ([Ticketed], [Held], [Available])) AS p
The results are as follows:
perf_no
Ticketed
Held
12633
1321
245
1322
12631
916
305
1751
12632
1029
304
1596
Note that I limited it to just three performances in our database and you’d need to add your zone group criteria in. I hope that helps!
_______________________________________________________ David Frederick Database Applications Analyst Segerstrom Center for the Arts 600 Town Center Drive, Costa Mesa, CA 92626 T (714) 556-2122 x 4067 E DFrederick@SCFTA.org
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!