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!
LVL 1
ttist25Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ste5anSenior DeveloperCommented:
Post table DDL instead of ASCII art. And use unambiguous date literals (yyyymmdd). Also don't use reserved words as object names.

E.g.

DECLARE @A TABLE
    (
        PersonID INT NOT NULL PRIMARY KEY ,
        [Date] DATE NOT NULL
    );

INSERT INTO @A
VALUES ( 1, '20110101' ) ,
       ( 2, '20120202' ) ,
       ( 3, '20130303' );

DECLARE @B TABLE
    (
        PersonID INT NOT NULL ,
        StartDate DATE NOT NULL ,
        EndDate DATE NOT NULL ,
        PRIMARY KEY
        (
            PersonID ,
            StartDate )
    );

INSERT INTO @B
VALUES ( 1, '20100112', '20110303' ) ,
       ( 1, '20150212', '20150214' ) ,
       ( 2, '20120202', '20120203' ) ,
       ( 2, '20110202', '20140304' ) ,
       ( 3, '20100109', '20100203' );

SELECT *
FROM   @A A
       OUTER APPLY (   SELECT COUNT(*) AS Cnt ,
                              CAST(IIF(COUNT(*) = 0, 0, 1) AS BIT)
                       FROM   @B B
                       WHERE  A.PersonID = B.PersonID
                              AND A.Date
                              BETWEEN B.StartDate AND B.EndDate ) RowsInB(Cnt, HasRows);

Open in new window

Using DATE as data type makes using BETWEEN safe.
0
Scott PletcherSenior DBACommented:
Count will be less efficient because it will have to go thru every eligible row.  Exists will exit at the first match.

SELECT A.*,
    CASE WHEN EXISTS(SELECT 1 FROM @B B WHERE B.PersonID = A.PersonID AND A.Date BETWEEN B.StartDate AND B.EndDate)
              THEN 'True' ELSE 'False' END AS Is_In_Table_B
FROM @A A
2

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Mark WillsTopic AdvisorCommented:
@ttist25,

Is PersonID part of the Primary Key ? If not, what is the primary key for each table ?

Do you have any indexes on these tables ? If yes, what indexes do you have ?

Are they big tables ?

The reason why I ask is largely in response to my esteemed colleague's comment above about EXISTS.

While that is very true, I am also looking at the data and assume that there are most likely other columns in [Table B].

Then I started thinking (dangerous as that is) about your requirement and the underlying reason. The thought process went something like:
What if [date] on [Table A] represents 'last updated' or 'submitted',
What if [Table B]  is a transactional table with some other primary key data, Maybe an Identity ?
If [Table B] is transactional in nature with its own PK, then rows will be added to the end.
That may well result in table scans. And we want index seeks if possible.

So, the reason for the Index questions is to clarify some of the above, and suggest an index on [Table B] would be highly beneficial if it had at least personID, and preferably, PersonID + the start and end dates. Something like
create index idx_TableB_PersonID_Start_End on [Table B] (PersonID,StartDate,EndDate)

Open in new window


And given that scenario, would suggest
Select A.PersonID, A.[Date], iif(B.PersonID is NOT NULL,'True','False') as HasTransactions 
from [Table A] A
outer apply (select top 1 personID from [Table B] B where B.PersonID = A.PersonID AND A.[Date] BETWEEN B.StartDate AND B.EndDate) B

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Scott PletcherSenior DBACommented:
No, EXISTS is still far more logical, regardless of table size and/or indexing.

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.
1
Mark WillsTopic AdvisorCommented:
@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,
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' end

Open in new window

I totally disagree with "many SQL developers would not directly understand this" I prefer to think anyone wanting to be a SQL developer would look it up, if in fact, they werent already familiar with the different syntax. It is easy to remember IIF ( boolean_expression, true_value, false_value )  and IF has been around for eons, so should be familiar enough.

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.
0
Scott PletcherSenior DBACommented:
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.
0
ttist25Author Commented:
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!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.