SQLTeam.com | Weblogs | Forums

Merging 3 Tables Into one New Table with a date Overlap

I have 3 tables that I need to merge together. One being Well Information, one being production, one being forecast. I want to insert into a new table using a join or union all. Well Information from the Well Information table, just Production Date, Oil & Gas Production from the Production Table joining on UWI, and just Scenario, Outdate, Oil & Gas from the Forecast Table where you can choose the Scenario you want. I can merge these together but I don't want a table that has the Well Information the Production and Forecast on the same row. What I need is to insert into a new Table the Well Information P_Date as Date Oil & Gas then when Production stops I need the Forecast inserted into the same table with Outdate as Date Oil & Gas forecast. So there is a date overlap between production and forecast and I want Production First so if Production stops in January of 2021 I want Forecast to start in the date column as February 2021. I Hope this makes sense I will attach the examples. The First Being the Well Information Table, the Second table the Production, the Third Table the Forecast and the 4th the Results I am looking for. I hope this makes sense. I have tried a view and a query. Forecast Example_Page_1|647x500

You can simple make 3 querys, make sure they have the same number of fields and the same format. When these 3 querys work you can combine them with union so you get one big query

SELECT ... FROM the Well Information
UNION
SELECT ... FROM the Production table
UNION
SELECT ... FROM the Forcast table

You can find lots of information on the internet, for example the diff between UNION and UNION ALL

What about the Date Overlap? I can do this but I need the P_Date into a Date Column and the OutDate into that same Date Column I need to make sure that the data is doing this.

API LEASE OPERATOR Date OIL GAS
4213543389 PARKS BELL 3921H CONOCOPHILLIPS 1/31/2019 1887 1208
4213543389 PARKS BELL 3921H CONOCOPHILLIPS 2/28/2019 824 542
4213543389 PARKS BELL 3921H CONOCOPHILLIPS 3/31/2019 801 509
4213543389 PARKS BELL 3921H CONOCOPHILLIPS 4/30/2019 779 478
4213543389 PARKS BELL 3921H CONOCOPHILLIPS 5/31/2019 758 450
4213543389 PARKS BELL 3921H CONOCOPHILLIPS 6/30/2019 729 425
4213543389 PARKS BELL 3921H CONOCOPHILLIPS 7/31/2019 712 436
4213543389 PARKS BELL 3921H CONOCOPHILLIPS 8/31/2019 682 410
4213543389 PARKS BELL 3921H CONOCOPHILLIPS 9/30/2019 757 533
4213543389 PARKS BELL 3921H CONOCOPHILLIPS 10/31/2019 884 812
4213543389 PARKS BELL 3921H CONOCOPHILLIPS 11/30/2019 697 700
4213543389 PARKS BELL 3921H CONOCOPHILLIPS 12/31/2019 858 841
4213543389 PARKS BELL 3921H CONOCOPHILLIPS 1/31/2020 706 815
4213543389 PARKS BELL 3921H CONOCOPHILLIPS 2/29/2020 1469 1985
4213543389 PARKS BELL 3921H CONOCOPHILLIPS 3/31/2020 2526 4150
4213543389 PARKS BELL 3921H CONOCOPHILLIPS 4/30/2020 2673 5293
4213543389 PARKS BELL 3921H CONOCOPHILLIPS 5/31/2020 2122 4302
4213543389 PARKS BELL 3921H CONOCOPHILLIPS 6/30/2020 2336 4576
4213543389 PARKS BELL 3921H CONOCOPHILLIPS 7/31/2020 2303 4356
4213543389 PARKS BELL 3921H CONOCOPHILLIPS 8/31/2020 2296 4312
4213543389 PARKS BELL 3921H CONOCOPHILLIPS 9/30/2020 2286 5335
4213543389 PARKS BELL 3921H CONOCOPHILLIPS 10/31/2020 2294 5364
4213543389 PARKS BELL 3921H CONOCOPHILLIPS 11/30/2020 2057 5011
4213543389 PARKS BELL 3921H CONOCOPHILLIPS 12/31/2020 2099 4603
4213543389 PARKS BELL 3921H CONOCOPHILLIPS 1/31/2021 3144 2178
4213543389 PARKS BELL 3921H CONOCOPHILLIPS 2/28/2021 2879 1976
4213543389 PARKS BELL 3921H CONOCOPHILLIPS 3/31/2021 2654 1815
4213543389 PARKS BELL 3921H CONOCOPHILLIPS 4/30/2021 2463 1683
4213543389 PARKS BELL 3921H CONOCOPHILLIPS 5/31/2021 2297 1573
4213543389 PARKS BELL 3921H CONOCOPHILLIPS 6/30/2021 2152 1479
4213543389 PARKS BELL 3921H CONOCOPHILLIPS 7/31/2021 2024 1398
4213543389 PARKS BELL 3921H CONOCOPHILLIPS 8/31/2021 1911 1327
4213543389 PARKS BELL 3921H CONOCOPHILLIPS 9/30/2021 1809 1265
4213543389 PARKS BELL 3921H CONOCOPHILLIPS 10/31/2021 1718 1210
4213543389 PARKS BELL 3921H CONOCOPHILLIPS 11/30/2021 1636 1160
4213543389 PARKS BELL 3921H CONOCOPHILLIPS 12/31/2021 1561 1115
4213543389 PARKS BELL 3921H CONOCOPHILLIPS 1/31/2022 1493 1074
4213543389 PARKS BELL 3921H CONOCOPHILLIPS 2/28/2022 1430 1037
4213543389 PARKS BELL 3921H CONOCOPHILLIPS 3/31/2022 1373 1002
4213543389 PARKS BELL 3921H CONOCOPHILLIPS 4/30/2022 1319 971
4213543389 PARKS BELL 3921H CONOCOPHILLIPS 5/31/2022 1270 942
4213543389 PARKS BELL 3921H CONOCOPHILLIPS 6/30/2022 1225 915
4213543389 PARKS BELL 3921H CONOCOPHILLIPS 7/31/2022 1182 889
4213543389 PARKS BELL 3921H CONOCOPHILLIPS 8/31/2022 1143 866
4213543389 PARKS BELL 3921H CONOCOPHILLIPS 9/30/2022 1106 844
4213543389 PARKS BELL 3921H CONOCOPHILLIPS 10/31/2022 1071 823
4213543389 PARKS BELL 3921H CONOCOPHILLIPS 11/30/2022 1038 804
4213543389 PARKS BELL 3921H CONOCOPHILLIPS 12/31/2022 1008 785

