Query help

Question

Query help

Guras

SSCertifiable

Points: 6518
More actions
July 11, 2017 at 8:47 am

#338208

I have the following values in table1
Id, startdate, endDate, PersonID, systemID
1, '01/01/2012', '01/12/2015' 1, 1
2 '01/01/2016, null 1, 2
I need to find the systemId for the startdate '01/10/2017' . It should return the second row
Select systemID from Table1 where @date is between startdate and enddate is retruning Null .
--@date is the input parameter

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

Guras - Tuesday, July 11, 2017 8:47 AM
I have the following values in table1
Id, startdate, endDate, PersonID, systemID
1, '01/01/2012', '01/12/2015' 1, 1
2 '01/01/2016, null 1, 2
I need to find the systemId for the startdate '01/10/2017' . It should return the second row
Select systemID from Table1 where @date is between startdate and enddate is retruning Null .
--@date is the input parameter

You've been here long enough to know how the sample data is needed.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

Guras - Tuesday, July 11, 2017 8:47 AM
I have the following values in table1
Id, startdate, endDate, PersonID, systemID
1, '01/01/2012', '01/12/2015' 1, 1
2 '01/01/2016, null 1, 2
I need to find the systemId for the startdate '01/10/2017' . It should return the second row
Select systemID from Table1 where @date is between startdate and enddate is retruning Null .
--@date is the input parameter

-- NULL isn't equal to, not equal to, greater than, or less than, anything else.

-- It's unknown.

SELECT systemID

FROM Table1

WHERE @date >= startdate

AND (@date <= enddate OR enddate IS NULL)

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden