Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2006
    Posts
    5

    Unanswered: function using comparing dates not working right

    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.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    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):
    Code:
    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
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Sep 2006
    Posts
    5
    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.

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    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

    Code:
    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
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •