Pulling the most recent notes from a table that has a lot of data

1

We have a table that has TON of notes in them. Someone in the past created this query below, how ever I don't want every note for that Specific PCS Record. I would like to get the two most recent notes. However, I don't know how to put that into this type of store procedure

here is a record that has multiple notes PCS 001542376. I would only like to pull the two most recent lines I don't want to see anything that has the stamp with ----------Above Note Written by MDEFR1 on 06/19/2017 at 14:40---------

So I created a Select query that does this. But some how I need to replace what is in the SP below with this select query and I don't know where to put it.

select Note_id, note_sys, note_line, note_data as [Most Recent Note],
row_number() over (partition by Note_id order by note_line desc) As r
from impact.dbo.notes
Where note_sys = 'PCS'order by note_id desc

IF OBJECT_ID('IMPACT_prod..Notes3') IS NOT NULL
DROP TABLE Notes3

SELECT DISTINCT pcs_id1 INTO Notes3 from PCS
ORDER BY pcs_id1
ALTER TABLE Notes3 ADD NoteData VARCHAR(8000)

DECLARE @id varchar(8), @lineid int, @lastlineid int, @notes VARCHAR(8000)
DECLARE cur CURSOR LOCAL FORWARD_ONLY READ_ONLY FOR
SELECT pcs_id1 FROM Notes3

OPEN cur
FETCH NEXT FROM cur INTO @id

WHILE @@FETCH_STATUS = 0
BEGIN --Begin outer while loop
SET @lastlineid = 0
SELECT @lineid = MIN(CAST(n.note_line AS int)) FROM impact.dbo.notes n WHERE note_id = @id AND note_sys = 'PCS'
SET @notes = (SELECT note_data from impact.dbo.notes where note_id = @id AND note_line = @lineid AND note_sys = 'PCS')
WHILE EXISTS (SELECT note_id FROM impact.dbo.NOTES WHERE note_id = @id AND note_line > @lineid AND note_sys = 'PCS')
BEGIN
SET @lastlineid = @lineid
SELECT @lineid = MIN(CAST(n.note_line AS int)) FROM notes n
WHERE n.note_id = @id AND n.note_line > @lastlineid AND note_sys = 'PCS'
PRINT @id + ' -- Line ID: ' + cast(@lineid as varchar(8)) + ' -- Last Line ID: ' + cast(@lastlineid as varchar(8))
SET @notes = @notes + '||' + LTRIM(RTRIM((SELECT note_data from impact.dbo.notes where note_id = @id AND note_line = @lineid AND note_sys = 'PCS')))
END --END INNER While loop
PRINT 'event ID: ' + cast(@id AS varchar(8)) + ':'
PRINT @notes
UPDATE Notes3
SET NoteData = @notes
WHERE pcs_id1 = @id
FETCH NEXT FROM cur INTO @id
END --End Outer While Loop!
CLOSE cur
DEALLOCATE cur

SELECT Distinct
--n.note_sys,
e.pcs_id1 AS 'EventNumber',
NoteData AS 'NOTES'
--left(n.NOTE_DATA, 10) AS 'NOTEStotakeoutprompt'

FROM Impact.dbo.PCS e
right Join Notes3 n
ON n.pcs_id1 = e.pcs_id1

2

What version of SQL Server are you using?

3

Something like below. Since you didn't post usable sample data, I couldn't test the code at all:

select Note_id,
    MAX(note_sys) AS note_sys,
    MAX(CASE WHEN row_num = 1 THEN CAST(note_line AS varchar(10)) END + 
        CASE WHEN row_num = 2 THEN '||' +  CAST(note_line AS varchar(10)) ELSE '' END) 
        AS [Most Recent Note Line(s)],
    MAX(CASE WHEN row_num = 1 THEN note_data END + 
            CASE WHEN row_num = 2 THEN note_data ELSE '' END) 
        AS [Most Recent Note(s)]
