• Replies 11 replies
  • Subscribers 338 subscribers
  • Views 79 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'));

 

  • $organization

    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?

  • $organization in reply to David Frederick

    Geez, the forum is being sluggish in sending out emails today -- this thread seems all out of order.

    What a great PIVOT example, David -- thanks for that! Definitely need to add that to my repertoire.

  • $organization

    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!

  • $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
  • $organization

    If I can’t take the scenic route, I’m not going to go  J

     

    I’ll give it a go.

     

    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!

  • $organization

    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
                    FROM dbo.TX_PERF_SEAT ps
                    WHERE ps.seat_status = 4 -- Held
                      AND ps.perf_no = p.perf_no ) as h
      OUTER APPLY ( SELECT COUNT(*) as available
                    FROM dbo.TX_PERF_SEAT ps
                    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
                    FROM dbo.TX_PERF_SEAT ps
                    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!


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

    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!

  • $organization

    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

           p.perf_no,

           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

    Available

    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!

  • $organization

    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

                    FROM dbo.TX_PERF_SEAT ps

                    WHERE ps.seat_status = 4 -- Held

                      AND ps.perf_no = p.perf_no ) as h

      OUTER APPLY ( SELECT COUNT(*) as available

                    FROM dbo.TX_PERF_SEAT ps

                    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

                    FROM dbo.TX_PERF_SEAT ps

                    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!

     




    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!

  • $organization

    You could try something like this:

     

     

    SELECT T_PERF.perf_no,

    count(case when TR_SEAT_STATUS.description = 'Held' then 1

                  else 0

                  end) as Held,

     

    count(case when TR_SEAT_STATUS.description = 'Available' then 1

                  else 0

                  end) as Available

     

     

    From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Nick Reilingh
    Sent: Wednesday, July 06, 2016 3:25 PM
    To: Hoffmann, Tanya
    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

                    FROM dbo.TX_PERF_SEAT ps

                    WHERE ps.seat_status = 4 -- Held

                      AND ps.perf_no = p.perf_no ) as h

      OUTER APPLY ( SELECT COUNT(*) as available

                    FROM dbo.TX_PERF_SEAT ps

                    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

                    FROM dbo.TX_PERF_SEAT ps

                    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!

     




    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 e-mail message is intended only for the recipient(s) named above. This message may contain trade secrets, attorney-client communication, or other privileged and confidential information. Any review, re-transmission, dissemination, reproduction or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the Sender and delete the material from any computer.
  • $organization

    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

           p.perf_no,

           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

    Available

    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!




    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!