Case Statement?

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!

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!