from (
    select 
        n.Note_id, n.note_sys, n.note_line, n.note_data,
        row_number() over (partition by n.Note_id order by n.note_line desc) As row_num
    from impact.dbo.notes n
    inner join (
        select DISTINCT pcs_id1
        from dbo.PCS
    ) as p on p.psc_id1 = n.Note_id
    where n.note_sys = 'PCS' and 
        n.note_data not like '----%Above Note%Written by%on%[012][0-9]/[0-3][0-9]/2[01][0-9][0-9]%----'
) as derived
where row_num in (1, 2)
group by Note_id
4

Here's another option...

IF OBJECT_ID('tempdb..#Notes', 'U') IS NOT NULL 
DROP TABLE #Notes;

CREATE TABLE #Notes (
	Note_id CHAR(9) NOT NULL,
	note_sys CHAR(3) NOT NULL,
	note_line INT NOT NULL,
	Most_recient_note VARCHAR(100) NOT NULL,
	PRIMARY KEY CLUSTERED (Note_id, note_line)
	);

INSERT #Notes (Note_id, note_sys, note_line, Most_recient_note) VALUES 
	('001542377', 'PCS', 2, 'Line 2 note'),
	('001542377', 'PCS', 1, 'Line 1 note'),
	('001542376', 'PCS', 2, 'Line 2 note'),
	('001542376', 'PCS', 1, 'Line 1 note'),
	('001542375', 'PCS', 6, 'Line 6 note'),
	('001542375', 'PCS', 5, 'Line 5 note'),
	('001542375', 'PCS', 4, 'Line 4 note'),
	('001542375', 'PCS', 3, 'Line 3 note'),
	('001542375', 'PCS', 2, 'Line 2 note'),
	('001542375', 'PCS', 1, 'Line 1 note');


SELECT * FROM #Notes n;

--====================================================================

WITH 
	cte_NoteID AS (
		SELECT DISTINCT
			n.Note_id
		FROM 
			#Notes n
		)
SELECT 
	*
FROM 
	cte_NoteID nid
	CROSS APPLY (
			SELECT TOP 2
				n.note_sys, n.note_line, n.Most_recient_note
			FROM
				#Notes n
			WHERE 
				nid.Note_id = n.Note_id
			ORDER BY 
				n.note_line DESC
			) last2;
5

Dunno if it is a sensible solution? but you could concatenate the two notes lines with the XML-trick

SELECT	P.pcs_id1 AS [EventNumber]
	, STUFF(
			(
				SELECT	TOP 2
						'||' + N.note_data
				FROM	dbo.notes AS N
				WHERE	N.Note_id = P.psc_id1
						AND n.note_sys = 'PCS'
						AND n.note_data NOT LIKE '----%Above Note%Written by%on%[012][0-9]/[0-3][0-9]/2[01][0-9][0-9]%----'
				ORDER BY N.note_line DESC
				FOR XML PATH(''), TYPE
			).value('.', 'varchar(max)')
	        , 1, 2, '') AS [Most Recent Note(s)]
FROM	dbo.PCS AS P

Is [impact] the currently-connected database or an external database?

If the database (i.e. [impact] in this case) is explicitly named in the query (in ALL your queries ...) and then it changes in the future then you will have a lot of code to change ... and debug / re-test :frowning:

I don't use the database name if it is the "current database", and if it is an external database I always use a SYNONYM. A SYNONYM is defined in the current database, then you refer to the external object by its Synonym Name, and if the external database name, or the table name in that database, changes the only change you have to make is to the Synonym Definition - all your code remains unchanged.

You can use a Synonym to refer to an object in another database on the same server, or an object in a database on a remote server - so even if the local, external, database moves to another server your synonym will still be able to reference it without any code changes.

6

Jason, we are using 2012

7

In that case the code I posted above should work for you. I haven't tested Scott or Kristen's code myself, so be sure to check them as well and see which one will perform better for you in your environment.

Best of luck! :slight_smile:

8

Given that you were kind enough to create some test data here's the outputs from the three samples (with column names corrected etc.). No idea which answers the O/P's question though ...

