there were 4 providers active for the entire month of February.
Provider 006 was active between 01/01/2016 and 05/13/2016 and thus should be counted for the entire month of Jan - April and 13 days in May.
January 4/(4+15/30)
assuming 30 days in a month
Brian Tkatch wrote:I do not understand the scenario.
there were 4 providers active for the entire month of February.
I see 5 in the data above, as all 5 started before February and ended after it.
Brian Tkatch wrote:
Provider 006 was active between 01/01/2016 and 05/13/2016 and thus should be counted for the entire month of Jan - April and 13 days in May.
First, that is not shown in the data above. Second, there is no id "006" in the data.
Brian Tkatch wrote:
January 4/(4+15/30)
Not sure what that means.
Brian Tkatch wrote:
assuming 30 days in a month
Why assume that which can be calculated?
Brian Tkatch wrote:
I just don't understand how you are understanding the data or what you are trying to do with it. Please explain it again.
Brian Tkatch wrote:This ought to be easy to do, once we understand exactly what needs to be done. Maybe it's my mood, but it just hasn't clicked yet. I'm still confused by the month thing though. The first post says, "per provider per month" (which i assume is calendar month) and then, "for the entire month of Jan - April and 13 days in May" (which is a lot more than a calendar month).
Anyway, let's decide all the steps that need to be done in a simple list, and we'll work out the answer to each one. We can join all the steps together at the end. I really just want a list of steps. For example:
1) Find all providers active for each calendar month.
2) For each, calculate average x in y
3) ...
As for the date functions, each RDBMS does it differently, but basically, there's always a way to figure out the last day of the month (and hence how many days are in it) either directly or indirectly (indirectly being first day of the following month - 1 day) and then subtracting the two dates directly (as dates) or indirectly (by extracting the days, or day of year first). A google search ought to find plenty of examples when we're up to that step.
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime. |