I have a table containing the following:
EffectiveFrom (DateTime) Start of service
EffectiveTo (DateTime) End of service
Now I need to get the total count of patients serviced between a given date range, however (here goes the catch..) if a patient has 30 days or more between the one end of service (EffectiveTo) and the next start of service (EffectiveFrom) it should be counted as 2.
My question is, is there a way to get this count from a query/sql view or there is a need to create a custom function to loop for all records etc..?