Note_id   note_sys Most Recent Note Line(s) Most Recent Note(s)
--------- -------- ------------------------ -------------------
001542375 PCS      6                        375 Line 6 note
001542376 PCS      2                        376 Line 2 note
001542377 PCS      2                        377 Line 2 note

Warning: Null value is eliminated by an aggregate or other SET operation.
(3 row(s) affected)

Note_id   note_sys note_line   note_data
--------- -------- ----------- ---------------
001542375 PCS      6           375 Line 6 note
001542375 PCS      5           375 Line 5 note
001542376 PCS      2           376 Line 2 note
001542376 PCS      1           376 Line 1 note
001542377 PCS      2           377 Line 2 note
001542377 PCS      1           377 Line 1 note

(6 row(s) affected)

EventNumber Most Recent Note(s)
----------- --------------------------------
001542375   375 Line 6 note||375 Line 5 note
001542376   376 Line 2 note||376 Line 1 note
001542377   377 Line 2 note||377 Line 1 note

(3 row(s) affected)

I reckon I've rogered Scott's code somehow , sorry about that, not enough time to find&fix the issue :frowning:

1 Like
9

This is from the OP...

So the two most resent lines per Note_ID...
Of course I missed the part about wanting to ignore "----------Above Note Written by MDEFR1 on 06/19/2017 at 14:40---------" lines... :pensive:

WITH 
	cte_NoteID AS (
		SELECT DISTINCT
			n.Note_id
		FROM 
			#Notes n
		)
SELECT 
	*
FROM 
	cte_NoteID nid
	CROSS APPLY (
			SELECT TOP 2
				n.note_sys, n.note_line, n.Most_recient_note
			FROM
				#Notes n
			WHERE 
				nid.Note_id = n.Note_id
                AND n.Most_recient_note <> '----------Above Note Written by MDEFR1 on 06/19/2017 at 14:40---------'
			ORDER BY 
				n.note_line DESC
			) last2;
10

Indeed, but no expected-results provided, so the O/P was silent on "two rows concatenated", or "two rows, separately" ...

... we could have a sweepstake? :slight_smile:

11

I say, race 'em and the 1st one to the finish line wins... Use logical reads and CPU time as a tie breaker. :wink:

1 Like
12

I told my wife I had entered a race but she seemed to glaze over when I explained the rules ... :smiley:

13

LOL... That's okay... Everyone I know thinks I'm the guy to call when Windows is acting a fool, they need their router setup or a printer installed...

No one understands us... :roll_eyes:

14

Sorry, I was in a hurry before and left out a level of the code:

SELECT Note_id, note_sys,
    CAST(note_line1 AS varchar(10)) + ISNULL('||' + CAST(note_line2 AS varchar(10)), '') AS [most_recent_note_line(s)],
    note_data1 + ISNULL('||' +note_data2, '') AS [most_recent_note(s)]
FROM (
    select Note_id,
        MAX(note_sys) AS note_sys,
        MAX(CASE WHEN row_num = 1 THEN note_line END) AS note_line1,
        MAX(CASE WHEN row_num = 2 THEN note_line END) AS note_line2,
        MAX(CASE WHEN row_num = 1 THEN note_data END) AS note_data1,
        MAX(CASE WHEN row_num = 2 THEN note_data END) AS note_data2
    from (
        select 
            n.Note_id, n.note_sys, n.note_line, n.note_data,
            row_number() over (partition by n.Note_id order by n.note_line desc) As row_num
        from #notes n
        /*
        inner join (
            select DISTINCT pcs_id1
            from dbo.PCS
        ) as p on p.psc_id1 = n.Note_id
        */
        where n.note_sys = 'PCS' and 
            n.note_data not like '----%Above Note%Written by%on%[012][0-9]/[0-3][0-9]/2[01][0-9][0-9]%----'
    ) as derived
    where row_num in (1, 2)
    group by Note_id
) AS derived
15

I would like to say thank you so much for everyones help and ideas.

ScottPletcher - This code that you have provided me is going to work for what I need. Thanks so much for everything!

You all rock! :slight_smile: