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.