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
5条答案
按热度按时间dgtucam11#
dgjrabp22#
It would affect performance, but I think it is worth just trying the odd looking between:
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.
b5lpy0ml3#
Can you please try:
I have edited the SQL after reading the spec more clearly
I have edited the SQL again. I'm using INNER JOIN now.
pw136qt24#
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
6yt4nkrj5#
at table
history
, setNULL
to9999-12-31