• Replies 4 replies
  • Subscribers 337 subscribers
  • Views 23 views
  • Users 0 members are here
Related

Previous and Next Perfromance

Cathy Smith
$organization

I am trying to create an SSRS report that included in the report it shows the previous and next performances for each account based on a list of account numbers.

I was using a cursor to pull this data, but it has to process so much data it takes forever and bogs down the system.

Is anyone currently pulling this data in a usable fashion or aware of a place it is stored in Tessitura?  I was able to find it in a view, but since we are using local single ticket tables that data is not populated.

Thanks in advance for any advice or assistance you can give!

Cathy Stamm

  • $organization

    Cursors are terribly slow, I'd never use them for a report, either. For what you describe, in a report, I'd do something like:

    select c.customer_no,
        nx.perf_code "next_perf",
        ls.perf_code "last_perf"
    from t_customer c
    left join
    (
        select distinct th.customer_no,
            th.perf_code,
            th.perf_dt
        from lt_tkt_hist_gt th -- this is our custom ticket history table, fyi
        join (
            select customer_no,
                max(perf_dt) "perf_Dt"
            from lt_tkt_hist_gt th2
            where perf_dt >= getdate()
            group by customer_no
        ) x on x.customer_no = th.customer_no
            and x.perf_dt = th.perf_Dt
    ) nx on nx.customer_no = c.customer_no
    left join
    (
        select distinct th.customer_no,
            th.perf_code,
            th.perf_dt
        from lt_tkt_hist_gt th
        join (
            select customer_no,
                max(perf_dt) "perf_Dt"
            from lt_tkt_hist_gt th2
            where perf_dt < getdate()
            group by customer_no
        ) x on x.customer_no = th.customer_no
            and x.perf_dt = th.perf_Dt
    ) ls on ls.customer_no = c.customer_no
    where c.customer_no in [select your customers from a list or whatever...]

  • $organization
    Clean false false false EN-US X-NONE X-NONE

    Thank you Chris!  This worked perfectly and is exactly what I was looking for!

     

    From: Chris Jensen [mailto:bounce-chrisjensen8841@tessituranetwork.com]
    Sent: Tuesday, April 27, 2010 2:57 PM
    To: Cathy Stamm
    Subject: Re: [Tessitura Technical Forum] Previous and Next Perfromance

     

    Cursors are terribly slow, I'd never use them for a report, either. For what you describe, in a report, I'd do something like:

    select c.customer_no,
        nx.perf_code "next_perf",
        ls.perf_code "last_perf"
    from t_customer c
    left join
    (
        select distinct th.customer_no,
            th.perf_code,
            th.perf_dt
        from lt_tkt_hist_gt th -- this is our custom ticket history table, fyi
        join (
            select customer_no,
                max(perf_dt) "perf_Dt"
            from lt_tkt_hist_gt th2
            where perf_dt >= getdate()
            group by customer_no
        ) x on x.customer_no = th.customer_no
            and x.perf_dt = th.perf_Dt
    ) nx on nx.customer_no = c.customer_no
    left join
    (
        select distinct th.customer_no,
            th.perf_code,
            th.perf_dt
        from lt_tkt_hist_gt th
        join (
            select customer_no,
                max(perf_dt) "perf_Dt"
            from lt_tkt_hist_gt th2
            where perf_dt < getdate()
            group by customer_no
        ) x on x.customer_no = th.customer_no
            and x.perf_dt = th.perf_Dt
    ) ls on ls.customer_no = c.customer_no
    where c.customer_no in [select your customers from a list or whatever...]

    From: Cathy Stamm <bounce-cathystamm5548@tessituranetwork.com>
    Sent: 4/27/2010 11:50:40 AM

    I am trying to create an SSRS report that included in the report it shows the previous and next performances for each account based on a list of account numbers.

    I was using a cursor to pull this data, but it has to process so much data it takes forever and bogs down the system.

    Is anyone currently pulling this data in a usable fashion or aware of a place it is stored in Tessitura?  I was able to find it in a view, but since we are using local single ticket tables that data is not populated.

    Thanks in advance for any advice or assistance you can give!

    Cathy Stamm




    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!

    P Please consider the environment before printing this email.

  • $organization in reply to Cathy Smith

    Unknown said:
    Clean false false false EN-US X-NONE X-NONE
    Thank you Chris!  This worked perfectly and is exactly what I was looking for!

    You're welcome! Glad the code was helpful.

     

  • $organization in reply to Chris Jensen

    An error was pointed out in the above code, i.e. at line 13 it should be min(perf_dt) rather than max(), but I bet you caught it.

    Type in haste, repent at leisure! :-)