• Replies 4 replies
  • Subscribers 337 subscribers
  • Views 24 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

Parents
  • $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...]

Reply
  • $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...]

Children
No Data