July 14, 2008 at 7:43 am
Background; I have a table where incident notes are added with the incident identifer on a row by row basis. I have been asked to write a report that will pull incident information into one place. Plan is to try and consolidate the multiple rows into one with the ID that I can then place into a SQL 2000 reporting services report using lists and text boxes.
I know that cursors are generally not very nice and that there are possibly better ways to do this (please point me in the right direction if you can). However as I am restricted to working with SQL 2K this is what I have so far.
--Create table to hold results.
CREATE TABLE #incNotes
(
noteIdVARCHAR(255),
noteVARCHAR(6000)
)
GO
--Declare cursor to extract information from source tbl
DECLARE concatNotes CURSOR
FOR
--Select ID and data field
SELECT FA.Id, CONVERT(VARCHAR(8000), FA.noteText) AS noteText
FROM dbo.filterviewI FI
RIGHT JOIN dbo.filterViewA FA
ON FI.incidentId = FA.objectId
WHERE FI.status = 'Active'
AND FA.noteText IS NOT NULL
--Declare variables to hold ID and data.
DECLARE @noteId AS VARCHAR(255)
DECLARE @notes AS VARCHAR(8000)
OPEN concatNotes
FETCH NEXT FROM concatNotes INTO @noteId, @notes
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
--PRINT @noteId + ' ' + @notes
--eg.
--Update temp table if ID exists else insert new values.
IF EXISTS (SELECT noteId FROM #incNotes)
BEGIN
UPDATE #incNotes
SET note = (note + ' ' + @notes)
WHERE noteId = @noteId
END
ELSE
INSERT INTO #incNotes
(noteId, note)
VALUES
(@noteId, @notes)
END
FETCH NEXT FROM concatNotes INTO @noteId, @notes
END
CLOSE concatNotes
DEALLOCATE concatNotes
GO
--Select from tmp tbl to check data.
SELECT *
FROM #incNotes
GO
DROP TABLE #incNotes
The result is that it will happily put the first cluster of notes into a row but nothign more. If I comment out the IF statement to insert to the table and uncomment the print command it happily presents all of the data within the source table so I know it is pulling the data in.
Apologies for the quality of the code but I'm not a big fan of cursors, any pointers would be greatly appreciated.
Many thanks
John
July 14, 2008 at 2:06 pm
John, I might be over simplifying this, but it seems to me a function would work nicely here. You should be able to change your table names, and make other alterations to make it work for you. If this looks familiar to anyone, that is because I learned to make functions with Jeff Moden's help, so if it works...
--Create some test tables. One main and one for notes.
CREATE TABLE IncidentMain
(
ID INT IDENTITY(1,1),
STATUS VARCHAR(20),
PRIMARY KEY(id)
)
CREATE TABLE IncidentNotes
(
ID INT IDENTITY(1,1),
INCIDENT_ID INT NOT NULL,
NOTE VARCHAR(500)
PRIMARY KEY(id)
)
--Insert some test data
INSERT INTO IncidentMain
SELECT 'ACTIVE' UNION ALL
SELECT 'INACTIVE' UNION ALL
SELECT 'ACTIVE' UNION ALL
SELECT 'OTHER'
INSERT INTO IncidentNotes
SELECT 1,'This ' UNION ALL
SELECT 1,'is ' UNION ALL
SELECT 1,'the ' UNION ALL
SELECT 1,'result ' UNION ALL
SELECT 3,'of ' UNION ALL
SELECT 3,'the ' UNION ALL
SELECT 3,'function'
--Create a function
CREATE FUNCTION dbo.fnIncidentNotes
(
@incident_id INT
)
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @Return VARCHAR(8000)
SELECT @Return = ISNULL(@Return,'') + note
FROM IncidentNotes
WHERE incident_id = @incident_id
RETURN @Return
END
--See the results
SELECT DISTINCT
m.ID,
m.status,
dbo.fnIncidentNotes(n.incident_id) AS Notes
FROM IncidentMain m,
IncidentNotes n
WHERE m.status = 'ACTIVE'
AND m.id = n.incident_id
Hope this helps
Greg
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
July 14, 2008 at 3:01 pm
Greg EXCELLENT answer complete with test data. Let me say again a nice job.
Now just want to point out the the statement
CREATE FUNCTION dbo.fnIncidentNotes
(
@incident_id INT )<---this one was missing
was missing that last ) Without it the error messages would confuse one not familiar with function definition format.
July 14, 2008 at 8:12 pm
Well it wasn't really missing, but rather was turned into the yellow face when I pasted the code in the block. I did not know how to get rid of it, but I knew you would figure it out.
Greg
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
July 16, 2008 at 1:40 am
Greg
Many thanks for the advice, I will give it a go and let you know how it turns out.
Many thanks
John
July 22, 2008 at 5:56 am
Greg
Many thanks for the pointer, just a couple of tweak to handle appending null values and it works perfectly.
Many thanks for the advice.
July 23, 2008 at 8:04 am
I'm not sure I understand why you need a single row in the 1st place. Doesn't reporting services have a table control with grouping capability? Notes related to an "incident" might well contain hundreds, if not thousands of characters, and aggregating them into a single field seems like unnecessary effort. Wouldn't you want to present the notes in reverse order of occurrence, with the most recent note at the top, and the date/time of each note showing? Maybe I'm missing something, but I would just wonder why a single record is really necessary.
Steve
(aka smunson)
:):):)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 24, 2008 at 8:37 am
smunson (7/23/2008)
Wouldn't you want to present the notes in reverse order of occurrence, with the most recent note at the top, and the date/time of each note showing?
Thats a good point. I actually had a request to do that, and just put an ORDER BY in the function.
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
July 24, 2008 at 9:18 am
While that will place the notes in the correct order, what will you do when there are more note characters than a single variable will hold? Also, the function shown isn't grabbing the date/time values for these notes, so someone viewing the data will have no idea when each note was entered, which seems likely to be critical to the monday morning quarterback process such data is usually used for. Just some things to think about, as I still don't see a good reason to just have a single record in your output.
Steve
(aka smunson)
:):):)
Greg Snidow (7/24/2008)
smunson (7/23/2008)
Wouldn't you want to present the notes in reverse order of occurrence, with the most recent note at the top, and the date/time of each note showing?Thats a good point. I actually had a request to do that, and just put an ORDER BY in the function.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 24, 2008 at 9:39 am
My personal preference was to place the data into a table at the foot of the report with the data pulled out of a seperate data set, however due to the customers requirements to have it in one field it meant concatenating all fo the data.
On the up side they will be migrating to SSRS 2005 in the near future so the issue can be mittigated somewhat by using VARCHAR(MAX), however I am probably going to still try and get them to go downt he tabular route.
July 24, 2008 at 9:53 am
Does the customer really understand what "one field" is ? If you're concatenating all the notes together, how could they know the difference between a field large enough to contain data containing CR/LF items vs. you're own introduction of CR/LF in the concatenation process, on an RS report? (which could easily allow you to introduce the date/time data, btw)
If they wouldn't know the difference, then there is none, so you might as well use multiple records. You can always adjust line height and font to bring the space usage to a fairly reasonable quantity, and conditional display of a line of "..." if there are more than x number of notes, perhaps...
Somehow this just sort of sounded like a user with expectations that might just be meetable if they can get more data than they otherwise would.
Steve
(aka smunson)
:):):)
John Martin (7/24/2008)
My personal preference was to place the data into a table at the foot of the report with the data pulled out of a seperate data set, however due to the customers requirements to have it in one field it meant concatenating all fo the data.On the up side they will be migrating to SSRS 2005 in the near future so the issue can be mittigated somewhat by using VARCHAR(MAX), however I am probably going to still try and get them to go downt he tabular route.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply