CODEWITHSUNDEEP
sqlsql-serverdatabasesql-server-2008
Tassadaque
Tassadaque

Reputation: 8199

Join query on Date Range

HI
I have following tables

=========================
Periods
=========================
PeriodID  StartDate   EndDate
1         01-01-11    07-01-11
2         08-01-11    15-01-11

and so on for whole year

=========================
History
=========================
PersonID  From       To
1         01-01-11  05-04-11
2         17-06-11   NULL 
and so on

I want the following output

 StartDate   EndDate   PersonID
  01-01-11    07-01-11    1
  08-01-11    15-01-11    1
  .
  .
  15-04-11   21-04-11     NULL
  .
  .
  15-06-11   21-06-11     2

I need to take join between these two tables but i couldn't figure how join condition will be look like

Ragards

Upvotes: 2

Views: 39113

Answers (5)

DBDWH
DBDWH

Reputation: 1

at table history, set NULL to 9999-12-31

SELECT *
FROM periods a
INNER JOIN history b
ON a.FROM < b.TO AND a.TO > b.FROM

Upvotes: -1

Andriy M
Andriy M

Reputation: 77737

SELECT
  p.StartDate,
  p.EndDate,
  h.PersonID
FROM Periods p
  LEFT JOIN History h
    ON h.[From] BETWEEN p.StartDate AND p.EndDate OR
       p.StartDate BETWEEN h.[From] AND ISNULL(h.[To], '30000101')

Upvotes: 8

clyc
clyc

Reputation: 2450

You need to do a left join in order to show all the periods available even if there are no history entries associated with that period. The criteria would be if the History date was between the period. You would also need to check if the To date was null and include it into your results

SELECT  p.StartDate, p.EndDate, h.PersonId
FROM    Period p
        LEFT JOIN History h
            ON p.StartDate >= h.[From] AND 
            (h.[To] IS NULL OR p.EndDate <= h.[To])

Upvotes: 1

ysrb
ysrb

Reputation: 6740

Can you please try:

SELECT P.StartDate, P.EndDate, H.PersonID
FROM Period P INNER JOIN History H ON P.StartDate <= H.Fromand (P.EndDate >= H.To OR H.To IS NULL)

I have edited the SQL after reading the spec more clearly

I have edited the SQL again. I'm using INNER JOIN now.

Upvotes: 1

MJB
MJB

Reputation: 7686

It would affect performance, but I think it is worth just trying the odd looking between:

select x
from table1 t1
  inner join table2 t2
    on t2.date between t1.startdate and t1.enddate

Whether it works or not will depend on whether this is to be production, or just a one time thing, and how many records are involved. It may be way too slow.

Upvotes: 2

Related Questions