Professional Web Applications Themes

Get Max date within same record - Microsoft SQL / MS SQL Server

I have a table with 5 date columns, nulls NOT allowed. Let's assume the table has 100 records. I want to return a resultset that has 100 records, and for each record the most recent date among the 5 on that record. That is, I want to get the max of the 5 dates for each record, and the Select WON'T have a Group By. Obviously I could do a bunch of crazy nested CASEs, but is there a better way/function to do this. Thanks. create table mytab ( pk int not null, --primary key a datetime not null, b ...

  1. #1

    Default Get Max date within same record

    I have a table with 5 date columns, nulls NOT allowed.
    Let's assume the table has 100 records.

    I want to return a resultset that has 100 records, and for each record the
    most recent date among the 5 on that record.
    That is, I want to get the max of the 5 dates for each record, and the
    Select WON'T have a Group By.

    Obviously I could do a bunch of crazy nested CASEs, but is there a better
    way/function to do this.

    Thanks.

    create table mytab (
    pk int not null, --primary key
    a datetime not null,
    b datetime not null,
    c datetime not null,
    d datetime not null,
    e datetime not null
    )


    Craig Guest

  2. #2

    Default Re: Get Max date within same record

    C> That is, I want to get the max of the 5 dates for each record, and the
    C> Select WON'T have a Group By.

    C> Obviously I could do a bunch of crazy nested CASEs, but is there a better
    C> way/function to do this.

    make your table more relational, like this:

    pk int not null ,
    datetype char(1) not null -- a,b,c,d,e
    datevalue datetype

    or


    create a user-defined function DateMax(date1,date2) returning the later of
    date1 and date2, then

    select datemax(date1,datemax(date2,datemax(date3,datemax( date4,date5))))

    I did not try it, just an idea.

    Vadim



    Vadim Guest

  3. #3

    Default Re: Get Max date within same record

    create table justnumbers(number int not null)
    insert into justnumbers values (1)
    insert into justnumbers values (2)
    insert into justnumbers values (3)
    insert into justnumbers values (4)
    insert into justnumbers values (5)

    select pk,max(case
    when number=1 then a
    when number=2 then b
    when number=3 then c
    when number=4 then d
    when number=5 then e end) as MaxDate
    from mytab
    cross join justnumbers
    --where number between 1 and 5
    group by pk

    Gert-Jan

    Craig wrote: 
    Gert-Jan Guest

  4. #4

    Default Re: Get Max date within same record

    As Vadim has suggested, you should reconsider whether this table is properly
    normalised. If it is, then:

    CREATE TABLE mytab (pk INTEGER PRIMARY KEY, a DATETIME NOT NULL, b DATETIME
    NOT NULL, c DATETIME NOT NULL, d DATETIME NOT NULL, e DATETIME NOT NULL)

    INSERT INTO mytab VALUES
    (1,'20030101','20030102','20030103','20030104','20 030105')

    SELECT pk,
    (SELECT MAX(v)
    FROM
    (SELECT a AS v UNION ALL
    SELECT b UNION ALL
    SELECT c UNION ALL
    SELECT d UNION ALL
    SELECT e) m)
    AS maxdate
    FROM mytab

    --
    David Portas
    ------------
    Please reply only to the newsgroup
    --



    David Guest

Similar Threads

  1. get record based on date time
    By cybertek23 in forum Coldfusion Database Access
    Replies: 15
    Last Post: May 23rd, 02:59 PM
  2. Get latest (by date) record and combine it with sum()
    By alanspamenglefield@yahoo.co.uk in forum MySQL
    Replies: 0
    Last Post: January 6th, 03:59 PM
  3. date record was last modified? Help
    By Joe Science in forum Macromedia ColdFusion
    Replies: 1
    Last Post: March 23rd, 08:24 PM
  4. date record was last modified. Help!!!!
    By Joe Science in forum Coldfusion - Getting Started
    Replies: 1
    Last Post: March 23rd, 07:52 PM
  5. Displaying Date From a Database Record
    By aDAMFOX in forum Dreamweaver AppDev
    Replies: 9
    Last Post: February 26th, 05:06 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139