asked on

Tables not matching because of different date fields

I have 2 tables which are shown as examples below. The ultimate goal is to get a query that includes all of the fields in the "Member_Table" and the "Risk_type" field in the "Risk_Table".
The problem is that both tables have effective and expiration dates and they don't match and the "Mem_Eff_Dates" can have gaps.
There are 2 fields to join on, the "Prov_ID" and "RISK_PROD_CODE". The problem is that if I just use these joins the results will include duplicates.
The results example below is what the results of the query should look like. The "Member_Table" records have to be split with new "Mem_Eff_Date" and "Mem_Exp_Date" dates
to accomodate the "Risk_Eff_Date" and "Risk_Exp_Date" date fields in the "Risk_Table".

Member_Table
Member_ID      Prov_ID      RISK_PROD_CODE      Mem_Eff_Date      Mem_Exp_Date
R11057276      L029      MEDCALQA        12/1/2017        12/31/2017
R11057276        K112      MEDCALQA        2/1/2018        4/30/2019
R11057276        L029      MEDCALQA        5/1/2019        6/30/2019

Risk_Table
RISK_PROD_CODE      Prov_ID      Risk_Type      Risk_Eff_Date      Risk_Exp_Date
MEDCALQA        L029      SR        7/1/2015        4/30/2018
MEDCALQA        K112      DR        5/1/2018        10/31/2019
MEDCALQA        L029      SR        11/1/2019        12/31/9999

Results
Member_ID      Prov_ID      RISK_PROD_CODE      Mem_Eff_Date      Mem_Exp_Date      Risk_Type
R11057276        L029      MEDCALQA        12/1/2017        12/31/2017        SR
R11057276        L029      MEDCALQA        2/1/2018        4/30/2019        SR
R11057276        K112      MEDCALQA        5/1/2019        6/30/2019        DR

Thanks,
Scott

slightwv (䄆 Netminder)

Based on the sample data provided I don't see how you get:

R11057276 L029 MEDCALQA 2/1/2018 4/30/2019 SR

There isn't any overlap in the risk dates for those member dates.

Also, for the test case example, can you make the following change to help visualize what code goes with what?

MEDCALQA L029 XX 11/1/2019 12/31/9999

Scott Palmer

ASKER

sorry, you are correct. I think I got this one right.

Results
Member_ID      Prov_ID      RISK_PROD_CODE        Mem_Eff_Date      Mem_Exp_Date      Risk_Type
R11057276      L029        MEDCALQA        12/1/2017        12/31/2017        SR
R11057276      L029        MEDCALQA        2/1/2018        4/30/2018        SR
R11057276      L029        MEDCALQA        5/1/2018        4/30/2019        DR
R11057276      K112        MEDCALQA        5/1/2019        6/30/2019        DR

ASKER CERTIFIED SOLUTION

slightwv (䄆 Netminder)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

awking00

Can you describe the criteria and the logic for your expected results? I'm having trouble understanding how you have prov_id K112 with a mem_exp_date of 6/30/2019 and L029 with a mem_exp_date of 4/30/2018 when no such dates from either table exist for those prov_ids.

Andrei Fomitchev

/* Result:
Member_ID Prov_ID RISK_PROD_CODE Mem_Eff_Date Mem_Exp_Date Risk_Type
R11057276 L029 MEDCALQA 2017-12-01 2017-12-31 SR
R11057276 K112 MEDCALQA 2018-02-01 2018-05-01 NULL
R11057276 K112 MEDCALQA 2018-05-01 2019-04-30 DR
R11057276 L029 MEDCALQA 2019-05-01 2019-06-30 NULL
*/

-- Working example :

DECLARE @Member_Table TABLE (Member_ID VarChar(40), Prov_ID VarChar(20),
RISK_PROD_CODE VarChar(20), Mem_Eff_Date Date, Mem_Exp_Date Date)
INSERT INTO @Member_Table VALUES
('R11057276', 'L029', 'MEDCALQA', '12/1/2017', '12/31/2017'),
('R11057276', 'K112', 'MEDCALQA', '2/1/2018', '4/30/2019'),
('R11057276', 'L029', 'MEDCALQA', '5/1/2019', '6/30/2019')
SELECT * FROM @Member_Table

DECLARE @Risk_Table TABLE (RISK_PROD_CODE VarChar(20), Prov_ID VarChar(20),
Risk_Type VarChar(20), Risk_Eff_Date Date, Risk_Exp_Date Date)
INSERT INTO @Risk_Table VALUES
('MEDCALQA', 'L029', 'SR', '7/1/2015', '4/30/2018'),
('MEDCALQA', 'K112', 'DR', '5/1/2018', '10/31/2019'),
('MEDCALQA', 'L029', 'SR', '11/1/2019', '12/31/9999')
SELECT * FROM @Risk_Table

