dimanche 21 avril 2019

How to use if condition in a select query?

I have a start date and end date column in my table and I get a start date and end date as input. I need to set START date as TBL.StartDate if it is after INPUT Start Date and I need to set END date as TBL.EndDate if it is before INPUT End Date. Finally count the days between START and END.

I'm trying to use if statement but I fail when I try to use if in select query.

I'm not sure if this is a correct way of solving this problem.

Declare @Start Date;
Declare @End Date;
Declare @INPUT_StartDate Date;
Declare @INPUT_EndDate Date;

set @INPUT_StartDate = '2019-01-01'; --user input
set @INPUT_EndDate = '2019-04-20'; --user input

select 
TBL.StartDate, 
TBL.EndDate, 
@INPUT_StartDate, 
@INPUT_EndDate,
if(TBL.StartDate < INPUT_StartDate) set @Start = TBL.STARTDate;
else set @Start = INPUT_StartDate;
if(TBL.EndDate < INPUT_EndDate) set @End = INPUT_EndDate;
else set @End = TBL.EndDate;
@DateDiff(day, @Start, @End) as COUNT
 from TBL

I'm expecting that this comparison happens, but i guess i'm using if statement in between select query is not allowed?

Actual results are quite a few syntax errors near if.

Aucun commentaire:

Enregistrer un commentaire