How to find a minimum value from multiple columns in a single row considering if any column contains 0 then it should not participate in comparision

Question

I want to find minimum value from multiple columns in a single row and need to add a new column against same row containing that minimum value. I also want to consider if any of the column contains 0 then that column should not participate in comparison.

For e.g. I have 13 columns joining from multiple tables.

I want result as below:

I have written the query to find out the minimum but not able to put the logic to exclude the column in comparison if it contains 0 value.

SELECT ACCGRPID,
       PERIL,
       lts1.LOCID,
       TIV,
       LIMITAMT AS LOCLIMIT,
       EQSITELIMAMT,
       EQCOMBINEDLIMAMT,
       HUSITELIMAMT,
       HUCOMBINEDLIMAMT,
       TOSITELIMAMT,
       TOCOMBINEDLIMAMT,
       FLSITELIMAMT,
       FLCOMBINEDLIMAMT,
       FRSITELIMAMT,
       FRCOMBINEDLIMAMT,
       TRSITELIMAMT,
       TRCOMBINEDLIMAMT,
       CASE WHEN (DEDUCTAMT <= 1) THEN (TIV * DEDUCTAMT) ELSE DEDUCTAMT END AS LOCDED,
       --EQDED
       CASE WHEN (EQSITEDEDAMT <= 1) THEN (TIV * EQSITEDEDAMT) ELSE EQSITEDEDAMT END AS EQSITEDEDAMT,
       CASE WHEN (EQCOMBINEDDEDAMT <= 1) THEN (TIV * EQCOMBINEDDEDAMT) ELSE EQCOMBINEDDEDAMT END AS EQCOMBINEDDEDAMT,
       --HUDED
       CASE WHEN (HUSITEDEDAMT <= 1) THEN (TIV * HUSITEDEDAMT) ELSE HUSITEDEDAMT END AS HUSITEDEDAMT,
       CASE WHEN (HUCOMBINEDDEDAMT <= 1) THEN (TIV * HUCOMBINEDDEDAMT) ELSE HUCOMBINEDDEDAMT END AS HUCOMBINEDDEDAMT,
       --TODED
       CASE WHEN (TOSITEDEDAMT <= 1) THEN (TIV * TOSITEDEDAMT) ELSE TOSITEDEDAMT END AS TOSITEDEDAMT,
       CASE WHEN (TOCOMBINEDDEDAMT <= 1) THEN (TIV * TOCOMBINEDDEDAMT) ELSE TOCOMBINEDDEDAMT END AS TOCOMBINEDDEDAMT,
       --FLDED
       CASE WHEN (FLSITEDEDAMT <= 1) THEN (TIV * FLSITEDEDAMT) ELSE FLSITEDEDAMT END AS FLSITEDEDAMT,
       CASE WHEN (FLCOMBINEDDEDAMT <= 1) THEN (TIV * FLCOMBINEDDEDAMT) ELSE FLCOMBINEDDEDAMT END AS FLCOMBINEDDEDAMT,
       --FRDED
       CASE WHEN (FRSITEDEDAMT <= 1) THEN (TIV * FRSITEDEDAMT) ELSE FRSITEDEDAMT END AS FRSITEDEDAMT,
       CASE WHEN (FRCOMBINEDDEDAMT <= 1) THEN (TIV * FRCOMBINEDDEDAMT) ELSE FRCOMBINEDDEDAMT END AS FRCOMBINEDDEDAMT,
       --TRDED
       CASE WHEN (TRSITEDEDAMT <= 1) THEN (TIV * TRSITEDEDAMT) ELSE TRSITEDEDAMT END AS TRSITEDEDAMT,
       CASE WHEN (TRCOMBINEDDEDAMT <= 1) THEN (TIV * TRCOMBINEDDEDAMT) ELSE TRCOMBINEDDEDAMT END AS TRCOMBINEDDEDAMT,
       **(SELECT MIN(Col)
        FROM (VALUES (LIMITAMT),
                     (EQSITELIMAMT),
                     (EQCOMBINEDLIMAMT),
                     (HUSITELIMAMT),
                     (HUCOMBINEDLIMAMT),
                     (TOSITELIMAMT),
                     (TOCOMBINEDLIMAMT),
                     (FLSITELIMAMT),
                     (FLCOMBINEDLIMAMT),
                     (FRSITELIMAMT),
                     (FRCOMBINEDLIMAMT),
                     (TRSITELIMAMT),
                     (TRCOMBINEDLIMAMT)
                     ) X (Col) ) AS MINLIM,**
       (SELECT MAX(Col)
        FROM (VALUES (DEDUCTAMT),
                     (EQSITEDEDAMT),
                     (EQCOMBINEDDEDAMT),
                     (HUSITEDEDAMT),
                     (HUCOMBINEDDEDAMT),
                     (TOSITEDEDAMT),
                     (TOCOMBINEDDEDAMT),
                     (FLSITEDEDAMT),
                     (FLCOMBINEDDEDAMT),
                     (FRSITEDEDAMT),
                     (FRCOMBINEDDEDAMT),
                     (TRSITEDEDAMT),
                     (TRCOMBINEDDEDAMT)
                     ) X (Col) ) AS MAXDED
FROM @LOCTS1 lts1
     OUTER APPLY (SELECT * FROM @LOCTS2 lts2 WHERE lts1.LOCID = lts2.LOCID) LO2
     OUTER APPLY (SELECT * FROM @LOCTS3 lts3 WHERE lts1.LOCID = lts3.LOCID) LO3
     OUTER APPLY (SELECT * FROM @LOCTS4 lts4 WHERE lts1.LOCID = lts4.LOCID) LO4
     OUTER APPLY (SELECT * FROM @LOCTS5 lts5 WHERE lts1.LOCID = lts5.LOCID) LO5
     OUTER APPLY (SELECT * FROM @LOCTS6 lts6 WHERE lts1.LOCID = lts6.LOCID) LO6
     OUTER APPLY (SELECT * FROM @LOCTS7 lts7 WHERE lts1.LOCID = lts7.LOCID) LO7

Answer 1

Use APPLY

select t.*, v.minlim
from t cross apply
     (select min(col) as minlim
      from (values (LIMITAMT),
                   (EQSITELIMAMT),
                   (EQCOMBINEDLIMAMT),
                   (HUSITELIMAMT),
                   (HUCOMBINEDLIMAMT),
                   (TOSITELIMAMT),
                   (TOCOMBINEDLIMAMT),
                   (FLSITELIMAMT),
                   (FLCOMBINEDLIMAMT),
                   (FRSITELIMAMT),
                   (FRCOMBINEDLIMAMT),
                   (TRSITELIMAMT),
                   (TRCOMBINEDLIMAMT)
            ) v(Col)
      where col <> 0
     ) v;