Hi all,
I searched for some answers both on the board and via google, but I'm not finding exactly what I'm looking for. This has come up for me a bunch of times in the past and I never walk away from even successful queries feeling totally satisfied that I 'got it right' in terms of best practices like I can do with SQL (most of the time).
I'm working on a query now that does things in steps and I'm not sure if the details are super important so much as the general question of how to approach situations like this. The query below doesn't work YET, so don't worry too much about de-cyphering (haha, sorry), but what I'd LOVE to do is break it out into really clear steps where what's happening in each step is super easy to see. In a SQL script, I'd probably create temp tables and have intermediary queries so I can inspect the output at each stage. But here, because I am not doing that, I'm struggling with a monolithic query that is going to eat me at some point, I'm sure of it.
Thanks for the ideas and help!
Sam
match (prgs:PROGRAM_STATUS)
with prgs limit 100
with collect(distinct prgs.MBR_DK) as mbr_dks
unwind mbr_dks as mbr_dk
optional match (s:PROGRAM_STATUS {MBR_DK:mbr_dk})-[:UPDATED_TO*]->(e:PROGRAM_STATUS)
where not (()-[]->(s)) and not ((e)-[]->())
with mbr_dk,
case when s is not null
then collect({MBR_DK:s.MBR_DK, CM_CASE_ID:s.CM_CASE_ID, ENROLL_START_DT:s.ENROLL_START_DT, ENROLL_END_DT:coalesce(e.ENROLL_END_DT,date('9999-01-01'))})
else [] end
as records
optional match (s:PROGRAM_STATUS {MBR_DK:mbr_dk})
where not (()-[]->(s)) and not ((s)-[]->())
with
case when s is not null
then records + collect({MBR_DK:s.MBR_DK, CM_CASE_ID:s.CM_CASE_ID, ENROLL_START_DT:s.ENROLL_START_DT, ENROLL_END_DT:coalesce(s.ENROLL_END_DT,date('9999-01-01'))})
else records end
as allRecords
unwind allRecords as records
with records order by records.ENROLL_START_DT, records.ENROLL_END_DT
with collect(records)[0..count(records)-1] as r_collection_head, tail(collect(records)) as r_collection_tail
unwind r_collection_head as r1
with r1, r_collection_tail, apoc.coll.indexOf(r_collection_head, r1) as i
with r1, r_collection_tail[i] as r2
with r1,r2, max(r1.ENROLL_START_DT, r2.ENROLL_START_DT) < min(r1.ENROLL_END_DT, r2.ENROLL_END_DT) as has_overlap
with collect ([{
MBR_DK:r1.MBR_DK
,CM_CASE_ID:r1.CM_CASE_ID + ':' + r2.CM_CASE_ID
,R1_ENROLL_START_DT:r1.ENROLL_START_DT
,R1_ENROLL_END_DT:r1.ENROLL_END_DT
,R2_ENROLL_START_DT:r2.ENROLL_START_DT
,R2_ENROLL_END_DT:r2.ENROLL_END_DT
,HAS_OVERLAP:has_overlap}]) as results
return results