ttist25
asked on
Date "scheduling" in SQL
Hey there!
I'm working in SQL Server 2012 and I'm trying to write a script to set a true false value.
I have two tables. Table A and Table B.
Table A has a person id and a date.
Table B has a person id and start/end dates.
Table B may have multiple rows for a person id with multiple start/end date ranges for the person id in multiple rows.
For example:
Table A
Person ID | Date
--------------------------
1 | 1/1/2011
2 | 2/2/2012
3 | 3/3/2013
--------------------------
Table B
Person ID |Start Date | End Date
--------------------------
1 | 12/1/2010 | 3/3/2011
1 | 2/12/2015 | 2/14/2015
2 | 2/2/2012 | 2/3/2012
2 | 3/3/2011 | 3/4/2014
3 | 1/9/2010 | 2/3/2010
--------------------------
I need to compare the date for the people in Table A with the set of date ranges for the person in Table B. If the date from Table A falls within any of the date ranges for the person's rows in Table B, return true, else return false.
The expected results given the data above would be:
Person ID 1 = True
Person ID 2 = True
Person ID 3 = False
Any help will be greatly appreciated. Thanks!
I'm working in SQL Server 2012 and I'm trying to write a script to set a true false value.
I have two tables. Table A and Table B.
Table A has a person id and a date.
Table B has a person id and start/end dates.
Table B may have multiple rows for a person id with multiple start/end date ranges for the person id in multiple rows.
For example:
Table A
Person ID | Date
--------------------------
1 | 1/1/2011
2 | 2/2/2012
3 | 3/3/2013
--------------------------
Table B
Person ID |Start Date | End Date
--------------------------
1 | 12/1/2010 | 3/3/2011
1 | 2/12/2015 | 2/14/2015
2 | 2/2/2012 | 2/3/2012
2 | 3/3/2011 | 3/4/2014
3 | 1/9/2010 | 2/3/2010
--------------------------
I need to compare the date for the people in Table A with the set of date ranges for the person in Table B. If the date from Table A falls within any of the date ranges for the person's rows in Table B, return true, else return false.
The expected results given the data above would be:
Person ID 1 = True
Person ID 2 = True
Person ID 3 = False
Any help will be greatly appreciated. Thanks!
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
@Scott,
Love the way you want to argue your case so very passionately, hopefully for the intellectual discourse rather than discredit others.
IIF() or CASE is much of a muchness ....Unless of course it is pre SQL2012 where you would need to use CASE, in which case,
I am also inclined to disagree about indexes. My test criteria had thousands of rows, built up from some of those aforementioned "thoughts" and found it did make a difference. I will concede that it might not be the case in this instance, hence the questions.
I have already acknowledged your use of "EXISTS" and agree "at least as efficient as OUTER APPLY" which is why I went with OUTER APPLY based on the testing I did in conjunction with the indexing "thoughts" above.
Love the way you want to argue your case so very passionately, hopefully for the intellectual discourse rather than discredit others.
IIF() or CASE is much of a muchness ....Unless of course it is pre SQL2012 where you would need to use CASE, in which case,
iif(B.PersonID is NOT NULL,'True','False')
-- can be just as easily expressed as
CASE when B.PersonID is NULL then 'False' else 'True' endI am also inclined to disagree about indexes. My test criteria had thousands of rows, built up from some of those aforementioned "thoughts" and found it did make a difference. I will concede that it might not be the case in this instance, hence the questions.
I have already acknowledged your use of "EXISTS" and agree "at least as efficient as OUTER APPLY" which is why I went with OUTER APPLY based on the testing I did in conjunction with the indexing "thoughts" above.
iif(B.PersonID is NOT NULL,'True','False') ... I prefer to think anyone wanting to be a SQL developer would look it up,
The "IS NOT NULL" is unnecessarily complicated as well, forced by the use of OUTER APPLY.
As to looking it up, that's part of the problem. In the middle of an issue with this code at 3AM, I don't want SQL developers forced to look up obscure additions to the language. I think MS is making a big mistake there. They keep this up, it's gonna be like Oracle's PL/SQL, which is so extended it's a complete mish-mash to try to read.
ASKER
Thanks for the solutions guys. I apologize for letting this sit open for so long.
Scott, your solution made the most sense to my simple mind and, although I don't know that it would've made a perceptible difference given my data, I like the use of EXISTS (one and done - waste not want not).
ste5an - thanks for the advice - it'd be a good exercise for me to give the DDL instead of silly ascii art. I'll try to remember that it will likely be faster and better the next time I think I'm in a time crunch.
Mark - your comments kind of gave me a different perspective from which to think so thanks for that as well.
Problem solved - ON TO THE NEXT!
Scott, your solution made the most sense to my simple mind and, although I don't know that it would've made a perceptible difference given my data, I like the use of EXISTS (one and done - waste not want not).
ste5an - thanks for the advice - it'd be a good exercise for me to give the DDL instead of silly ascii art. I'll try to remember that it will likely be faster and better the next time I think I'm in a time crunch.
Mark - your comments kind of gave me a different perspective from which to think so thanks for that as well.
Problem solved - ON TO THE NEXT!
EXISTS determines if any row is found -- exactly what we are doing here.
OUTER APPLY returns a column(s) from another table(s) -- which we don't need here.
EXISTS will be more efficient than COUNT(*) and always at least as efficient as OUTER APPLY, perhaps more in some cases.
And the code is less convoluted. Would this make sense to any non-programmer?
iif(B.PersonID is NOT NULL,'True','False')
Even many SQL developers would not directly understand this, since IIF is a crutch welded into SQL.
WHEN EXISTS() would be initially confusing as well, but it can be worked out just from its key word, EXISTS.