Post reply

problem using MAX on date

  • July 6, 2006 at 5:19 am

    #173151

    I've used the stored procedure below on a database I keep a caller ID log on, basically there is a field called callrecieved which I use within my program which stores the current time using NOW within VB which produces the time and date in this format 06/07/2006 11:57:00, the problem I'm not getting is if I get 2 calls within the same minute I get more than one return when I run the SP. All I want is the most recent call

    declare @sVar as smalldatetime

    set @sVar = (select max (callrecieved) from [cdi])

    select * from [cdi] where [callrecieved] = @sVar

    print @sVar

     

     

  • July 6, 2006 at 5:51 am

    #647716

    How do you identify the "most recent" call if they have the same value for callreceived?

    Perhaps you would be better using datetime datatype, rather than smalldatetime?

    You can use "top 1" (as below), but you will not necessarily get the "most recent" call - just one them.

    select top 1 * from cdi order by callrecieved desc

     

    Ryan Randall

    Solutions are easy. Understanding the problem, now, that's the hard part.

  • July 6, 2006 at 6:12 am

    #647722

    that appears to work ok, thanks for the response

  • July 6, 2006 at 6:29 am

    #647728

    Books Online states that SMALLDATETIME is only accurate to the minute.

    Are the calls in the database also stored as SMALLDATETIME? IF so, changing to DATETIME for getting latest call is useless.

    N 56°04'39.16"
    E 12°55'05.25"

  • July 6, 2006 at 9:15 am

    #647775

    >> Books Online states that SMALLDATETIME is only accurate to the minute.

    Yep. That's why they will often have the same value for callreceived. And why you can't then really tell which is the "most recent".

    >> Are the calls in the database also stored as SMALLDATETIME? IF so, changing to DATETIME for getting latest call is useless.

    True for existing data, yes. But for new data going in, this will improve things.

     

     

    Ryan Randall

    Solutions are easy. Understanding the problem, now, that's the hard part.

  • July 10, 2006 at 2:10 am

    #648172

    thanks for the input guys

  • July 12, 2006 at 10:25 pm

    #648867

    It's things like this that make me appreciate a good old fashioned IDENTITY column...

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • July 13, 2006 at 4:04 am

    #648903

    Jeff, any chance you can show me how the IDENTITY column works?

  • July 13, 2006 at 7:50 am

    #648955

    Sure, Mick... Sorry for the previous all too brief post...

    First, let me tell you that the others are absolutely correct... SMALLDATETIME is rounded to the nearest minute and you should probably use a DATETIME datatype, instead.

    Even with that, though, some programs and devices don't capture or present seconds or milliseconds as part of their clock offering.  Determining which record was the last record entered for any given minute is impossible because you cannot rely on any physical order of records.  That's were the IDENTITY column would help...

    An IDENTITY column is nothing more than an autonumbering column.  Inherently, it shows the order the rows of info were originally entered into a table when the rows are entered 1 at a time (bulk inserts of many rows are a different story).  In other words, all though it doesn't give you the time or date, it will show the relative chronological order that the rows were entered from a GUI or a device that automatically captures data and writes it to a table one row at a time, such as in your situation (I believe).

    So, if you have records that look like this (person either made 4 call attempts in same minute, or perhaps it's a call status from an IVR, etc)...

    CallerID   CallReceived            SomeOtherData

    2485151234 2006-07-12 13:10:00.000 xx

    2485151234 2006-07-12 13:10:00.000 bb

    2485151234 2006-07-12 13:10:00.000 xx

    2485151234 2006-07-12 13:10:00.000 bb

    There's no way to identify what the "latest" record is (or anything about the chronological order of the records) because all of the CallReceived datetimes are identical and there's nothing else in the records to indicate a relative chronological order (the records are not sortable within the same minute).

    If a column with an IDENTITY property where added (I'll use "CallNum" for the example), the records would might look like this when sorted by CallerID, CallReceived, and CallNum...

    CallNum CallerID   CallReceived            SomeOtherData

    18382   2485151234 2006-07-12 13:10:00.000 bb

    18385   2485151234 2006-07-12 13:10:00.000 xx

    18387   2485151234 2006-07-12 13:10:00.000 bb

    18395   2485151234 2006-07-12 13:10:00.000 xx

    The "gaps" in CallNum for this CallerID would be caused by the sort order where other callers called in during the same minute.  The key is that you now know the relative order of the records and which call made by CallerID 2485151234 is the latest call for the given minute.

    The above table could be created as follows (just an example, folks):

     CREATE TABLE dbo.Calls

            (

            CallNum       INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,

            CallerID      VARCHAR(10),

            CallReceived  DATETIME,

            SomeOtherDate VARCHAR(10)

            )

    The first "1" in the IDENTITY property definition says to start the numbering at "1".  The second "1" says to increment each successive row by 1.

    As a side benefit, since the example table has no candidates for a Primary Key, the CallNum column is guaranteed to be unique and works well as a Primary Key with all the performance benefits.  Since the data in the column is always increasing in value, it's OK to use a CLUSTERED Primary Key (in this case) even if the table is extremely transactional.

    Now, you could add a datetime column with a default of GETDATE()... but, that might not buy you anything with an automated status capture device such as an IVR.  If the IVR program is quick, it could send two individual records to the table within the same 3 milliseconds of each other (3 ms is the resolution of the DATETIME datatype, I believe) or even within the same millisecond and the times in the column would be the same.  Inserting single records with the IDENTITY column present guarantees that the order the records were inserted will be preserved by the IDENTITY column.

    I gotta get to work so I have to end this thread, for now... I'll come back to show you the code that does what you want tonight if no one else does (I don't mind, either).

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • July 13, 2006 at 9:57 am

    #649022

    Thanks Jeff, as always you guys come up trumps, your post is very informative and has helped a lot. Any information you come up with will be great. Thanks again

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply