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