Filter as shown in figure, if date is between the first and the second column shows the value of the third column, did not meet to 0

CodePudding user response:

Condition value is a single value:

CASE the WHEN condition value BETWEEN FBEGINDATE AND FENDDATE END THEN FLOCALAMOUNT ELSE 0

Conditional value is a range:

CASE value WHEN starting conditions & gt; Value=FBEGINDATE AND end conditions & lt;=FENDDATE THEN FLOCALAMOUNT ELSE 0 END

CodePudding user response:

 
If not OBJECT_ID (' # a ') is null 
Drop table # a 
Go 
The create table # a 
(
Kaishi date, 
Jieshu date, 
C money 
) 
GO 
Insert into # a 
Select '2019/05/08', '2019/06/09', '745.00' union all 
Select '2019/05/02', '2019/06/16', '345.00' union all 
Select '2019/05/16', '2019/06/22', '5654.00' union all 
Select '2019/05/11', '2019/06/28', '345.00' union all 
Select '2019/05/06', '2019/06/29', '564.00' 
Go 
Select * from (select kaishi, jieshu, case when '2019/06/20 between kaishi and jieshu then c else' end price from # a) as a where a. Price & gt; 0 

CodePudding user response:

If not OBJECT_ID (' # a ') is null
Drop table # a
Go
The create table # a
(
Kaishi date,
Jieshu date,
C money
)
GO
Insert into # a
Select '2019/05/08', '2019/06/09', '745.00' union all
Select '2019/05/02', '2019/06/16', '345.00' union all
Select '2019/05/16', '2019/06/22', '5654.00' union all
Select '2019/05/11', '2019/06/28', '345.00' union all
Select '2019/05/06', '2019/06/29', '564.00'
Go
- if filtering date in the first column and the second column is displayed between the value of the third column, did not meet to 0
Declare @ dt as smalldatetime
The set @ dt='2019/05/07'
Select *, case when @ dt between kaishi and jieshu then c else 0 end as the from c # a