Home extract record on basis of date span
Reply: 1

extract record on basis of date span

Mohd Shariq
1#
Mohd Shariq Published in 2017-11-14 04:39:43Z

I have 2 tables MEMBEN and MEMELIG, data is as below.

MEMBEN details:

MEMELIG details:

i want to join two tables in such way that it does not give cartesian product but selective data on basis of datespan of Benefit eff and benefit end date, so consider for above example we take member with id = '1001',

below picture contains all details which i am looking for.

Expected result details

Please help on this i tried using WITH clause but not able to proceed with result.

zarruq
2#
zarruq Reply to 2017-11-14 05:51:30Z

Looking into you sample data, it seems that you are looking for something like below.

SELECT m1.member_id
    ,lis_eff_dt
    ,lis_end_dt
    ,benefit_eff_dt
    ,benefit_end_dt
FROM memben m1
INNER JOIN memlig m2 ON m1.member_id = m2.member_id
    AND m1.member_id = '1001'
    AND m1.lis_eff_dt >= m2.benefit_eff_dt
    AND m1.lis_end_dt <= m2.benefit_end_dt;

The cte version can be.

WITH cte (
    member_id
    ,lis_eff_dt
    ,lis_end_dt
    ,benefit_eff_dt
    ,benefit_end_dt
    )
AS (
    SELECT m1.member_id
        ,lis_eff_dt
        ,lis_end_dt
        ,benefit_eff_dt
        ,benefit_end_dt
    FROM memben m1
    INNER JOIN memlig m2 ON m1.member_id = m2.member_id
        AND m1.member_id = '1001'
        AND m1.lis_eff_dt >= m2.benefit_eff_dt
        AND m1.lis_end_dt <= m2.benefit_end_dt
    )
SELECT *
FROM cte;

You can check the demo here

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.483361 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO