Merging Multilple Tables

Question

I want to take 3 tables that are linked together by API and merge them into a new table with a stored procedure in Sql Server Management Studio. I also attached an example.

I want to take the following information from Table 1:

Well
API
Operator
State
County

I want to take the following from Table 2:

Souce
Date
Oil
Gas

I want to take the following from Table 3:

Souce
Date
Gross_Oil
Gross_Gas

The only problem is there is a date overlap for dates. I want to insert all of the items from Table 1 and Table 2 into the new table and then I only want the 4 columns from table 3 where the dates don't overlap.

For Example:

Well Api Operator County State Source Date Oil_Production Gas_Production Oil_Forecast Gas_Forecast

Harbor 1 4256820125 Devon Howard Tx MP 1/2020 546 948
Harbor 1 4256820125 Devon Howard Tx MP 2/2020 572 1001
Harbor 1 4256820125 Devon Howard Tx MP 3/2020 506 1119
Harbor 1 4256820125 Devon Howard Tx MP 4/2020 499 968
Harbor 1 4256820125 Devon Howard Tx MP 5/2020 599 1165
Harbor 1 4256820125 Devon Howard Tx MP 6/2020 544 1065
Harbor 1 4256820125 Devon Howard Tx MP 7/2020 492 1267
Harbor 1 4256820125 Devon Howard Tx MP 8/2020 546 988
Harbor 1 4256820125 Devon Howard Tx MP 9/2020 566 932
Harbor 1 4256820125 Devon Howard Tx MP 10/2020 496 1003
Harbor 1 4256820125 Devon Howard Tx MP 11/2020 465 1075
Harbor 1 4256820125 Devon Howard Tx MP 12/2020 598 1222
Harbor 1 4256820125 Devon Howard Tx FCST 1/2021 666 897
Harbor 1 4256820125 Devon Howard Tx FCST 2/2021 625 798
Harbor 1 4256820125 Devon Howard Tx FCST 3/2021 614 965
Harbor 1 4256820125 Devon Howard Tx FCST 4/2021 600 865
Harbor 1 4256820125 Devon Howard Tx FCST 5/2021 597 845
Harbor 1 4256820125 Devon Howard Tx FCST 6/2021 532 802
Harbor 1 4256820125 Devon Howard Tx FCST 7/2021 512 812
Harbor 1 4256820125 Devon Howard Tx FCST 8/2021 500 784
Harbor 1 4256820125 Devon Howard Tx FCST 9/2021 495 784
Harbor 1 4256820125 Devon Howard Tx FCST 10/2021 465 684
Harbor 1 4256820125 Devon Howard Tx FCST 11/2021 432 656
Harbor 1 4256820125 Devon Howard Tx FCST 12/2021 412 632
Harbor 1 4256820125 Devon Howard Tx FCST 3/2021 400 444

Is it possible to do this I tried an insert into but not sure how to handle inserting into the same column after production to insert the date source and forecast only when the production stops. Hopefully this makes sense. Thanks so much I have exhausted trying and I can get Table 1 and 2 but table 3 is not coming in correctly.

Answer 1

Hi @Kati_14 ，

Welcome to Microsoft Q&A!

For this type of problem we recommend that you post CREATE TABLE statements for your tables together with INSERT statements with sample data as below.

create table WELLS    
(lease varchar(20),  
API int,  
Operator varchar(20),  
State varchar(20),  
County varchar(20))  
  
insert into WELLS values  
('Harbor', 1, '4256820125', 'Devon Howard', 'Tx')  
  
create table PRODUCTION   
(API int,  
SOURCE varchar(20),  
OUTDATE date,  
GAS int,  
OIL int)  
  
insert into PRODUCTION values  
(1,'MP','1/1/2020', 546, 948),  
(1,'MP','2/1/2020', 572, 1001),  
(1,'MP','3/1/2020', 506, 1119),  
(1,'MP','4/1/2020', 499, 968),  
(1,'MP','5/1/2020', 599, 1165),  
(1,'MP','6/1/2020', 544, 1065),  
(1,'MP','7/1/2020', 492, 1267),  
(1,'MP','8/1/2020', 546, 988),  
(1,'MP','9/1/2020', 566, 932),  
(1,'MP','10/1/2020', 496, 1103),  
(1,'MP','11/1/2020', 465, 1075),  
(1,'MP','12/1/2020', 598, 1222),  
(1,'MP','1/1/2021', 565, 1026)  
  
create table MONTHLY    
(API int,  
SOURCE varchar(20),  
OUTDATE date,  
Gross_Oil int,  
Gross_gas int)  
  
insert into MONTHLY values  
(1,'FCST','1/1/2021', 532, 1019),  
(1,'FCST','2/1/2021', 625, 798),  
(1,'FCST','3/1/2021', 614, 965),  
(1,'FCST','4/1/2021', 600, 865),  
(1,'FCST','5/1/2021', 597, 845),  
(1,'FCST','6/1/2021', 532, 802),  
(1,'FCST','7/1/2021', 512, 812),  
(1,'FCST','8/1/2021', 500, 784),  
(1,'FCST','9/1/2021', 495, 784),  
(1,'FCST','10/1/2021', 465, 684),  
(1,'FCST','11/1/2021', 432, 656),  
(1,'FCST','12/1/2021', 412, 632),  
(1,'FCST','3/1/2021', 400, 444)  

Please provide more details about the duplicated rows of '3/1/2021'. Need we remain or remove the duplicated row?

Please refer below statement and check whether it is helpful:

;with cte as (  
select ac.[LEASE] as WELL,  
ac.[API],  
ac.[OPERATOR],  
ac.[STATE],  
ac.[COUNTY],  
mp.[SOURCE],  
mp.[OUTDATE],   
mp.[GAS] as GAS_PROD,  
mp.[OIL] as OIL_PROD,  
NULL OIL_FCST,  
NULL GAS_FCST  
from WELLS ac   
inner join PRODUCTION mp  
on ac.API = mp.API  
union   
select ac.[LEASE] as WELL,  
ac.[API],  
ac.[OPERATOR],  
ac.[STATE],  
ac.[COUNTY],  
fcst.SOURCE,  
fcst.OUTDATE,  
NULL GAS_PROD,  
NULL OIL_PROD,  
fcst.Gross_Gas,  
fcst.Gross_Oil  
from WELLS ac   
INNER JOIN MONTHLY fcst  
ON ac.API = fcst.API  
),cte1 as (  
select *,ROW_NUMBER() over (partition by outdate order by (IIF(source='MP',1,2)),GAS_FCST desc) rn  
 from cte)  
 select well,api,Operator,State,County,SOURCE,OUTDATE,GAS_PROD,OIL_PROD,GAS_FCST,OIL_FCST  
  from cte1 where rn=1  

Output:

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Production Table Merge
[86404-well-table-plus-production-merge.pdf][1]

Forecast Table Merge
[86395-well-table-plus-forecast-merge.pdf][2]

Desired Outcome:
[86368-desired-outcome.pdf][3]

I am trying to merge 3 tables into one table and if there is a Production Date in Outdate on the Production Table or second table then ignore it in the forecast table Third Table and insert all the rest of the records. For example if there is Production till 1/2021 then I want to insert the Forecast after the production starting on 2/28/2021 forward. I am using SSMS version 18.

Answer 3

The well table is not large the Production and Forecast Tables are large I only included one well that was merged from the Well Table to the Production Table via API, The Well Table to the Forecast Table via API and the Desired outcome.

CREATE PROCEDURE [dbo].[FORECAST_master]

AS
BEGIN
SET NOCOUNT ON;
TRUNCATE TABLE [dbo].[FORECAST_PROVED]

INSERT INTO [dbo].[FORECAST_PROVED]
[LEASE],
[API],
[OPERATOR],
[COUNTY],
[[SOURCE],
[OUTDATE],
[GROSS_OIL_PROD],
[GROSS_GAS_PROD],
[GROSS_OIL_FCST],
[GROSS_GAS_FCST])

SELECT DISTINCT ac.[LEASE] as WELL,
ac.[API],
ac.[OPERATOR],
ac.[STATE],
ac.[COUNTY],
mp.[SOURCE],
mp.[OUTDATE], (SAME COLUMN AS fcst.DATE)
mp.[GAS] as GROSS_GAS_PROD,
mp.[OIL] as GROSS_OIL_PROD,
fcst.[OUTDATE], (SAME COLUMN AS MP.SOURCE)
fcst.[GROSS_OIL] as GROSS_OIL_FCST,
fcst.GROSS_GAS as GROSS_GAS_FCST

FROM WELLS ac
INNER JOIN PRODUCTION mp
ON ac.API = mp.API
INNER JOIN MONTHLY fcst
ON ac.API = fcst.API
ORDER BY LEASE, SOURCE, OUTDATE
END

This would work if MP.SOURCE and FCST.SOURCE were not the same column and MP.OUTDATE and FCST.OUTDATE where not the same column. But since I want to chart one date and show Gas Production, and Oil Production then Gas Forecast and Oil Forecast .

Example Chart to go with Desired Outcome
86386-graph-example.pdf

Answer 4

Great Melissa this worked great. One last question. This worked separately, but if you wanted to do this like the example below could you?
I

The only difference is you would insert mp into the Date Column, source, oil and gas from the Production table and when there is no more Monthly Production you would insert fcst into the Source, Date, Oil and Gas Column. Please let me know if this makes sense.

Answer 5

Hi @Kati_14 ，

Please refer below updated one and check whether it is helpful to you:

;with cte as (  
 select ac.[LEASE] as WELL,  
 ac.[API],  
 ac.[OPERATOR],  
 ac.[STATE],  
 ac.[COUNTY],  
 mp.[SOURCE],  
 mp.[OUTDATE],   
 mp.[GAS] as GAS,  
 mp.[OIL] as OIL  
 from WELLS ac   
 inner join PRODUCTION mp  
 on ac.API = mp.API  
 union   
 select ac.[LEASE] as WELL,  
 ac.[API],  
 ac.[OPERATOR],  
 ac.[STATE],  
 ac.[COUNTY],  
 fcst.SOURCE,  
 fcst.OUTDATE,  
 fcst.Gross_Gas as GAS,  
 fcst.Gross_Oil as OIL  
 from WELLS ac   
 INNER JOIN MONTHLY fcst  
 ON ac.API = fcst.API  
 ),cte1 as (  
 select *,ROW_NUMBER() over (partition by outdate order by (IIF(source='MP',1,2)),GAS desc) rn  
  from cte)  
  select well,api,Operator,State,County,SOURCE,OUTDATE,GAS,OIL  
   from cte1 where rn=1  

Output:

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.