This SQL query is creating a recursive Common Table Expression (CTE) named fill_end. It processes a table named final to calculate additional information about prescription fills, such as the first and last doses of a prescription period and an adjusted days supply. Initial CTE (Base Case) The first part of the CTE initializes the recursive process: Select Columns: It selects all columns from the final table along with some calculated fields: first_dose: This is set to the fill date (fill_sold_dt) of the prescription. last_dose: This is calculated as the minimum of either the fill_sold_dt plus the fill_days_supply minus 1 or the maximum date from a table named tbl. Essentially, it ensures that the last dose does not exceed the maximum date in tbl. span: A constant value of 1, used to track spans of prescription fills. Filter Rows: It only considers the first row (rownum = 1) from the final table for each patient and medication combination. Calculate adj_daysupp: This calculates the adjusted days supply as the difference between last_dose and first_dose plus 1.   Recursive Part The recursive part of the CTE processes subsequent prescription fills: Select Columns: It selects all columns from the final table along with some recalculated fields: first_dose: This is the later of either the current fill date (fill_sold_dt) or the day after the last dose from the previous prescription fill (b.last_dose + 1), ensuring it does not exceed the maximum date plus one from tbl. last_dose: This is the minimum of either the calculated first_dose plus the fill_days_supply minus 1 or the maximum date from tbl. span: This increments by 1 if the gap between the current fill date and the previous last dose is 60 days or more, otherwise, it remains the same as the previous span. Join Condition: The join condition matches prescription fills for the same patient (pat_id), same medication (gpi10), and consecutive rows (rownum and rownum + 1). Calculate adj_daysupp: This calculates the adjusted days supply as the difference between last_dose and first_dose plus 1.   Summary The CTE fill_end thus recursively processes each patient's prescription fills, adjusting the first_dose and last_dose dates to account for potential overlaps and gaps. It also calculates an adj_daysupp to reflect the adjusted days supply for each prescription period. This recursive CTE helps in creating a detailed and adjusted timeline of prescription fills for analysis. ... View more