DECLARE @Results TABLE(Member_ID VarChar(40), Prov_ID VarChar(20),
RISK_PROD_CODE VarChar(20), Mem_Eff_Date Date, Mem_Exp_Date Date, Risk_Type VarChar(20))

SELECT * FROM @Results
-- Cases:
-- (1) MMMMMM (2) MMMMMMM (3) MMMMMM (4) MMMMMM (5) MMM MMMM
-- RRRRR RRRRRRR RRR RRRRRRRRRR RRRR
DECLARE cr CURSOR LOCAL FAST_FORWARD READ_ONLY FOR
SELECT Member_ID, Prov_ID, RISK_PROD_CODE, Mem_Eff_Date, Mem_Exp_Date FROM @Member_Table
DECLARE @Member_ID VarChar(40), @Prov_ID VarChar(20),
@RISK_PROD_CODE VarChar(20), @Mem_Eff_Date Date, @Mem_Exp_Date Date, @Risk_Type VarChar(20),
@Risk_Eff_Date Date, @Risk_Exp_Date Date, @Risk_Found Int = 0

OPEN cr
WHILE 1=1 BEGIN
FETCH NEXT FROM cr INTO @Member_ID, @Prov_ID, @RISK_PROD_CODE, @Mem_Eff_Date,
@Mem_Exp_Date
IF @@FETCH_STATUS <> 0 BREAK
SET @Risk_Found = 0
-- (1)
SET @Risk_Type = NULL
SELECT TOP 1 @Risk_Type = Risk_Type, @Risk_Exp_Date = Risk_Exp_Date FROM @Risk_Table
WHERE @Mem_Eff_Date > Risk_Eff_Date
AND @Mem_Eff_Date < Risk_Exp_Date
AND @Mem_Exp_Date > Risk_Exp_Date
AND Prov_ID = @Prov_ID
IF @Risk_Type IS NOT NULL BEGIN
INSERT INTO @Results (Member_ID, Prov_ID, RISK_PROD_CODE, Mem_Eff_Date, Mem_Exp_Date, Risk_Type)
VALUES (@Member_ID, @Prov_ID, @RISK_PROD_CODE, @Mem_Eff_Date, @Risk_Exp_Date, @Risk_Type)
INSERT INTO @Results (Member_ID, Prov_ID, RISK_PROD_CODE, Mem_Eff_Date, Mem_Exp_Date, Risk_Type)
VALUES (@Member_ID, @Prov_ID, @RISK_PROD_CODE, @Risk_Exp_Date, @Mem_Exp_Date, NULL)
SET @Risk_Found = 1
END
-- (2)
SET @Risk_Type = NULL
SELECT TOP 1 @Risk_Type = Risk_Type, @Risk_Exp_Date = Risk_Exp_Date, @Risk_Eff_Date = Risk_Eff_Date
FROM @Risk_Table
WHERE @Mem_Eff_Date < Risk_Eff_Date
AND @Mem_Exp_Date < Risk_Exp_Date
AND @Mem_Exp_Date > Risk_Eff_Date
AND Prov_ID = @Prov_ID
IF @Risk_Type IS NOT NULL BEGIN
INSERT INTO @Results (Member_ID, Prov_ID, RISK_PROD_CODE, Mem_Eff_Date, Mem_Exp_Date, Risk_Type)
VALUES (@Member_ID, @Prov_ID, @RISK_PROD_CODE, @Mem_Eff_Date, @Risk_Eff_Date, NULL)
INSERT INTO @Results (Member_ID, Prov_ID, RISK_PROD_CODE, Mem_Eff_Date, Mem_Exp_Date, Risk_Type)
VALUES (@Member_ID, @Prov_ID, @RISK_PROD_CODE, @Risk_Eff_Date, @Mem_Exp_Date, @Risk_Type)
SET @Risk_Found = 1
END
-- (3)
SET @Risk_Type = NULL
SELECT TOP 1 @Risk_Type = Risk_Type, @Risk_Exp_Date = Risk_Exp_Date, @Risk_Eff_Date = Risk_Eff_Date
FROM @Risk_Table
WHERE @Mem_Eff_Date < Risk_Eff_Date AND @Mem_Exp_Date > Risk_Exp_Date
AND Prov_ID = @Prov_ID
IF @Risk_Type IS NOT NULL BEGIN
INSERT INTO @Results (Member_ID, Prov_ID, RISK_PROD_CODE, Mem_Eff_Date, Mem_Exp_Date, Risk_Type)
VALUES (@Member_ID, @Prov_ID, @RISK_PROD_CODE, @Mem_Eff_Date, @Risk_Eff_Date, NULL)
INSERT INTO @Results (Member_ID, Prov_ID, RISK_PROD_CODE, Mem_Eff_Date, Mem_Exp_Date, Risk_Type)
VALUES (@Member_ID, @Prov_ID, @RISK_PROD_CODE, @Risk_Eff_Date, @Risk_Exp_Date, @Risk_Type)
INSERT INTO @Results (Member_ID, Prov_ID, RISK_PROD_CODE, Mem_Eff_Date, Mem_Exp_Date, Risk_Type)
VALUES (@Member_ID, @Prov_ID, @RISK_PROD_CODE, @Risk_Exp_Date, @Mem_Exp_Date, NULL)
SET @Risk_Found = 1
END
-- (4)
SET @Risk_Type = NULL
SELECT TOP 1 @Risk_Type = Risk_Type, @Risk_Exp_Date = Risk_Exp_Date, @Risk_Eff_Date = Risk_Eff_Date
FROM @Risk_Table
WHERE @Mem_Eff_Date > Risk_Eff_Date AND @Mem_Exp_Date < Risk_Exp_Date
AND Prov_ID = @Prov_ID
IF @Risk_Type IS NOT NULL BEGIN
INSERT INTO @Results (Member_ID, Prov_ID, RISK_PROD_CODE, Mem_Eff_Date, Mem_Exp_Date, Risk_Type)
VALUES (@Member_ID, @Prov_ID, @RISK_PROD_CODE, @Mem_Eff_Date, @Mem_Exp_Date, @Risk_Type)
SET @Risk_Found = 1
END
-- (5)
PRINT @Risk_Found
IF @Risk_Found = 0 BEGIN
INSERT INTO @Results (Member_ID, Prov_ID, RISK_PROD_CODE, Mem_Eff_Date, Mem_Exp_Date, Risk_Type)
VALUES (@Member_ID, @Prov_ID, @RISK_PROD_CODE, @Mem_Eff_Date, @Mem_Exp_Date, NULL)
END
END
CLOSE cr
DEALLOCATE cr
SELECT * FROM @Results

