• 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

    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';

     



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

    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';

     



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