SQL Server Performance

how to get max date from a list

Discussion in 'General Developer Questions' started by To_DBA, Sep 3, 2008.

  1. To_DBA New Member

    Hi All
    i'm nt good at coding. could anyone help me how to get the max date from several datetime columns? we have a provider table:
    Provider (Prov_Num, Prov_Name, Prov_Address, Prov_Phone, Date_1, Date_2, Date_3, Date_5, Date_6)
    how could i get the maximum date of Date_1, Date_2, Date_3, Date_5, Date_6 in the query?
    thanks in advance.
  2. moh_hassan20 New Member

    Create function
    CREATE FUNCTION get_max_date(
    @d1 datetime ,@d2 datetime ,@d3 datetime ,@d4 datetime ,@d5 datetime ) returns datetime
    as
    begin
    declare @max_date datetime
    set @max_date = @d1

    if @max_date > @d2
    set @max_date = @d2

    if @max_date > @d3
    set @max_date = @d3


    if @max_date > @d4
    set @max_date = @d4


    if @max_date > @d5
    set @max_date = @d5

    return @max_date
    end

    how to call
    select dbo.get_max_date(Date_1, Date_2, Date_3, Date_5, Date_6)
    from Provider
    quick test
    print dbo.get_max_date(getdate(), getdate()+4, getdate()+5, getdate()+3,
    getdate()+2)
  3. Madhivanan Moderator

    orselect
    Prov_Num, max(date_1) as max_date from(select
    Prov_Num,date_1 from providerunion
    allselect
    Prov_Num,date_2 from providerunion
    allselect
    Prov_Num,date_3 from providerunion
    allselect
    Prov_Num,date_4 from providerunion
    allselect
    Prov_Num,date_5 from providerunion
    allselect
    Prov_Num,date_6 from provider)
    as tgroup
    by Prov_Num
  4. Madhivanan Moderator

    Also read about Normalization
  5. To_DBA New Member

    thank you All
    i picked up the one which creates a function and call it from SQL query.
    have a good day.
  6. Madhivanan Moderator

    [quote user="To_DBA"]
    thank you All
    i picked up the one which creates a function and call it from SQL query.
    have a good day.
    [/quote]
    Note that you need to change > to < in the function to pick max date
  7. moh_hassan20 New Member

    [quote user="Madhivanan"]Note that you need to change > to < in the function to pick max date[/quote]
    oh my mistake, change > to be <
    thanks Madhivanan
  8. Madhivanan Moderator

    [quote user="moh_hassan20"]
    [quote user="Madhivanan"]Note that you need to change > to < in the function to pick max date[/quote]
    oh my mistake, change > to be <
    thanks Madhivanan
    [/quote]
    You are welcome [:)]

Share This Page