How to create dynamic span periods in Recursive query

General
General covers Articles, Reference documentation, FAQs, Downloads and Blogs that do not belong to a specific subject area. General-purpose Articles about everything and anything
Visitor

How to create dynamic span periods in Recursive query

Hi Guys,

 

   I have  2 below input tables

TEST_MEMBER_START_DATES
EDW_MEMBER_CKSTART_DATE
11/1/2017

 

TEST_ASSESSMENTS  
ASSESSMENT_FACT_CKEDW_MEMBER_CK ASSESSMENT_DATE
111/5/2017
211/25/2017
316/20/2017

Business Rules:

Assessment must be completed within 90 days of previous assessment (or 90 days from the start date if it is the first row)
If an assessment is not completed in time, the next span starts from the due date
If more than one assessment is completed in a span, take the assessment that happened closest to the due date                
Spans must be created until they overlap the current date

 

Using above rules i need to get desired output table like

    
EDW_MEMBER_CKASSESSMENT_FACT_CKASSESSMENT_DATESPAN_STARTDUE_DATECOMPLETED_INDICAOTR
111/25/20171/1/20173/31/20171
1  1/25/20174/24/20170
136/20/20174/24/20177/23/20171
1  6/20/20179/18/20170

 

Can anybody please help me on this to write a query??