slightwv (䄆 Netminder)

@ Andrei,

First: This was posted in an Oracle Topic Area so we have to assume an Oracle solution.

Second: Even if this was SQL Server, your post sees way over complicated from what I know about SQL Server.

This should be able to be done in plain SQL without added logic.

awking00

Can you just explain the logic used to determine your desired results in plain language?
I agree with slightwv that you may be creating too complicated a procedure when plain sql would likely solve the problem.

slightwv (䄆 Netminder)

Personally, I would rather not have it explained. The data and results should speak for themselves.

I just need the results to make sense given the data provided.

@awking00,

The complicated procedure wasn't from the question Author so I'm not understanding.

Scott Palmer

ASKER

Thanks for all of your time. I have to move on to other work and will get back to this eventually.

Scott

Andrei Fomitchev

Too complex...

1. Slightwv, I am sorry that it is too complex for you - it is simple for me :-)
2. Long does not mean complex. It just have 5 cases that supposed to generate from 1 to 3 rows.
3. Oracle SQL or MS SQL - they both based on the same SQL Standard. I am working on both and there is no much difference for this task.
4. Unfortunately, on the moment I don't have access to Oracle SQL Server, so I implemented and tested it on MS SQL Server, which is free BTW.
5. For sure you can combine 5 cases in 3 (up to 3 rows supposed to be generated), but implementing them separately clearly indicates from where each row was generated.
6. As everyone knows, SQL allows to implement solution in many different ways. My solution is working, but fill you free to implement any different one.

slightwv (䄆 Netminder)

>> it is simple for me :-)

Considering we don't have any definitive expected results, how are you sure? I'm convinced you can do this in a single SQL statement that should be much more efficient. Anyway, the point is moot since you haven't ported it to Oracle.

>> My solution is working

Not for this question since it doesn't port. I also see differences in the "incorrect" expected results poste and what your results show.

Until we get definitive results from the data provided, I'm not sure how you can say anything "works".

>> Long does not mean complex.

Guess we can disagree. More often than not, yes it does.

>> so I implemented and tested it on MS SQL Server, which is free BTW.

SQL Server Express is. Oracle has a free Express version as well.

Don't want to download and install it?

You can test simple things with:

https://dbfiddle.uk/

https://livesql.oracle.com/apex/f?p=590:1000