CODEWITHSUNDEEP
sql-serverdatejoinrange
BohdanZPM
BohdanZPM

Reputation: 755

SQL: How to join two tables by their date ranges

I have a table with a history of assigning Eployee Type to a Work item, like follows:

| WorkItemID | EmployeeTypeID | ValidFrom               | ValidTo                 |
| 1          | 1              | 2017-03-01 12:19:20.000 | 2017-03-05 14:11:20.000 |
| 1          | 1              | 2017-03-10 17:00:20.000 | NULL                    |
| 1          | 2              | 2017-05-12 12:19:20.000 | 2017-05-29 14:11:20.000 |
| 1          | 2              | 2017-07-01 12:19:20.000 | NULL                    |
| 2          | 1              | 2017-01-01 15:19:20.000 | 2017-03-01 11:29:20.000 |
| 2          | 1              | 2017-04-03 16:19:20.000 | NULL                    |

NULL means that there's no End date for the last assignment and it is still valid. I also have a table with a history of assigning Eployee Type to an Employee:

| EmployeeID | EmployeeTypeID | ValidFrom               | ValidTo                 |
| 1          | 1              | 2017-01-01 12:19:20.000 | 2017-03-05 14:11:20.000 |
| 1          | 2              | 2017-03-05 14:11:20.000 | NULL                    |
| 2          | 1              | 2016-05-05 15:19:20.000 | 2017-03-01 11:29:20.000 |
| 2          | 2              | 2017-03-01 11:29:20.000 | NULL                    |

For a given EmployeeID and WorkItemID, I need to select a minimum date within these date ranges where their EmployeeTypeID matched (if there is any).

For example, for EmployeeID = 1 And WorkItemID = 1 the minimum date when their Employeetypes matched is 2017-03-01 (disregard the time part).

How do I write an SQL query to join these two tables correctly and select the desired date?

Upvotes: 0

Views: 6855

Answers (4)

Kenneth Kong
Kenneth Kong

Reputation: 1

Don't use >=, <=, = or between when comparing datetime fields. Since all of the mention operator would check against time as well. You would want to use datediff to check against the smallest interval according to your needs

select 
  Min_Overlap_Per_Section = (select MAX(ValidFrom)
    FROM (VALUES (t1.ValidFrom), (t2.ValidFrom)) as ValidFrom(ValidFrom))
, Section_From = (select MAX(ValidFrom)
    FROM (VALUES (t1.ValidFrom), (t2.ValidFrom)) as ValidFrom(ValidFrom))
, Section_To = (select MIN(ValidTo)
    FROM (VALUES (t1.ValidTo), (t2.ValidTo)) as ValidTo(ValidTo))
from Table1
JOIN Table2 t2 ON t1.EmployeeTypeID = t2.EmployeeTypeID 
where (
    datediff(day, t1.ValidFrom, t2.ValidTo) >= 0 
    or t2.ValidTo IS NULL
)
and (
    datediff(day, t2.ValidFrom, t1.ValidTo) >= 0
    or t1.ValidTo IS NULL
)

Upvotes: 0

BohdanZPM
BohdanZPM

Reputation: 755

Actually, my variant still does not work correctly. The @MinDate1 and @MinDate2 should be compared by each EmployeeTypeID one by one. There it was compared independently.

Here is correct variant of solving this problem:

SELECT MIN(CASE WHEN t1.ValidFrom > t2.ValidFrom THEN t1.ValidFrom ELSE t2.ValidFrom END) AS MinOverlapDate
            FROM Table1 t1
JOIN Table2 t2 ON t1.EmployeeTypeID = t2.EmployeeTypeID
WHERE t1.WorkItemID = 1 AND t2.EmployeeID = 1
AND (t1.ValidFrom <= t2.ValidTo OR t2.ValidTo IS NULL)
AND (t1.ValidTo >= t2.ValidFrom OR t1.ValidTo IS NULL)

Upvotes: 0

BohdanZPM
BohdanZPM

Reputation: 755

The following way appeared to be correct for me: Firstly, I select Min Date from table 1 that match with table 2 by date ranges and they should overlap as well:

DECLARE @MinDate1 datetime
DECLARE @MinDate2 datetime

SELECT @MinDate1 = 
(SELECT MIN(t1.ValidFrom)
FROM Table1 t1
JOIN Table2 t2 ON t1.EmployeeTypeID = t2.EmployeeTypeID
WHERE t1.WorkItemID = 1 AND t2.EmployeeID = 1
AND (t1.ValidFrom <= t2.ValidTo OR t2.ValidTo IS NULL)
AND (t1.ValidTo >= t2.ValidFrom OR t1.ValidTo IS NULL))

Then I select Min Date from table 2 that match with table 1 by date ranges and they should overlap as well:

SELECT @MinDate2 = 
(SELECT MIN(t2.ValidFrom)
FROM Table1 t1
JOIN Table2 t2 ON t1.EmployeeTypeID = t2.EmployeeTypeID 
WHERE t1.WorkItemID = 1 AND t2.EmployeeID = 1
AND (t1.ValidFrom <= t2.ValidTo OR t2.ValidTo IS NULL)
AND (t1.ValidTo >= t2.ValidFrom OR t1.ValidTo IS NULL))

And finaly, I select the max date of two which would be the min date when the two ranges actually overlap and have the same EmployeeTypeID

SELECT CASE WHEN @MinDate1 > @MinDate2 THEN @MinDate1 ELSE @MinDate2 END AS MinOverlapDate

The output would be:

| MinOverlapDate          |
| 2017-03-01 12:19:20.000 |

Upvotes: 1

Zdeněk Bednař&#237;k
Zdeněk Bednař&#237;k

Reputation: 257

So it should be something like this:

SELECT MIN(Date)
FROM table1 t1
JOIN table2 t2 ON t1.EmployeeTypeID = t2.EmployeeTypeID 
WHERE t1.EmployeeID = givenValue AND t2.WorkitemID = givenValue

But again if you dont know from which table the result goes you cant write a query for that.

What you should do is do at least 3 tables or maybe more

  1. Would contain Employee informations
  2. Items jobs dates whatever is connected to WORK
  3. Some connection between them (Emp 1 has Work 2) (Emp 2 has Work 4) and so on

You CANNOT have same values in two tables without knowing from which one you want to get tha data!

OR .. You can do it into one table.

Columns: WorkItem | EmployeeID | EmployeeType | Date | Date

Upvotes: 0

Related Questions