-1

I'm trying to get the min value from the table by comparing four adjacent columns (Date-1 to Date-4) in that table and updating the min value in the final column. But I'm receiving only NULL as the min value instead of getting the min value from four columns.

I'm using case for comparing the columns.

Details of the table

Thorsten Kettner
  • 89,309
  • 7
  • 49
  • 73
Bharath Babu
  • 65
  • 2
  • 8
  • 2
    Does this answer your question? [What's the best way to select the minimum value from several columns?](https://stackoverflow.com/questions/368351/whats-the-best-way-to-select-the-minimum-value-from-several-columns), Because [LEAST](https://learn.microsoft.com/en-us/sql/t-sql/functions/logical-functions-least-transact-sql?view=azure-sqldw-latest) is not yet implemented in sql-server, unless you are on Azure – Luuk Oct 09 '22 at 09:25
  • I've already seen that post, but here the scenario is different. those examples are shown without NULL values. but here i've multiple NULL values and i'm getting min value as NULL instead of min date. Even i've tried with cross apply also, but it's not worked well and good for me. – Bharath Babu Oct 09 '22 at 09:36
  • Next time, please don't use an image, but post the data as formatted text instead. Then you mention that your query generates nulls instead of the desired dates. But where is that query? Show it. – Thorsten Kettner Oct 09 '22 at 09:39

1 Answers1

1

As SQL Server does not yet support LEAST in its current version SQL Server 2019, you can apply a lateral cross join on an aggregation query on the values instead.

select *
from mytable t
cross apply
(
    select min(dt) as final_min_date
    from (values (t.date_1), (t.date_2), (t.date_3), (t.date_4)) all_dates (dt)
) min_date;
Thorsten Kettner
  • 89,309
  • 7
  • 49
  • 73
  • Thanks for your solution. But, while trying with update command I need to fetch single line of value without NULL this only generating multiple values. – Bharath Babu Oct 09 '22 at 09:37
  • This is a working solution, pretty much what is in the linked duplicated question. `Min` will only produce a single row and will ignore any NULL values, therefore it's your implementation (which you have not shared) that's the issue. – Stu Oct 09 '22 at 12:03
  • @Bharath Babu: This query select all rows from your table plus the computed column final_min_date. Is this not what you are getting or not what you want? – Thorsten Kettner Oct 09 '22 at 18:37