CODEWITHSUNDEEP
sqlsql-serversql-server-2005aggregate
john
john

Reputation: 3

The max value of several columns (date and cost relationship)

How do you return the max value of several columns (date and cost relationship)?

TableName [ID, Date1, Cost1, Date2, Cost2, Date3, Cost3]

I need to return something like this: [ID, Most Recent Date, Cost]

I need to return something like this:

I need to use another table - BackupTableName:

...and use its' data in the situation when Cost1, Cost2 and Cost3 from TableName are null.

Upvotes: 0

Views: 259

Answers (2)

bobince
bobince

Reputation: 536645

SELECT ID,
    CASE
        WHEN Date1>Date2 AND Date1>Date3 THEN Date1
        WHEN Date2>Date1 AND Date2>Date3 THEN Date2
        ELSE Date3
    END AS "Most Recent Date",
    CASE
        WHEN Date1>Date2 AND Date1>Date3 THEN Cost1
        WHEN Date2>Date1 AND Date2>Date3 THEN Cost2
        ELSE Cost3
    END AS Cost
FROM TableName;

‘Date1’...‘Date2’...‘Date3’ is a schema smell. Consider normalising to a separate many-to-one table.

ETA:

in the situation when Cost1, Cost2 and Cost3 from TableName are null.

You have nulls in there too? OK, this is getting really unpleasant. You'd need to protect against the nulls, like:

    CASE
        WHEN Date1>COALESCE(Date2, 0) AND Date1>COALESCE(Date3, 0) THEN Date1
        WHEN Date2>COALESCE(Date1, 0) AND Date2>COALESCE(Date3, 0) THEN Date2
        ELSE Date3
    END AS "Most Recent Date",

(in both the date-selection bit and the cost-selection bit. This assumes a Cost will always normally be higher than 0.)

I need to use another table: BackupTableName [ID, Date1, Cost1, Date2, Cost2, Date3, Cost3] and use its data

Well... it's possible but your model is now looking extremely precarious.

SELECT t0.ID,
    CASE
        WHEN t0.Date1>COALESCE(t0.Date2, 0) AND t0.Date1>COALESCE(t0.Date3, 0) THEN t0.Date1
        WHEN t0.Date2>COALESCE(t0.Date1, 0) AND t0.Date2>COALESCE(t0.Date3, 0) THEN t0.Date2
        WHEN t0.Date3>COALESCE(t0.Date1, 0) AND t0.Date3>COALESCE(t0.Date1, 0) THEN t0.Date3
        WHEN t1.Date1>COALESCE(t1.Date2, 0) AND t1.Date1>COALESCE(t1.Date3, 0) THEN t1.Date1
        WHEN t1.Date2>COALESCE(t1.Date1, 0) AND t1.Date2>COALESCE(t1.Date3, 0) THEN t1.Date2
        WHEN t1.Date3>COALESCE(t1.Date1, 0) AND t1.Date3>COALESCE(t1.Date1, 0) THEN t1.Date3
    END AS "Most Recent Date",
    -- the same thing all over again for the cost selector
FROM TableName AS t0
LEFT JOIN BackupTableName AS t1 ON t0.ID=t1.ID;

If it is at all within your power you need to normalise these tables, because the data model you have at the moment is essentially broken.

Upvotes: 2

Chandra Patni
Chandra Patni

Reputation: 17587

If your DB supports, you can use greatest function

select ID, greatest(Date1, Date2, Date3) as recent_date, 
           greatest(Cost1, Cost2, Cost3) as priceless from Table

Upvotes: 0

Related Questions