• Replies 3 replies
  • Subscribers 962 subscribers
  • Views 70 views
  • Users 0 members are here
Related

Comp Code report

Claire Murray
$organization

Hi

I wondered if anyone has a report that gives information about the comps that have been issued on a particular performance or run using comp codes? We would be looking for detail including the name of each customer issued a comp and how many they were given.

Look forward to hearing if anyone has a way of reporting this information.

Parents
  • Former Member
    Former Member $organization

    We have a shared report that works nicely, I can't attach the sql script but I copied it here.

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

    USE

     

     

    [impresario]

    GO

    /****** Object: StoredProcedure [dbo].[lp_comp_list] Script Date: 04/28/2009 13:59:31 ******/

    SET

     

     

    ANSI_NULLS

    ON

    GO

    SET

     

     

    QUOTED_IDENTIFIER

    ON

    GO

     

     

    /*

    lp_whi_comp_list

    @perf_no=106

    */

     

    ALTER

     

     

    procedure [dbo].

    [lp_comp_list]

    @perf_no

     

    int

    as

    select

     

     

    c

     

    .customer_no

    ,

    max

     

     

    (l.esal1_desc) as esal1_desc

    ,

    max

     

     

    (l.esal2_desc) as esal2_desc

    ,

    max

     

     

    (c.lname) as lname

    ,

    s

     

    .comp_code as comp_code

    ,

    max

     

     

    (d.description) as comp_code_desc

    ,

    max

     

     

    (f.perf_code) as perf_code

    ,

    max

     

     

    (i.description) as perf_Desc

    ,

    max

     

     

    (f.perf_dt) as perf_dt

    ,

    count

     

     

    (sli_no) as tot_tkts

    ,

    max

     

     

    (y.description) as

    facility_desc

    from

     

     

    t_sub_lineitem s

    JOIN

     

     

    t_order o on s.order_no=o.

    order_no

    JOIN

     

     

    t_customer c on o.customer_no=c.

    customer_no

    JOIN

     

     

    tx_cust_sal l on c.customer_no=l.customer_no and l.default_ind =

    'Y'

    JOIN

     

     

    tr_price_type p on p.id=s.

    price_type

    JOIN

     

     

    tr_comp_code d on s.comp_code=d.

    id

    JOIN

     

     

    t_perf f on s.perf_no=f.

    perf_no

    JOIN

     

     

    t_inventory i on f.perf_no=i.

    inv_no

    JOIN

     

     

    t_facility y ON f.facility_no = y.

    facil_no

    where

     

     

    price_type in (select id from tr_price_type where price_type_category=4

    )

     

     

    and s.sli_status in (3,12

    )

     

     

    and s.perf_no=

    @perf_no

    group

     

     

    by f.perf_no,c.customer_no,s.

    comp_code

Reply
  • Former Member
    Former Member $organization

    We have a shared report that works nicely, I can't attach the sql script but I copied it here.

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

    USE

     

     

    [impresario]

    GO

    /****** Object: StoredProcedure [dbo].[lp_comp_list] Script Date: 04/28/2009 13:59:31 ******/

    SET

     

     

    ANSI_NULLS

    ON

    GO

    SET

     

     

    QUOTED_IDENTIFIER

    ON

    GO

     

     

    /*

    lp_whi_comp_list

    @perf_no=106

    */

     

    ALTER

     

     

    procedure [dbo].

    [lp_comp_list]

    @perf_no

     

    int

    as

    select

     

     

    c

     

    .customer_no

    ,

    max

     

     

    (l.esal1_desc) as esal1_desc

    ,

    max

     

     

    (l.esal2_desc) as esal2_desc

    ,

    max

     

     

    (c.lname) as lname

    ,

    s

     

    .comp_code as comp_code

    ,

    max

     

     

    (d.description) as comp_code_desc

    ,

    max

     

     

    (f.perf_code) as perf_code

    ,

    max

     

     

    (i.description) as perf_Desc

    ,

    max

     

     

    (f.perf_dt) as perf_dt

    ,

    count

     

     

    (sli_no) as tot_tkts

    ,

    max

     

     

    (y.description) as

    facility_desc

    from

     

     

    t_sub_lineitem s

    JOIN

     

     

    t_order o on s.order_no=o.

    order_no

    JOIN

     

     

    t_customer c on o.customer_no=c.

    customer_no

    JOIN

     

     

    tx_cust_sal l on c.customer_no=l.customer_no and l.default_ind =

    'Y'

    JOIN

     

     

    tr_price_type p on p.id=s.

    price_type

    JOIN

     

     

    tr_comp_code d on s.comp_code=d.

    id

    JOIN

     

     

    t_perf f on s.perf_no=f.

    perf_no

    JOIN

     

     

    t_inventory i on f.perf_no=i.

    inv_no

    JOIN

     

     

    t_facility y ON f.facility_no = y.

    facil_no

    where

     

     

    price_type in (select id from tr_price_type where price_type_category=4

    )

     

     

    and s.sli_status in (3,12

    )

     

     

    and s.perf_no=

    @perf_no

    group

     

     

    by f.perf_no,c.customer_no,s.

    comp_code

Children