I am trying to find a way to get a count of records that were open as of any given date by date. So, if ten records were opened on the 1st of the month and 5 were closed on the 5th and I run the report on the 7th, I need to see:
the open and close dates are separate fields
this is my starting point
FROM CHP_ENCOUNTER EN
(nvl(en.date_locked,sysdate)-en.date_encounter)*24 > 24
and en.date_encounter > to_date('01/01/2012','MM/DD/YYYY')
I would like to be able to expand the date range to know how many were open for a each day in a month or for each month in a year, etc.
I am thinking some analytic function will do this for me but I am not getting my head wrapped around it. I am stuck on what to partition by and how to make records that span several groups (e.g. months) count in each.
Thanks in advance for any help.