CODEWITHSUNDEEP
t-sqlmerge
mko
mko

Reputation: 7335

Merging tables in t-sql

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

Answers (3)

Bob Vale
Bob Vale

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

King
King

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

DarylChymko
DarylChymko

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

Related Questions