Hello
I hope someone can help me with this query. unfortunately this one's beyond my sql reporting skills!
Please see the attachment for an example of the table I want to report from. This table records the length of time a certain event has been open - in this case, social work care plans.
The person_id and person_name record who the plan is recorded against, the plan_id is the unique identier for the plan, the plan start and end dates record how long each plan has been open for. The Plan length is a calculated field I've added (plan_end_Date - plan_start_date).
What I need is a report that will calculate how long each episode of plans has lasted, adding up the length of contiguous plans by client. For John Smith for example, I want to add the durations of plans 1, 2 and 3 but not plan 4 as there is a break in time of more than one day between the care_plan_end date of plan 3 and the start date of 4. In the example of roger Black Id want the lengths of plans 7 and 8 totaled but I would not want to total the lengths of either plan 6 or 9.
The attachment also has the kind of output that Id want from this report.
For your information, a person can only have one plan open at any point in time so the plan dates for a person would not overlap.
Hope you can help, thanks, tonmachine
Start Free Trial
