Sql Server- Get the dates from the database between the range supplied

Question

I have an issue to get the records if the FtableID at any point in the range (Start date and End date) has a StatusID=2.

My table Structure is

ID FTableID StatusID AddedOn 75324 53591 1 2019-03-17 06:48:14.490 75325 53591 2 2019-03-18 06:48:14.663 75326 53591 3 2019-03-19 06:54:20.830

@StartDate Datetime='2019/03/17 23:00:00' ,            
@EndDate Datetime='2019/03/20 23:59:59' 

Select ID, FTableID,StatusID,AddedOn from MyTableName where FTableID=53591
And StatusID=2 and AddedOn <= @EndDate

I know my query is wrong and it gives the record even when I pass the @startdate after its status gets changed to 3 (Completed)

I am confused to set the start date filter.

I need to check if this FtableID record is in status id =2 at any point in the range supplied

The record should come If I pass the @StartDate Datetime='2019/03/18 23:00:00', @EndDate Datetime='2019/03/20 23:59:59' because it is in this range it was in the status=2

The record should not come If I pass the @StartDate Datetime='2019/03/19 23:00:00', @EndDate Datetime='2019/03/20 23:59:59' because it was convertted to statusID=3

Please suggest me on this. Thanks in advance.

Uhm, so if you have that @StartDate variable, then why not use it in the SQL? I.e. and AddedOn >= @StartDate Or is the problem that the @StartDate is after that datetime of status 2 ? — LukStorms
– LukStorms, Commented Apr 16, 2019 at 7:26

avinash · Accepted Answer · 2019-04-16 07:35:42Z

0

use this

select * 
from YourTableName
where AddedOn between (@StartDate and @EndDate)
and StatusID=2

answered Apr 16, 2019 at 7:35

avinash

1811 silver badge8 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gordon Linoff · Accepted Answer · 2019-04-16 11:59:04Z

0

One method uses exists.

I would simplify your date arithmetic as well, so:

declare @StartDate datetime '2019-03-17 23:00:00'; declare @EndDate datetime = '2019-03-20'

select t.*
from mytablename t
where exists (select 1
              from mytablename t2
              where t2.ftableid = t.ftableid and
                    t2.statusid = 2 and
                    t2.AddedOn >= @StartDate and
                    t2.AddedOn < @EndDate 
             );

answered Apr 16, 2019 at 11:59

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Collectives™ on Stack Overflow

Sql Server- Get the dates from the database between the range supplied

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related