Home extract record on basis of date span
Reply: 0

extract record on basis of date span

user7489 Published in September 19, 2018, 6:54 pm

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.

share|improve this question
  • I know the below query is one solution : select A.MEMBER_ID,A.NAME,A.BENEFIT_EFF_DT,A.BENEFIT_END_DT,B.LIS_EFF_DT,B.LIS_END_DT,B.LIS_CO_PAY from MEMELIG A inner join MEMBEN B on A.MEMBER_ID = B.MEMBER_ID where A.MEMBER_ID = '1001' and LIS_EFF_DT between BENEFIT_EFF_DT and BENEFIT_END_DT and LIS_END_DT between BENEFIT_EFF_DT and BENEFIT_END_DT but i need one generic query using with clause so that if more than one conditions encountered then can modify the subquery of with clause without impacting current query. – Mohd Shariq Nov 14 '17 at 4:47
  • Do not put code or additional information in comments. Edit your question and add the code as formatted text, no screen shots please – a_horse_with_no_name Nov 14 '17 at 7:19

1 Answer 1

active oldest votes
up vote 0 down vote
You need to login account before you can post.

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

© 2016 Powered by mzan.com design MATCHINFO