• Replies 6 replies
  • Subscribers 337 subscribers
  • Views 87 views
  • Users 0 members are here
Related

Reporting on Research Notes and the Full Bio Report

A. Rey Pamatmat
$organization

Hi everyone,

 

I’m looking for a bit of SQL that will join the separate lines in TX_CUST_NOTES based on their serial order. I figured that, before making something up, there must be someone out there whose already put it together. Am I wrong?

 

Alternatively, I was poking around trying to find the stored procedure for the component of the Full Bio report that reports on Research Notes. Unfortunately, there doesn’t appear to be documentation on that report, so I haven’t had much luck. If you know the SP for that sub-report, I’m sure I could find the SQL there, too.

 

Thanks in advance,

 

Rey

 

--

A. Rey Pamatmat

Tessitura Manager

The Public Theater

425 Lafayette Street

New York, NY 10003

(212) 539-8739

rpamatmat@publictheater.org

 

Parents
  • $organization
    Here's for a report we have:

    CREATE procedure [dbo].[lrp_dev_notes]

    ( @start_dt datetime

    )

    as

    declare @cust_notes table

    ( cust_notes_no int,

    customer_no int,

    esal1_desc varchar(55),

    description varchar(30),

    created_by char(8),

    create_dt datetime,

    last_updated_by char(8),

    last_update_dt datetime,

    notes varchar(255),

    serial_order int

    )

    insert into @cust_notes

    ( cust_notes_no,

    customer_no,

    esal1_desc,

    description,

    created_by,

    create_dt,

    last_updated_by,

    last_update_dt,

    notes,

    serial_order

    )

    select

    a.cust_notes_no,

    a.customer_no,

    d.esal1_desc,

    c.description,

    a.created_by,

    a.create_dt,

    a.last_updated_by,

    a.last_update_dt,

    b.notes,

    b.serial_order

    from tx_cust_notes a

    join tx_cust_notes_ext b on a.cust_notes_no = b.cust_notes_no

    join tr_cust_notes_type c on a.note_type = c.id

    join tx_cust_sal d on a.customer_no = d.customer_no and d.signor = 0

    where a.create_dt >= @start_dt

    or a.last_update_dt >= @start_dt

    order by a.cust_notes_no, b.serial_order

    select distinct

    cust_notes_no,

    customer_no,

    esal1_desc,

    description,

    created_by,

    create_dt,

    last_updated_by,

    last_update_dt,

    (select '' + notes from @cust_notes a where a.cust_notes_no = b.cust_notes_no order by customer_no for xml path('')) as notegroup

    from @cust_notes b

    group by cust_notes_no,

    customer_no,

    esal1_desc,

    description,

    created_by,

    create_dt,

    last_updated_by,

    last_update_dt


    ================================
    Lucie Spieler
    IT Development and Training Manager
    Editor, Season Program
    Florida Grand Opera
    ------------------------
    8390 NW 25th Street
    Miami, FL 33122-1504

    305-854-1643 x.1521 phone
    305-856-1042 fax
    800-741-1010 ticket office
    lspieler@fgo.org
    www.fgo.org
Reply
  • $organization
    Here's for a report we have:

    CREATE procedure [dbo].[lrp_dev_notes]

    ( @start_dt datetime

    )

    as

    declare @cust_notes table

    ( cust_notes_no int,

    customer_no int,

    esal1_desc varchar(55),

    description varchar(30),

    created_by char(8),

    create_dt datetime,

    last_updated_by char(8),

    last_update_dt datetime,

    notes varchar(255),

    serial_order int

    )

    insert into @cust_notes

    ( cust_notes_no,

    customer_no,

    esal1_desc,

    description,

    created_by,

    create_dt,

    last_updated_by,

    last_update_dt,

    notes,

    serial_order

    )

    select

    a.cust_notes_no,

    a.customer_no,

    d.esal1_desc,

    c.description,

    a.created_by,

    a.create_dt,

    a.last_updated_by,

    a.last_update_dt,

    b.notes,

    b.serial_order

    from tx_cust_notes a

    join tx_cust_notes_ext b on a.cust_notes_no = b.cust_notes_no

    join tr_cust_notes_type c on a.note_type = c.id

    join tx_cust_sal d on a.customer_no = d.customer_no and d.signor = 0

    where a.create_dt >= @start_dt

    or a.last_update_dt >= @start_dt

    order by a.cust_notes_no, b.serial_order

    select distinct

    cust_notes_no,

    customer_no,

    esal1_desc,

    description,

    created_by,

    create_dt,

    last_updated_by,

    last_update_dt,

    (select '' + notes from @cust_notes a where a.cust_notes_no = b.cust_notes_no order by customer_no for xml path('')) as notegroup

    from @cust_notes b

    group by cust_notes_no,

    customer_no,

    esal1_desc,

    description,

    created_by,

    create_dt,

    last_updated_by,

    last_update_dt


    ================================
    Lucie Spieler
    IT Development and Training Manager
    Editor, Season Program
    Florida Grand Opera
    ------------------------
    8390 NW 25th Street
    Miami, FL 33122-1504

    305-854-1643 x.1521 phone
    305-856-1042 fax
    800-741-1010 ticket office
    lspieler@fgo.org
    www.fgo.org
Children
No Data