CODEWITHSUNDEEP
sqlsql-server
Ali Ryder
Ali Ryder

Reputation: 91

How to conditionally replace a date with another in SQL?

I have a dataset that has lots of date ranges. I want to build a code that just lets me select, say, days in the past 30 days. However, i want to truncate or exclude the days before the start of my range.

Let's imagine that my dataset is this:

StayID StayStart StayEnd
1      Jan 1     Jan 10
2      Jan 1     Feb 28
3      Feb 1     Feb 10
4      Feb 10    Feb 28

I also have a parameter {?ReportStart} and let's say I set it to Feb 1. First I would exclude all Stays that are before then, but I also want to modify the dates so that all dates are after the selected range. I want the following output:

StayID StayStart StayEnd
2      Feb 1     Feb 28
3      Feb 1     Feb 10
4      Feb 10    Feb 28

I'm thinking I should do something like this, but I'm very new to SQL:

SELECT StayID, 
(SELECT CASE WHEN StayStart < {?ReportStart} THEN {?ReportStart}
    ELSE StayStart
    END
    ) AS StayStart,
StayEnd

Am I on the right track? Any advice would be appreciated!

Upvotes: 1

Views: 168

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

It seems like you want:

select stayId,
       (case when StayStart < {?ReportStart} then {?ReportStart} else StayStart end) as StayStart,
       StayEnd
from t
where stayEnd > {?ReportStart};

Many databases support least() and greatest() as well:

select stayId,
       greatest({?ReportStart}, StayStart) as StayStart,
       StayEnd
from t
where stayEnd > {?ReportStart};

Upvotes: 1

Related Questions