Reputation: 7335
I have a table holding periods and prices, something like this
itemid periodid periodstart periodend price
1 1 2011/01/01 2011/05/01 50.00
1 2 2011/05/02 2011/08/01 80.00
1 3 2011/08/02 2011/12/31 50.00
Now I have a second table that can hold single dates or periods
itemid periodid periodstart periodend price
1 8 2011/07/01 2011/07/17 70.00
Now, how can I do a query that would return the following result?
itemid periodid periodstart periodend price
1 1 2011/01/01 2011/05/01 50.00
1 2 2011/05/02 2011/06/30 80.00 ****
1 8 2011/07/01 2011/07/17 70.00 ***
1 2 2011/07/18 2011/08/01 80.00 ****
1 3 2011/08/02 2011/12/31 50.00
EDIT -- Highlight the fact that the merge is modifying the dates around it
Upvotes: 0
Views: 122
Reputation: 18474
How about something like
select
t1.itemid,t1.periodid,t1.periodstart, coalesce(dateadd(d,-1,t2.periodstart),t1.periodend) as periodend, t1.price
from t1
left outer join t2 on t1.periodstart < t2.periodstart and t1.periodend>t2.periodstart and t1.itemid=t2.itemid
union
select
t2.itemid,t2.periodid,t2.periodstart, t2.periodend, t2.price
from t1
inner join t2 on t1.periodstart < t2.periodstart and t1.periodend>t2.periodstart and t1.itemid=t2.itemid
union
select
t1.itemid,t1.periodid,dateAdd(d,1,t2.periodend), t1.periodend, t1.price
from t1
inner join t2 on t1.periodstart < t2.periodend and t1.periodend>t2.periodend and t1.itemid=t2.itemid
order by periodstart
Upvotes: 1
Reputation: 339
Are you accounting for entries that line up or are you just trying to combine the rows?
if the latter, you could just do a Union
Select itemid, periodid, periodstart, periodend, price
From Table1
Union
Select itemid, periodid, periodstart, periodend, price
From Table2
Upvotes: 0
Reputation: 1078
Use a Union?
Select itemid, periodid,periodstart, periodend,price FROM table1
UNION
SELECT itemid, periodid,periodstart, periodend,price FROM table2
Are you trying to do some sort of join though? the result set doesn't match the two tables you supplied.
Upvotes: 0