Hi,
I'm trying to write a function to return all notes with date. Sample data for 1 record=187189 as follows:
iincidentid,iWorkNoteId,iSeqnum, dtEntryDate, workNoteAll
187189 3440 1 2006-04-24 note1
187189 3545 1 2006-06-22 note2
187189 3547 1 2006-06-22 note3
187189 3653 1 2006-08-10 note4
187189 3653 2 2006-08-10 note5
funtion will return = 2006_08-10 note4 note5 for iincidentid=187189
----------------
CREATE FUNCTION dbo.getIncidentNotesRev(@.iIncidentID int)
RETURNS varchar(8000)
AS
BEGIN
declare @.incidentId int
declare @.worknoteid int
declare @.worknotesaveid int
declare @.seqnum int
declare @.dtEntryDate smalldatetime
declare @.worknoteall varchar(8000)
declare @.allnotes varchar(8000)
declare @.currentWEDate smalldatetime
declare @.beginWEDate smalldatetime
select @.allnotes=''
select @.currentWEDate=currentweekEndDate from csCurrentweekEndDate --get the current week end date
select @.beginWEDate = DATEADD(d, - 28, @.currentWEDate)--get the last 4 weeks
declare CursorIncident CURSOR
LOCAL FOR SELECT iIncidentId, iWorkNoteID, iSeqNum, dtEntryDate,worknoteall FROM dbo.rpt_weekly_prospect_status_vw
where iIncidentId=@.iIncidentID order by iWorkNoteId
OPEN CursorIncident
FETCH NEXT FROM CursorIncident INTO @.incidentId,@.worknoteid,@.seqnum,@.dtEntryDate,@.work noteall
--store 1st record of cursor
select @.worknotesaveid =@.worknoteid
WHILE (@.@.FETCH_STATUS=0)
BEGIN
if @.dtEntryDate >=@.beginWEDate AND @.dtEntryDate <= @.currentWEDate
Begin
if @.worknotesaveid <> @.worknoteid
Begin
Select @.allnotes = @.allnotes + @.dtEntryDate + @.worknoteall
End
else
BEgin
select @.allnotes = @.allnotes + @.worknoteall
End
select @.worknotesaveid = @.worknoteid --save next worknoteId
End
else
Begin
select @.allnotes=''
End
FETCH NEXT FROM CursorIncident INTO @.incidentId,@.worknoteid,@.seqnum,@.dtEntryDate,@.work noteall
END --WHILE (@.@.FETCH_STATUS=0)
CLOSE CursorIncident
DEALLOCATE CursorIncident
return @.allnotes
END
----
Function not working right. I appreciate any help.
Thanks in advance.There is a problem with you logic. If there is more than one dtEntryDate value for an incidentId within the past four weeks, which date should your function return? It is only able to return a single date value.
Think about it.
In the meantime, here is a bit of wisdom: If you find yourself using a cursor, you are either an SQL guru or you are doing something wrong.
This is the type of logic you want to use (returns only notes, because unclear of date issue above):CREATE FUNCTION dbo.getIncidentNotesRev(@.iIncidentID int)
RETURNS varchar(8000)
AS
BEGIN
declare @.allnotes varchar(8000)
declare @.currentWEDate smalldatetime
declare @.beginWEDate smalldatetime
select @.currentWEDate=currentweekEndDate from csCurrentweekEndDate --get the current week end date
select @.beginWEDate = DATEADD(d, - 28, @.currentWEDate)--get the last 4 weeks
select @.AllNotes = Coalesce(@.AllNotes, '') + worknoteall
from dbo.rpt_weekly_prospect_status_vw
where iIncidentId=@.iIncidentID
and dtEntryDate between @.beginWEDate and @.currentWEDate
order by iWorkNoteId
return @.AllNotes
End|||Hi,
In my example above, it should return only the date for note4 and note5 (they are the same) since they have the same iWorkNoteId. In my code I'm checking that; otherwise, if the iWorkNoteId is different, then the date for that note is added to the @.allnotes.
I originally have a similar code as yours, but then I need to modify it because the users need the last 4 weeks notes in which the date is also a part of the allnotes.
I ran your code but it's alwyas giving NUll, although there is data. I think the date comparison is not working.
Thanks.|||Fine...what you supplied with the sample data and expected results is perfect...I just decided not to go through your code...
But this does what you want
USE Northwind
GO
CREATE TABLE myTable99(Incident int, col2 int, col3 int, col4 datetime, note char(10))
GO
INSERT INTO myTable99(Incident, col2, col3, col4, note)
SELECT 187189, 3440, 1, '2006-04-24', 'note1' UNION ALL
SELECT 187189, 3545, 1, '2006-06-22', 'note2' UNION ALL
SELECT 187189, 3547, 1, '2006-06-22', 'note3' UNION ALL
SELECT 187189, 3653, 1, '2006-08-10', 'note4' UNION ALL
SELECT 187189, 3653, 2, '2006-08-10', 'note5'
GO
SELECT * FROM myTable99
GO
SELECT *
FROM myTable99 o
WHERE EXISTS (
SELECT Incident
FROM myTable99 i
WHERE i.Incident = o.Incident
GROUP BY Incident
HAVING o.Col4 = MAX(i.Col4))
GO
DROP TABLE myTable99
GO
