SQL between min of two values

Question

If I have to write this query in SQL Server:

SELECT * 
FROM Table1 
WHERE data BETWEEN interval1 AND interval2

interval1 is fld1 if fld1 >= '2014-1-1' otherwise it's '2014-1-1'
interval2 is fld2 if fld2 is <= '2014-12-31' otherwise it's '2014-12-31'

How can I write it without using variables?

i try

Ksdt_TP022_Data BETWEEN     
                         (SELECT CASE 
                                WHEN sdt_TP019_InizioVal < '2014-1-1' THEN '2014-1-1' 
                                ELSE sdt_TP019_InizioVal 
                                END as b
                        from TP022_Quarto)
                         AND 
                         (SELECT CASE 
                                WHEN sdt_TP019_Fineval > '2014-12-31' THEN '2014-12-31' 
                                WHEN sdt_TP019_Fineval IS NULL THEN '2014-12-31' 
                                ELSE sdt_TP019_Fineval 
                                END as a
                            from TP022_Quarto)

but get compiler error

Which SQL Server version? If you have 2012 or above, you can use IIF(), ie, WHERE data BETWEEN IIF(fld1 >= '2014-1-1', fld1, '2014-1-1') — Kahn, Aug 07 '15 at 09:37

score 2 · Answer 1 · answered Aug 07 '15 at 09:24

2

Try this

select * from Table1 
WHERE data between 
case when fld1 < '20140101' then '20140101' else fld1 end and 
case when fld2 >'20141231 then '20141231' else fld2 end

answered Aug 07 '15 at 09:24

Madhivanan

13,470
1
24
29

t-clausen.dk · Accepted Answer · 2015-08-07T09:34:38.927

1

SELECT * 
FROM 
  Table1 
WHERE
  data BETWEEN interval1 AND coalesce(interval2, '2014-12-31')
  and data between '2014-1-1' and '2014-12-31'

edited Aug 07 '15 at 09:34

answered Aug 07 '15 at 09:32

t-clausen.dk

43,517
12
59
92

i try it before but interval2 can be null and in that case i have to take '2014-12-31' – gt.guybrush Aug 07 '15 at 09:33
@Madhivanan with coalesce works too, but this way it's more nice ;) – gt.guybrush Aug 07 '15 at 09:45

score 0 · Answer 3 · edited May 23 '17 at 12:30

0

Almost the same question is asked here

In short, you could use a UDF (user defined function) or CASE statements.

edited May 23 '17 at 12:30

Community

1
1

answered Aug 07 '15 at 09:24

Jeroen Peeters

66
1
6

SQL between min of two values

3 Answers3