CODEWITHSUNDEEP
sqlsql-server
rakesh
rakesh

Reputation: 13

how to search using a date in one column across 2 date columns?

Any suggestions?

i have a table tblInstallBase where i have to pass the parameter WarrantyEnd date to search if it falls in between dates

MFGWarrantyStartDt1 and MFGWarrantyStartDt2

it is a normal warranty or

if it falls in between

MFGWarrantyStartDt2 and MFGWarrantyExpDt2 it is ExtendedWarranty

Upvotes: 0

Views: 128

Answers (3)

yyyy
yyyy

Reputation: 36

Use a union for the best possible index usage:

select 'Normal' as TypeOf,* from tblInstallBase 
where @givenDate>=MFGWarrantyStartDt1 and @givenDate<=MFGWarrantyStartDt2
union all
select 'extended',* from tblInstallBase 
where @givenDate>=MFGWarrantyStartDt2 and @givenDate<=MFGWarrantyExpDt2

Upvotes: 1

psmitty
psmitty

Reputation: 23

select
case when @WarrantyEnd between MFGWarrantyStartDt1  and MFGWarrantyStartDt2 then 'Normal Warranty'
when @WarrantyEnd between MFGWarrantyStartDt2 and MFGWarrantyExpDt2  then 'Extended Warranty'
else 'Expired' end
from @warantee

Note, if the @WarrantyEnd falls on MFGWarrantyStartDt2 then it is "Normal Warranty" as the first condition will be satisfied

Upvotes: 0

Joel Coehoorn
Joel Coehoorn

Reputation: 416049

SELECT 
   CASE WHEN @WarrantyEnd BETWEEN MFGWarrantyStartDt2 AND MFGWarrantyExpDt2 THEN 'ExtendedWarranty'
    ELSE 'Normal Warranty' END
FROM MyTable

Upvotes: 0

Related Questions