CODEWITHSUNDEEP
sqlsql-server
DannyBland
DannyBland

Reputation: 493

How to use the minimum date from three available - SQL

I'm trying to plug a formula into a query to pull back how much should have run on a particular contract.

The formula itself is quite simple, but I can't find anywhere how to take the minimum date between 3, based on each record separately.

I need to calculate which is the earliest of Term_date, Suspend_date and today's date, some of which may be NULL, on each contract.

Upvotes: 0

Views: 177

Answers (4)

Gordon Linoff
Gordon Linoff

Reputation: 1270513

And interesting way to approach this is to use cross apply:

select t.contractid, mindte
from table t cross apply
     (select min(dte) as mindte
      from (values(t.term_date), (t.suspend_date), (getdate())) d(dte)
     ) d;

Upvotes: 3

Paul Williams
Paul Williams

Reputation: 17030

Here is a method using cross apply to generate a work table from which you can get the minimum date:

-- mock table with sample testing data
declare @MyTable table
(
    id int identity(1,1) primary key clustered,
    term_date datetime null,
    suspend_date datetime null
)
insert into @MyTable (term_date, suspend_date)
select null, null
union all select '1/1/2015', null
union all select null, '1/2/2015'
union all select '1/3/2015', '1/3/2015'
union all select '1/4/2015', '1/5/2015'
union all select '1/6/2015', '1/5/2015'

select * from @MyTable

select datevalues.id, min([date])
from @MyTable
cross apply
(
    values (id, term_date), (id, suspend_date), (id, getdate())
) datevalues(id, [date])
group by datevalues.id

Upvotes: 0

Matt
Matt

Reputation: 1155

I know a CASE statement will be suggested, but I thought I'd try something different:

;WITH cte (RecordID, CheckDate) AS
( SELECT RecordID, Term_date FROM sourcetable UNION ALL
  SELECT RecordID, Suspend_date FROM sourcetable UNION ALL
  SELECT RecordID, GETDATE() FROM sourcetable )
SELECT src.RecordID, src.Field1, src.Field2, MinDate = MIN(cte.CheckDate)
FROM sourcetable src
LEFT JOIN cte ON cte.RecordID = src.RecordID
GROUP BY src.RecordID, src.Field1, src.Field2

Upvotes: 0

Dan
Dan

Reputation: 10680

CASE 
    WHEN Term_date < Suspend_date AND Term_date < GETDATE() THEN Term_date
    WHEN Suspend_date < GETDATE() THEN Suspend_date
    ELSE GETDATE()
END AS MinimumDate

Upvotes: 2

Related Questions