Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2007
    Posts
    7

    Unanswered: The aggregate for several fields in same row?

    I need to find an aggregate for several fields in a row
    e.g. Max(date1, date2, ..., dateN)

    I can pass this to a delimited string,
    pass the string to an UDF that returns a table
    and run Max(tablefield) on that UDF

    Unfortunately I can only get this working for 1 delimited string at a time

    Ideally I would want to include the function in a SELECT statement, e.g. something like

    SELECT t1.a, dbo.MaxOfFieldValues(t1.d1+','+t1.d2+...+','+t2.dN )
    FROM t1

    I got it working with the following two udfs, but I am sure visitors here have solved this a bit smarter:


    ALTER Function [dbo].[MaxOfFieldValues]
    (
    @ListOfValues varchar(8000)
    , @delimiter varchar(10) = ','
    )
    RETURNS VARCHAR(8000)
    AS
    BEGIN
    --Need to get the maximum changedate first
    --pass the fields as one value (a delimited string)
    --and calc the max
    declare @result varchar(8000)
    declare @remainder varchar(8000)
    set @remainder = @ListOfValues
    declare @NoOfItems int --items = delimiters +1
    SET @NoOfItems = (len(@ListOfValues) - Len(Replace(@ListOfValues,@delimiter,''))/Len(@ListOfValues))+1
    declare @counter int
    set @counter =1
    set @result = dbo.TakePart(@remainder,@delimiter,@counter)
    WHILE @counter <= @NoOfItems
    BEGIN
    set @counter = @counter + 1
    IF @result < dbo.TakePart(@remainder,@delimiter,@counter)
    BEGIN
    SET @result = dbo.TakePart(@remainder,@delimiter,@counter)
    END
    END
    RETURN (@result)
    END

    ALTER FUNCTION [dbo].[TakePart]
    (
    @param varchar(8000)
    , @delimiter varchar(10)
    , @NumPart int
    )
    RETURNS varchar(8000)
    AS
    BEGIN
    --Note: maybe smarter to whack the delimiter to the end of the string to avoid the IF statement
    declare @result varchar(8000)
    declare @remainder varchar(8000)
    declare @counter int
    set @result = ''
    set @remainder = @param
    set @counter = 1
    WHILE @counter < @Numpart
    BEGIN
    SET @remainder = SUBSTRING(@remainder,CHARINDEX(@delimiter,@remaind er,1)+Len(@delimiter),8000)
    SET @counter = @counter +1
    END

    IF @counter > (len(@param) - Len(Replace(@param,@delimiter,''))/Len(@delimiter))
    BEGIN
    SET @result = @remainder
    END
    ELSE
    BEGIN
    SET @result = LEFT(@remainder,CHARINDEX(@delimiter,@remainder,1)-1)
    END

    RETURN @result
    END


    Cheers

    Drio

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I'd just create a single function called, say, LARGEST(), that compares only two values and returns the highest one. Then I'd nest the function calls for your purposes:
    select LARGEST(Date1, LARGEST(Date2, LARGEST(Date3, LARGEST(Date4, LARGEST(Date5, LARGEST(Date6, Date7))))))
    If it's not practically useful, then it's practically useless.

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

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    good one, blindman, that appears to be the cleanest solution

    too bad sql server does not support the standard sql GREATEST function, which returns—you guessed it—the largest value from amongst all its parameters --
    Code:
    select greatest(date1, date2, ..., dateN) from ...
    but hey, at least sql server lets you create UDFs, eh
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Jun 2007
    Posts
    7
    Thanks guys

    I have 16 (sometimes 17) in this specific case

    In this (my) specific case this relates to patient details, but it could be customer details elsewhere.

    They store data in the originating systems as:
    0. CustomerID
    1. FieldValue
    2. FieldChangeDate
    3. FieldChangedBy

    And the data we get is some sort of a flat file with
    0. CustomerID
    1a. FieldValue
    1b. FieldChangeDate
    1c. FieldChangedBy
    ...
    Na. FieldValue

    etc.

    Cheers

    Drio

    BTW. I saw this GREATEST function from Oracle, and wondered if anyone has implemented a more efficient one (than the one in my example) for MSSQL 2000/2005.

  5. #5
    Join Date
    Jun 2007
    Posts
    7
    Hi guys

    In my sleep I dreamt up the following, which seems to do the trick in SQL

    Code:
    SELECT t2.ID, MAX(t2.dte) AS Max_dte
    FROM (
    SELECT t1.ID, t1.[1_Ch_Date] AS dte FROM MyTable AS t1 UNION ALL
    SELECT t1.ID, t1.[2_Date] AS dte  FROM MyTable AS t1 UNION ALL
    SELECT t1.ID, t1.[3_Ch_Date] AS dte  FROM MyTable AS t1 UNION ALL
    ...
    SELECT t1.ID, t1.[16_Ch_Date] AS dte  FROM MyTable AS t1 UNION ALL
    SELECT t1.ID, t1.[17_Ch_Date] AS dte  FROM MyTable AS t1
    ) AS t2
    GROUP BY t2.ID
    To make it more generic, I'll (try to) encapsulate it in a sproc or UDF that
    a. receives a delimted string
    b. uses CSV2table
    c. scripts the query
    d. executes the query
    e. passes the result

    Cheers

    Drio

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I suspect this will not give anywhere near the performance of the nested function method I suggested, since you are scanning the table umpteen times and tossing in a UNION to boot. The method I suggested entails only one scan through the table.
    If it's not practically useful, then it's practically useless.

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

Posting Permissions

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