Research Notes

I have a report that we have scheduled to run weekly for Dev that spits out all Research notes past a given start date. Here’s the query, which can certainly be modified:

 

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

 

 

Brandon -

It depends on what you need. If you have a list of people you can run the Full Bio report (found in the Development folder) and select only the notes section.

HTH,

Heather
Seattle Rep 

I have a report that we have scheduled to run weekly for Dev that spits out all Research notes past a given start date. Here’s the query, which can certainly be modified:

 

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

 

 

Brandon -

It depends on what you need. If you have a list of people you can run the Full Bio report (found in the Development folder) and select only the notes section.

HTH,

Heather
Seattle Rep