Get MIN value between 3 columns on same row

Though I have come up with a couple of working solutions to what I am looking for, I am wondering if there is a more streamlined way of determining which of 3 columns from a single row contains the smallest/min value. Below is an example of the data I am working with:

AccountNumber	Job	DaysSinceLastSale	DaysSinceLastCharge	DaysSinceEstablished
YO502	NULL	NULL	5283	NULL
YO525	NULL	2303	2303	5917
ZE100	1	190	449	707
ZE100	2	160	279	615
ZI402	NULL	2109	2109	NULL

And what the outcome would be, which is just the new column of DaysInactive containing the lesser of the 3 non-null DaysSincexxx values:

AccountNumber	Job	DaysSinceLastSale	DaysSinceLastCharge	DaysSinceEstablished	DaysInactive
YO502	NULL	NULL	5283	NULL	5283
YO525	NULL	2303	2303	5917	2303
ZE100	1	190	449	707	190
ZE100	2	160	279	615	160
ZI402	NULL	2109	2109	NULL	2109

This is what I have to this point that simply uses a series of CASE expressions to compare them with. The objective is to find the lowest value of days of inactivity for a client job based on any of 3 different date values tied to the job. And to be clear, 2 of the columns (DaysSinceLastSale and DaysSinceLastCharge) are the primary target, with the 3rd (DaysSinceEstablished) being a last resort in the event the first 2 are NULL (All 3 of them can actually be NULL, in which case we default to 99999). I am ultimately using this in a PowerBI report so a >= slicer can be setup for the end-users to manually enter the minimum number of inactive days a record should have in order to be returned on the report visual:

SELECT 
    CASE 
        WHEN COALESCE(DaysSinceLastSale, DaysSinceLastCharge) IS NOT NULL THEN 
            CASE WHEN COALESCE(DaysSinceLastSale, DaysSinceLastCharge) <= COALESCE(DaysSinceLastCharge, DaysSinceLastSale)  
                THEN COALESCE(DaysSinceLastSale, DaysSinceLastCharge) 
                ELSE COALESCE(DaysSinceLastCharge, DaysSinceLastSale) 
            END
        ELSE  COALESCE(DaysSinceEstablished, 99999) 
        END as DaysInactive
    , DS.*  
FROM #DS2 DS

This was my original solution but I just didn’t like having all the sub-queries and UNIONs:

SELECT 
    NormalizedDaysInactive.DaysInactive
    , DS.* 
FROM #DS2 DS 
JOIN (
    SELECT 
        AccountNumber, BillingLevel, Job, MIN(ISNULL(DaysInactive, 99999)) as DaysInactive 
    FROM
    (
        SELECT AccountNumber, BillingLevel, Job, MIN(DaysSinceLastSale) as DaysInactive FROM #DS2 GROUP BY AccountNumber, BillingLevel, Job
        UNION ALL
        SELECT AccountNumber, BillingLevel, Job, MIN(DaysSinceLastCharge) as DaysInactive FROM #DS2 GROUP BY AccountNumber, BillingLevel, Job
        UNION ALL
        SELECT AccountNumber, BillingLevel, Job, MIN(DaysSinceEstablished) as DaysInactive FROM #DS2 GROUP BY AccountNumber, BillingLevel, Job
    ) ActDays
    GROUP BY AccountNumber, BillingLevel, Job
    HAVING MIN(ISNULL(DaysInactive, 1)) > 0
) NormalizedDaysInactive ON NormalizedDaysInactive.AccountNumber = DS.AccountNumber
    AND (NormalizedDaysInactive.Job = DS.Job OR NormalizedDaysInactive.BillingLevel = 'Account')

Appreciate any suggestions! Thanks

Answer

You can use APPLY to do a little “inline” unpivot operation and then grab the min value. I added the column TypeOfActivity as well as it might come in handy to know what activity as well

Min Value Between Multiple Columns

SELECT *
FROM YourTable AS A
CROSS APPLY (
    SELECT TOP(1) *
    FROM (
        VALUES 
            ('Sale',DaysSinceLastSale)
            ,('Charge',DaysSinceLastCharge)
            ,('Established',DaysSinceEstablished)
    ) AS DTA(TypeOfActivity,DaysSinceActivity)
    WHERE DaysSinceActivity IS NOT NULL
    ORDER BY DaysSinceActivity
) AS B

Advertisement

Answer

Min Value Between Multiple Columns