[SQL] SQL To find length of medication use

I'm looking to see for a SQL code or logic for a person on medication use. The situation is if a person has multiple medications prescribed during the same time there is an overlap, if they are prescribed one after the other there is no overlap and its a continuous length of medication use, or there could be situation of gap between two prescriptions. I have a list of multiple medications with multiple start and end dates. So for a given year how can I calculate the total days of meds use in these situations i described above. Any help is appreciated.

I've tried using MIN or MAX, or cross apply or self join none of them work. Below is an example. The sample shows the the use of medications for the entire year. But For the month of March there was no meds. So how can we calculate so that overlap, gaps and continuous start and stop dates are accounted for?



Continue reading...