When I tried that earlier it Gave me the Well Information the P_Date Oil & Gas Outdate Oil & Gas on the same line across I need the Dates to Line up with P_Date First and the Outdate next with P_Date taking presidence when new production is added. The dates in production can also be in forecast and I don't want those duplicates I want Monthly Production till it stops and then I want Forecast to Start on the next Month and go forward.

I created a Source Column in Production called MP and one in Forecast called FCST if that helps to say use MP until null then pickup Fcst using a date range maybe??

As long as the number of colums and the formats is the same it shouldn't be any problem.

So I am pulling in 15 columns from the Well Information table joined on UWI to the Monthly Production which I am only pulling in 4 coulumns (Source, P_Date, Oil and Gas), then 5 from the Fcst table (Source, Scenario, Date, Oil and Gas). Does that matter that the formats are not the same. Well Header is just well information, Monthly Production is just monthly Historical Production so each well will be different amount of lines. Joined on UWI to the Fcst table which could be 30 years of forecasted data by well. Does it matter on the Union All. I tried with this but this was only one table and I am trying with 3 to get this done to run a stored procedure to update when new production or forecast is out there to do the above table format. With the exception that I have more well information than what is above I truncated it just to show an example.

please see MERGE

hope this helps

You want to create a new tabel from 3 tables

Select field1 as source, field2 as scenario, field3 as date, field4 as Oil, field5 as Gas from the well
union
Select Source as source, NULL as scenario, Date as date, Oil as Oil, Gas as Gas from the production
etc..

Ok thanks I did this and I got 2 tables joined and updated to the new table but the 3rd table is not working may have to do it in a 2 step process because table 2 has Outdate, Gas and Oil and the 3rd table has Outdate Gas and Oil and I want table 2 inserted into the new table then table 3 where outdate is not equal and then insert into Outdate, Oil and Gas. This is the part I am stuck on I can union them and I can join them however since their is only one Date Column and One Oil Column and One Gas Column I can't make the program insert into the same columns for the 3rd table.