Count of pending records by date(Day/Month/Year)

DaveJBaldwin
DaveJBaldwin used Ask the Experts™
on
Hello,
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:
1st 10
2nd 10
3rd 10
4th 10
5th 10
6th 5
7th 5
the open and close dates are separate fields

this is my starting point
SELECT
  EN.PROVIDER_RSN PROVIDER_ID
  ,EN.CHP_ENCOUNTER_ID
  ,en.date_encounter
  ,en.date_locked
  ,round((nvl(en.date_locked,sysdate)-en.date_encounter)*24,0) HOURS_OLD
  FROM CHP_ENCOUNTER EN
  WHERE
    (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.
Dave
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2011
Top Expert 2012

Commented:
how are "open" and "closed" determined?

is there one row for each status?

can something open/close/open/close/open/close?  If so, in what frequency?
if something opens and closes in the same day, what is that status in your summary?

Author

Commented:
All status are recorded in a single row. open is in Date_encounter closed is in Date_locked.
Once date locked is set it cannot be reversed
PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
Perhaps this will help the discussion.
Using COUNT() OVER() partitioned by TRUNC( a date , 'YYYY') or TRUNC( a date , 'MM')

SELECT
      EN.PROVIDER_RSN                                                        AS PROVIDER_ID
    , EN.CHP_ENCOUNTER_ID
    , en.date_encounter
    , en.date_locked
    , ROUND((nvl(en.date_locked, sysdate) - en.date_encounter) * 24, 0)      AS HOURS_OLD

    , TRUNC(date_encounter,'YYYY') X1
    , TRUNC(date_encounter,'MM')   X2
    , TRUNC(date_locked,'MM')      X3

    , COUNT(date_encounter) OVER (PARTITION BY TRUNC(date_encounter,'MM'))   AS DE_MM_COUNT
    , COUNT(date_locked)    OVER (PARTITION BY TRUNC(date_locked,'MM'))      AS DL_MM_COUNT
                                  
    , COUNT(date_encounter) OVER (PARTITION BY TRUNC(date_encounter,'YYYY')) AS DE_YYYY_COUNT
    , COUNT(date_locked)    OVER (PARTITION BY TRUNC(date_locked,'YYYY'))    AS DL_YYYY_COUNT
                                  
FROM CHP_ENCOUNTER EN
WHERE (nvl(en.date_locked, sysdate) - en.date_encounter) * 24 > 24
      AND en.date_encounter > to_date('01/01/2012', 'MM/DD/YYYY')
;

Open in new window


demo http://sqlfiddle.com/#!4/9e34f/2

setup:
**Oracle 11g R2 Schema Setup**:

    
    
    CREATE TABLE CHP_ENCOUNTER
    	(PROVIDER_RSN int, CHP_ENCOUNTER_ID int, DATE_ENCOUNTER DATE, DATE_LOCKED DATE)
    ;
    
    INSERT ALL 
    	INTO CHP_ENCOUNTER (PROVIDER_RSN, CHP_ENCOUNTER_ID, DATE_ENCOUNTER, DATE_LOCKED)
    		 VALUES (1, 1, TO_DATE('2014-04-01','YYYY-MM-DD'), TO_DATE('2014-04-02','YYYY-MM-DD'))
    	INTO CHP_ENCOUNTER (PROVIDER_RSN, CHP_ENCOUNTER_ID, DATE_ENCOUNTER, DATE_LOCKED)
    		 VALUES (1, 1, TO_DATE('2014-04-11','YYYY-MM-DD'), TO_DATE('2014-04-12','YYYY-MM-DD'))
    	INTO CHP_ENCOUNTER (PROVIDER_RSN, CHP_ENCOUNTER_ID, DATE_ENCOUNTER, DATE_LOCKED)
    		 VALUES (1, 1, TO_DATE('2014-04-21','YYYY-MM-DD'), TO_DATE('2014-04-22','YYYY-MM-DD'))
    	INTO CHP_ENCOUNTER (PROVIDER_RSN, CHP_ENCOUNTER_ID, DATE_ENCOUNTER, DATE_LOCKED)
    		 VALUES (1, 1, TO_DATE('2014-05-01','YYYY-MM-DD'), TO_DATE('2014-05-02','YYYY-MM-DD'))
    	INTO CHP_ENCOUNTER (PROVIDER_RSN, CHP_ENCOUNTER_ID, DATE_ENCOUNTER, DATE_LOCKED)
    		 VALUES (1, 1, TO_DATE('2014-05-11','YYYY-MM-DD'), TO_DATE('2014-05-12','YYYY-MM-DD'))
    	INTO CHP_ENCOUNTER (PROVIDER_RSN, CHP_ENCOUNTER_ID, DATE_ENCOUNTER, DATE_LOCKED)
    		 VALUES (1, 1, TO_DATE('2014-05-21','YYYY-MM-DD'), TO_DATE('2014-05-22','YYYY-MM-DD'))
    	INTO CHP_ENCOUNTER (PROVIDER_RSN, CHP_ENCOUNTER_ID, DATE_ENCOUNTER, DATE_LOCKED)
    		 VALUES (1, 1, TO_DATE('2014-06-01','YYYY-MM-DD'), TO_DATE('2014-06-02','YYYY-MM-DD'))
    	INTO CHP_ENCOUNTER (PROVIDER_RSN, CHP_ENCOUNTER_ID, DATE_ENCOUNTER, DATE_LOCKED)
    		 VALUES (1, 1, TO_DATE('2014-06-11','YYYY-MM-DD'), TO_DATE('2014-06-12','YYYY-MM-DD'))
    	INTO CHP_ENCOUNTER (PROVIDER_RSN, CHP_ENCOUNTER_ID, DATE_ENCOUNTER, DATE_LOCKED)
    		 VALUES (1, 1, TO_DATE('2014-06-21','YYYY-MM-DD'), TO_DATE('2014-06-22','YYYY-MM-DD'))
    	INTO CHP_ENCOUNTER (PROVIDER_RSN, CHP_ENCOUNTER_ID, DATE_ENCOUNTER, DATE_LOCKED)
    		 VALUES (1, 1, TO_DATE('2014-07-01','YYYY-MM-DD'), TO_DATE('2014-07-02','YYYY-MM-DD'))
    	INTO CHP_ENCOUNTER (PROVIDER_RSN, CHP_ENCOUNTER_ID, DATE_ENCOUNTER, DATE_LOCKED)
    		 VALUES (1, 1, TO_DATE('2014-07-11','YYYY-MM-DD'), TO_DATE('2014-07-12','YYYY-MM-DD'))
    	INTO CHP_ENCOUNTER (PROVIDER_RSN, CHP_ENCOUNTER_ID, DATE_ENCOUNTER, DATE_LOCKED)
    		 VALUES (1, 1, TO_DATE('2014-07-21','YYYY-MM-DD'), TO_DATE('2014-07-22','YYYY-MM-DD'))
    	INTO CHP_ENCOUNTER (PROVIDER_RSN, CHP_ENCOUNTER_ID, DATE_ENCOUNTER, DATE_LOCKED)
    		 VALUES (1, 1, TO_DATE('2014-08-01','YYYY-MM-DD'), TO_DATE('2014-08-02','YYYY-MM-DD'))
    	INTO CHP_ENCOUNTER (PROVIDER_RSN, CHP_ENCOUNTER_ID, DATE_ENCOUNTER, DATE_LOCKED)
    		 VALUES (1, 1, TO_DATE('2014-08-11','YYYY-MM-DD'), TO_DATE('2014-08-12','YYYY-MM-DD'))
    	INTO CHP_ENCOUNTER (PROVIDER_RSN, CHP_ENCOUNTER_ID, DATE_ENCOUNTER, DATE_LOCKED)
    		 VALUES (1, 1, TO_DATE('2014-08-21','YYYY-MM-DD'), TO_DATE('2014-08-22','YYYY-MM-DD'))
    	INTO CHP_ENCOUNTER (PROVIDER_RSN, CHP_ENCOUNTER_ID, DATE_ENCOUNTER, DATE_LOCKED)
    		 VALUES (1, 1, TO_DATE('2014-09-01','YYYY-MM-DD'), TO_DATE('2014-09-02','YYYY-MM-DD'))
    	INTO CHP_ENCOUNTER (PROVIDER_RSN, CHP_ENCOUNTER_ID, DATE_ENCOUNTER, DATE_LOCKED)
    		 VALUES (1, 1, TO_DATE('2014-09-11','YYYY-MM-DD'), TO_DATE('2014-09-12','YYYY-MM-DD'))
    	INTO CHP_ENCOUNTER (PROVIDER_RSN, CHP_ENCOUNTER_ID, DATE_ENCOUNTER, DATE_LOCKED)
    		 VALUES (1, 1, TO_DATE('2014-09-21','YYYY-MM-DD'), TO_DATE('2014-09-22','YYYY-MM-DD'))
    SELECT * FROM dual
    ;

**Query 1**:

    SELECT
          EN.PROVIDER_RSN                                                        AS PROVIDER_ID
        , EN.CHP_ENCOUNTER_ID
        , en.date_encounter
        , en.date_locked
        , ROUND((nvl(en.date_locked, sysdate) - en.date_encounter) * 24, 0)      AS HOURS_OLD
    
        , TRUNC(date_encounter,'YYYY') X1
        , TRUNC(date_encounter,'MM')   X2
        , TRUNC(date_locked,'MM')      X3
    
        , COUNT(date_encounter) OVER (PARTITION BY TRUNC(date_encounter,'MM'))   AS DE_MM_COUNT
        , COUNT(date_locked)    OVER (PARTITION BY TRUNC(date_locked,'MM'))      AS DL_MM_COUNT
                                      
        , COUNT(date_encounter) OVER (PARTITION BY TRUNC(date_encounter,'YYYY')) AS DE_YYYY_COUNT
        , COUNT(date_locked)    OVER (PARTITION BY TRUNC(date_locked,'YYYY'))    AS DL_YYYY_COUNT
                                      
    FROM CHP_ENCOUNTER EN

**[Results][2]**:
    
    | PROVIDER_ID | CHP_ENCOUNTER_ID |                   DATE_ENCOUNTER |                      DATE_LOCKED | HOURS_OLD |                             X1 |                               X2 |                               X3 | DE_MM_COUNT | DL_MM_COUNT | DE_YYYY_COUNT | DL_YYYY_COUNT |
    |-------------|------------------|----------------------------------|----------------------------------|-----------|--------------------------------|----------------------------------|----------------------------------|-------------|-------------|---------------|---------------|
    |           1 |                1 |     April, 01 2014 00:00:00+0000 |     April, 02 2014 00:00:00+0000 |        24 | January, 01 2014 00:00:00+0000 |     April, 01 2014 00:00:00+0000 |     April, 01 2014 00:00:00+0000 |           3 |           3 |            18 |            18 |
    |           1 |                1 |     April, 11 2014 00:00:00+0000 |     April, 12 2014 00:00:00+0000 |        24 | January, 01 2014 00:00:00+0000 |     April, 01 2014 00:00:00+0000 |     April, 01 2014 00:00:00+0000 |           3 |           3 |            18 |            18 |
    |           1 |                1 |     April, 21 2014 00:00:00+0000 |     April, 22 2014 00:00:00+0000 |        24 | January, 01 2014 00:00:00+0000 |     April, 01 2014 00:00:00+0000 |     April, 01 2014 00:00:00+0000 |           3 |           3 |            18 |            18 |
    |           1 |                1 |       May, 01 2014 00:00:00+0000 |       May, 02 2014 00:00:00+0000 |        24 | January, 01 2014 00:00:00+0000 |       May, 01 2014 00:00:00+0000 |       May, 01 2014 00:00:00+0000 |           3 |           3 |            18 |            18 |
    |           1 |                1 |       May, 11 2014 00:00:00+0000 |       May, 12 2014 00:00:00+0000 |        24 | January, 01 2014 00:00:00+0000 |       May, 01 2014 00:00:00+0000 |       May, 01 2014 00:00:00+0000 |           3 |           3 |            18 |            18 |
    |           1 |                1 |       May, 21 2014 00:00:00+0000 |       May, 22 2014 00:00:00+0000 |        24 | January, 01 2014 00:00:00+0000 |       May, 01 2014 00:00:00+0000 |       May, 01 2014 00:00:00+0000 |           3 |           3 |            18 |            18 |
    |           1 |                1 |      June, 01 2014 00:00:00+0000 |      June, 02 2014 00:00:00+0000 |        24 | January, 01 2014 00:00:00+0000 |      June, 01 2014 00:00:00+0000 |      June, 01 2014 00:00:00+0000 |           3 |           3 |            18 |            18 |
    |           1 |                1 |      June, 11 2014 00:00:00+0000 |      June, 12 2014 00:00:00+0000 |        24 | January, 01 2014 00:00:00+0000 |      June, 01 2014 00:00:00+0000 |      June, 01 2014 00:00:00+0000 |           3 |           3 |            18 |            18 |
    |           1 |                1 |      June, 21 2014 00:00:00+0000 |      June, 22 2014 00:00:00+0000 |        24 | January, 01 2014 00:00:00+0000 |      June, 01 2014 00:00:00+0000 |      June, 01 2014 00:00:00+0000 |           3 |           3 |            18 |            18 |
    |           1 |                1 |      July, 01 2014 00:00:00+0000 |      July, 02 2014 00:00:00+0000 |        24 | January, 01 2014 00:00:00+0000 |      July, 01 2014 00:00:00+0000 |      July, 01 2014 00:00:00+0000 |           3 |           3 |            18 |            18 |
    |           1 |                1 |      July, 11 2014 00:00:00+0000 |      July, 12 2014 00:00:00+0000 |        24 | January, 01 2014 00:00:00+0000 |      July, 01 2014 00:00:00+0000 |      July, 01 2014 00:00:00+0000 |           3 |           3 |            18 |            18 |
    |           1 |                1 |      July, 21 2014 00:00:00+0000 |      July, 22 2014 00:00:00+0000 |        24 | January, 01 2014 00:00:00+0000 |      July, 01 2014 00:00:00+0000 |      July, 01 2014 00:00:00+0000 |           3 |           3 |            18 |            18 |
    |           1 |                1 |    August, 01 2014 00:00:00+0000 |    August, 02 2014 00:00:00+0000 |        24 | January, 01 2014 00:00:00+0000 |    August, 01 2014 00:00:00+0000 |    August, 01 2014 00:00:00+0000 |           3 |           3 |            18 |            18 |
    |           1 |                1 |    August, 11 2014 00:00:00+0000 |    August, 12 2014 00:00:00+0000 |        24 | January, 01 2014 00:00:00+0000 |    August, 01 2014 00:00:00+0000 |    August, 01 2014 00:00:00+0000 |           3 |           3 |            18 |            18 |
    |           1 |                1 |    August, 21 2014 00:00:00+0000 |    August, 22 2014 00:00:00+0000 |        24 | January, 01 2014 00:00:00+0000 |    August, 01 2014 00:00:00+0000 |    August, 01 2014 00:00:00+0000 |           3 |           3 |            18 |            18 |
    |           1 |                1 | September, 01 2014 00:00:00+0000 | September, 02 2014 00:00:00+0000 |        24 | January, 01 2014 00:00:00+0000 | September, 01 2014 00:00:00+0000 | September, 01 2014 00:00:00+0000 |           3 |           3 |            18 |            18 |
    |           1 |                1 | September, 11 2014 00:00:00+0000 | September, 12 2014 00:00:00+0000 |        24 | January, 01 2014 00:00:00+0000 | September, 01 2014 00:00:00+0000 | September, 01 2014 00:00:00+0000 |           3 |           3 |            18 |            18 |
    |           1 |                1 | September, 21 2014 00:00:00+0000 | September, 22 2014 00:00:00+0000 |        24 | January, 01 2014 00:00:00+0000 | September, 01 2014 00:00:00+0000 | September, 01 2014 00:00:00+0000 |           3 |           3 |            18 |            18 |



  [1]: http://sqlfiddle.com/#!4/9e34f/2

Open in new window

Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Most Valuable Expert 2011
Top Expert 2012

Commented:
here are a couple of variations.  I'm not sure if this the type of output you're looking for.

If you can provide sample data and expected output that would help a lot


This one returns a daily,monthly and yearly summary for dates within the data in the table

SELECT CASE
           WHEN grpd = 0 AND grpm = 1 AND grpy = 1
           THEN
               TO_CHAR(open_day, 'yyyy-mm-dd')
           WHEN grpd = 1 AND grpm = 0 AND grpy = 1
           THEN
               'Month Total: ' || TO_CHAR(open_month, 'yyyy-mm')
           WHEN grpd = 1 AND grpm = 1 AND grpy = 0
           THEN
               'Year Total: ' || TO_CHAR(open_year, 'yyyy')
       END
           period,
       cnt
  FROM (  SELECT open_day,
                 open_month,
                 open_year,
                 GROUPING(open_day) grpd,
                 GROUPING(open_month) grpm,
                 GROUPING(open_year) grpy,
                 COUNT(*) cnt
            FROM (SELECT provider_rsn,
                         chp_encounter_id,
                         date_encounter,
                         date_locked,
                         TRUNC(date_encounter + COLUMN_VALUE) open_day,
                         TRUNC(date_encounter + COLUMN_VALUE, 'mm') open_month,
                         TRUNC(date_encounter + COLUMN_VALUE, 'yyyy') open_year
                    FROM chp_encounter ce,
                         TABLE(
                                 SELECT COLLECT(LEVEL - 1)
                                   FROM DUAL
                             CONNECT BY LEVEL < NVL(date_locked, SYSDATE) - date_encounter
                         ))
        GROUP BY CUBE(open_day, open_month, open_year)) x
 WHERE CASE
           WHEN grpd = 0 AND grpm = 1 AND grpy = 1
           THEN
               TO_CHAR(open_day, 'yyyy-mm-dd')
           WHEN grpd = 1 AND grpm = 0 AND grpy = 1
           THEN
               'Month Total: ' || TO_CHAR(open_month, 'yyyy-mm')
           WHEN grpd = 1 AND grpm = 1 AND grpy = 0
           THEN
               'Year Total: ' || TO_CHAR(open_year, 'yyyy')
       END
           IS NOT NULL

Open in new window



This one returns a daily,monthly and yearly summary for dates within a given date range regardless of whether the data exists in the table or not


WITH days(d)
     AS (SELECT TO_DATE('01/01/2012', 'MM/DD/YYYY') d FROM DUAL
         UNION ALL
         SELECT d + 1
           FROM days
          WHERE d <= TRUNC(SYSDATE))
SELECT CASE
           WHEN grpd = 0 AND grpm = 1 AND grpy = 1
           THEN
               TO_CHAR(open_day, 'yyyy-mm-dd')
           WHEN grpd = 1 AND grpm = 0 AND grpy = 1
           THEN
               'Month Total: ' || TO_CHAR(open_month, 'yyyy-mm')
           WHEN grpd = 1 AND grpm = 1 AND grpy = 0
           THEN
               'Year Total: ' || TO_CHAR(open_year, 'yyyy')
       END
           period,
       cnt
  FROM (  SELECT open_day,
                 open_month,
                 open_year,
                 GROUPING(open_day) grpd,
                 GROUPING(open_month) grpm,
                 GROUPING(open_year) grpy,
                 COUNT(date_encounter) cnt
            FROM (SELECT provider_rsn,
                         chp_encounter_id,
                         date_encounter,
                         date_locked,
                         d open_day,
                         TRUNC(d, 'mm') open_month,
                         TRUNC(d, 'yyyy') open_year
                    FROM days
                         LEFT OUTER JOIN chp_encounter ce
                             ON ce.date_encounter <= days.d AND ce.date_locked > days.d)
        GROUP BY CUBE(open_day, open_month, open_year)) x
 WHERE CASE
           WHEN grpd = 0 AND grpm = 1 AND grpy = 1
           THEN
               TO_CHAR(open_day, 'yyyy-mm-dd')
           WHEN grpd = 1 AND grpm = 0 AND grpy = 1
           THEN
               'Month Total: ' || TO_CHAR(open_month, 'yyyy-mm')
           WHEN grpd = 1 AND grpm = 1 AND grpy = 0
           THEN
               'Year Total: ' || TO_CHAR(open_year, 'yyyy')
       END
           IS NOT NULL

Open in new window

Author

Commented:
here is an example of what output I have now
DATE_ENCOUNTER      DATE_LOCKED      HOURS_OLD
1/1/2012 8:23      4/2/2012 17:30      2217
1/1/2012 8:31      4/2/2012 17:30      2217
1/1/2012 9:51      4/2/2012 17:30      2216
1/1/2012 10:27      2/12/2012 14:24      1012
1/1/2012 10:28      1/16/2013 12:55      9146
1/1/2012 10:47      4/2/2012 17:30      2215
1/1/2012 11:19      4/2/2012 17:30      2214
1/1/2012 11:56      4/2/2012 17:30      2214
1/2/2012 6:57      1/11/2012 15:32      225
1/2/2012 7:10      1/11/2012 15:32      224
1/2/2012 7:12      1/11/2012 15:32      224
1/2/2012 7:25      1/16/2012 13:24      342
1/2/2012 7:31      1/11/2012 15:32      224
1/2/2012 7:40      1/11/2012 15:32      224
1/2/2012 7:43      7/26/2012 17:28      4954
1/2/2012 7:44      1/13/2012 14:20      271
1/2/2012 7:45      1/3/2012 7:52      24
1/2/2012 7:46      1/10/2012 17:27      202
1/2/2012 7:49      8/14/2012 21:11      5413
1/2/2012 7:52      4/10/2012 11:11      2379
1/2/2012 7:56      7/26/2012 17:28      4954
1/2/2012 7:58      6/1/2012 14:19      3630
1/2/2012 7:58      1/11/2012 15:32      224
1/2/2012 8:00      7/26/2012 17:28      4953
1/2/2012 8:00      8/15/2012 10:39      5427
1/2/2012 8:01      7/26/2012 20:06      4956
1/2/2012 8:03      1/27/2012 19:30      611
1/2/2012 8:03      8/14/2012 21:11      5413
1/2/2012 8:04      7/26/2012 17:28      4953
1/2/2012 8:04      7/26/2012 20:06      4956
1/2/2012 8:04      8/13/2012 21:26      5389
1/2/2012 8:04      1/27/2012 17:03      609
1/2/2012 8:06      6/1/2012 14:19      3630
1/2/2012 8:08      2/21/2012 8:02      1200
1/2/2012 8:09      6/1/2012 14:19      3630
1/2/2012 8:10      8/21/2012 20:44      5581
1/2/2012 8:11      8/21/2012 18:26      5578
1/2/2012 8:13      1/27/2012 17:03      609
1/2/2012 8:13      1/31/2012 14:55      703
1/2/2012 8:15      8/21/2012 18:26      5578
1/2/2012 8:16      7/26/2012 19:23      4955
1/2/2012 8:20      1/31/2012 14:55      703
1/2/2012 8:20      6/7/2012 13:35      3773
1/2/2012 8:24      8/14/2012 21:11      5413
1/2/2012 8:25      1/11/2012 8:37      216
1/2/2012 8:25      1/10/2012 14:03      198
1/2/2012 8:26      2/6/2012 10:31      842
1/2/2012 8:27      1/31/2012 14:55      702
1/2/2012 8:27      1/27/2012 19:35      611
1/2/2012 8:28      6/1/2012 14:19      3630

What I am hoping to eventually get is something like:
Report_Date      Items Pending as of report date
1/1/2012      10000
1/2/2012      9999
1/3/2012      9999
1/4/2012      9998
1/5/2012      9997
1/6/2012      9997
1/7/2012      9997
1/8/2012      9998
1/9/2012      9998
1/10/2012      9998
1/11/2012      9998
1/12/2012      9999
1/13/2012      9999
1/14/2012      9999
1/15/2012      9999
1/16/2012      10100
1/17/2012      10100
1/18/2012      10100
1/19/2012      10100
1/20/2012      10100
1/21/2012      10100
1/22/2012      10100

The counts above are purely hypothetical.  The eventual output would span a minimum of 24 months and be used to build a graph to show average number of items pending per month over a two year timespan.  An item is considered pending when the time difference between the date_encounter and date_locked is greater than 24 hours.

Sorry for my delay in response to all of your ideas but I have been pulled to work on office moves and I am trying to fit in testing of your suggestions in between.

I see sdstuber suggested building a temp table of all the days, this makes sense but I need to mess with it some more.

Thanks again!

Author

Commented:
forgot to mention my version
NLSRTL       10.2.0.4.0      Production
Oracle Database 10g       10.2.0.4.0      Production
PL/SQL       10.2.0.4.0      Production
TNS for Solaris:       10.2.0.4.0      Production

so the temp table is not working as posted but I should be able to figure that out
Most Valuable Expert 2011
Top Expert 2012

Commented:
>>> I see sdstuber suggested building a temp table of all the days,

not really a temp table, the WITH clause generates the range of days based on your inputs.

I suppose you create a table of all days, but if you're going to do that then it probably makes sense to have it be a normal table because there's no reason to generate a temp table on the fly repeatedly.

WITH is a 9i feature, but recursive is not, that requires 11gR2

for 10.2.0.4  change the WITH to use connect by


WITH days
     AS (    SELECT TO_DATE('01/01/2012', 'MM/DD/YYYY') + LEVEL - 1 d
               FROM DUAL
         CONNECT BY TO_DATE('01/01/2012', 'MM/DD/YYYY') + LEVEL - 1 <= TRUNC(SYSDATE))

Author

Commented:
Thanks! That saves me some time.
Most Valuable Expert 2011
Top Expert 2012

Commented:
thanks for the sample but that's not really what I was looking for.  I wasn't specific enough.  sorry

Given a date range, do you want counts for every day that has data? (i.e. my first query)
or, every day regardless of whether there is data? (i.e. my second query)


Note - in my first query I didn't include a date range, but you can do so.

Something like


 FROM chp_encounter ce,
                         TABLE(
                                 SELECT COLLECT(LEVEL - 1)
                                   FROM DUAL
                             CONNECT BY LEVEL < NVL(date_locked, SYSDATE) - date_encounter
                         )
WHERE  nvl(ce.date_locked,sysdate)-ce.date_encounter > 1
    and ce.date_encounter > to_date('01/01/2012','MM/DD/YYYY')
    and ce.date_encounter < sysdate-1

Open in new window


if you use the same date range for both and every day will always have at least some data, then they should return the same thing.

Author

Commented:
a count for every day regardless of if there is data
Most Valuable Expert 2011
Top Expert 2012

Commented:
ok, the WITH version should give you what you want then

Author

Commented:
It looks like this is what I need

WITH days
     AS (    SELECT TO_DATE('09/01/2014', 'MM/DD/YYYY') + LEVEL - 1 d
               FROM DUAL
         CONNECT BY TO_DATE('09/01/2014', 'MM/DD/YYYY') + LEVEL - 1 <= TRUNC(SYSDATE))
         
SELECT 
  d
  ,EN.PROVIDER_RSN PROVIDER_ID
  ,count(EN.CHP_ENCOUNTER_ID) cnt
  FROM days
  left outer join CHP_ENCOUNTER EN on trunc(en.date_encounter) < days.d AND nvl(trunc(en.date_locked),trunc(sysdate)) >= days.d
  
  WHERE 
    round((nvl(trunc(en.date_locked),trunc(sysdate))-trunc(en.date_encounter))*24,0) > 24
  group by d,EN.PROVIDER_RSN
  order by EN.PROVIDER_RSN,d asc

Open in new window


I added the provider column but otherwise this seems to be giving me the data I am after.

it is similar in function to this

select
  count(chp_encounter_id) cnt
  from chp_encounter EN
  where date_locked is null
  and    round((nvl(trunc(en.date_locked),trunc(sysdate))-trunc(en.date_encounter))*24,0) > 24 

Open in new window


which gives me the current pending item count as of NOW

the numbers are a little different for the current day between the two queries and I suspect that I am messing up the join which I modified based off of the logic that I should be looking at the age of a record when it is locked or the current day if the record was never locked to the current day.

A sanity check would be much appreciated.
Most Valuable Expert 2011
Top Expert 2012

Commented:
Your query is only returning data for rows that have data in your time window.

That isn't the same as what you asked for in ID: 40364363,  did you change your mind?  If so then an outer join is not correct - since you are negating the "OUTER" part making it an INNER JOIN anyway.  Thus losing the functionality but gaining the inefficiency.

Also, I think you have your   > vs >= and < vs <= backwards on your join conditions

And - you have removed the monthly and yearly counts and are only returning the daily counts,  do you not need those anymore either?

Author

Commented:
Maybe I described it wrong regarding the time window.
I want to get a count of records that were open for each day in a given time window where records can be open spanning multiple days in that time window and may have been opened before the time window started and closed after the time window ended.

I did notice that the OUTER part of the join was negated.  If I understand correctly the left inner would give me all days in days table and count all encounters in encounters table that were opened before the "day" and closed (locked) on or after the "day" (pending as of the day) but exclude any encounters from the "day" count that had an open/closed date that the "day" was not between.

I do not need to know how many encounters were created or closed on the day, only how many were pending (time between open and close is > 24 hours) as of the day.

to do this for just today I use this:
select
  count(chp_encounter_id) cnt
  from chp_encounter EN
  where date_locked is null
  and    round((nvl(trunc(en.date_locked),trunc(sysdate))-trunc(en.date_encounter))*24,0) > 24

Open in new window


As for the yearly and monthly counts, I think what I need to do is use a cutoff date for each month and say "as of the first of each month there were N pending records"

I think the term running total is appropriate.

Sorry if I am not making sense and again I very much appreciate your help working through this.
Dave
Most Valuable Expert 2011
Top Expert 2012

Commented:
Let's take a different approach,  given the data below and a date range of Sep 25 - Oct 6  what should the output be?  Including any summary rows for month and/or year.  I've already pre-filtered the data so all rows span at least 24 hours.  Also including any dates that have 0 counts but you want to display anyway

date_encounter           date_locked
2014-09-26 23:10:35	2014-10-01 06:49:11
2014-09-27 07:12:02	2014-09-30 22:29:14
2014-09-27 15:13:28	2014-10-02 14:09:16
2014-09-27 23:14:55	2014-10-02 05:49:19
2014-09-28 07:16:21	2014-10-01 21:29:21
2014-09-28 15:17:47	2014-10-03 13:09:23
2014-09-28 23:19:14	2014-10-03 04:49:25
2014-09-29 07:20:40	2014-10-02 20:29:27
2014-09-29 15:22:07	2014-10-04 12:09:30
2014-09-29 23:23:33	2014-10-04 03:49:32

Open in new window

Author

Commented:
Ok, here is what it would look like with that sample

Calendar_Date	Count
9/25/14 0:00	0
9/26/14 0:00	0
9/27/14 0:00	0
9/28/14 0:00	0
9/29/14 0:00	1
9/30/14 0:00	4
10/1/14 0:00	6
10/2/14 0:00	7
10/3/14 0:00	4
10/4/14 0:00	2

Open in new window


I manually counted the number of records that were opened 24 hours before midnight on each calendar date and had a date_locked after midnight on the calendar day.

If I were to graph this with a data point for each month I would have one record from this sample
Calendar Date Count
10/1/2014 0:00 6

Open in new window


If I were to run this for any longer period of time (multi month) I would use a calculated series in my graphing tool to give me the moving average per day over a 7 day period, so really only need the count for each calendar day and no monthly summary in the actual SQL.  

For any individual record to be counted as pending on a calendar date it must be more than 24 hours old and not yet locked at midnight on that calendar day

The code I sent in ID: 40364630 looks to be getting me within arms reach of my requirement but takes a looong time to run (~200 seconds) for any more than a few calendar days and there is still a few hundred count difference between the count for the "pending right now" query which I think is due to what you mentioned with the < vs <= earlier.
The encounter table has > 1.5m rows and I do not have control over indexes and wouldn't know what to do with them if I did.

Thanks again,
Dave
Most Valuable Expert 2011
Top Expert 2012

Commented:
So, you only fill in missing dates at the beginning of the data range, but not at the end?  I'm asking because Sep 25 is filled in with 0 as I expected, but you don't have Oct 5 or Oct 6

Author

Commented:
Sorry, I missed those but they would be 0 as well.
Most Valuable Expert 2011
Top Expert 2012

Commented:
I don't get quite the same counts as you,

Here I'm including the date ranges that are considered part of each day in my output, can you confirm if these obey your rules?

date            cnt       date_ranges
2014-09-25	0	 - 
2014-09-26	0	 - 
2014-09-27	1	2014-09-26 23:10:35 - 2014-10-01 06:49:11
2014-09-28	4	2014-09-26 23:10:35 - 2014-10-01 06:49:11  ,  2014-09-27 07:12:02 - 2014-09-30 22:29:14  ,  2014-09-27 15:13:28 - 2014-10-02 14:09:16  ,  2014-09-27 23:14:55 - 2014-10-02 05:49:19
2014-09-29	7	2014-09-26 23:10:35 - 2014-10-01 06:49:11  ,  2014-09-27 07:12:02 - 2014-09-30 22:29:14  ,  2014-09-27 15:13:28 - 2014-10-02 14:09:16  ,  2014-09-27 23:14:55 - 2014-10-02 05:49:19  ,  2014-09-28 07:16:21 - 2014-10-01 21:29:21  ,  2014-09-28 15:17:47 - 2014-10-03 13:09:23  ,  2014-09-28 23:19:14 - 2014-10-03 04:49:25
2014-09-30	10	2014-09-26 23:10:35 - 2014-10-01 06:49:11  ,  2014-09-27 07:12:02 - 2014-09-30 22:29:14  ,  2014-09-27 15:13:28 - 2014-10-02 14:09:16  ,  2014-09-27 23:14:55 - 2014-10-02 05:49:19  ,  2014-09-28 07:16:21 - 2014-10-01 21:29:21  ,  2014-09-28 15:17:47 - 2014-10-03 13:09:23  ,  2014-09-28 23:19:14 - 2014-10-03 04:49:25  ,  2014-09-29 07:20:40 - 2014-10-02 20:29:27  ,  2014-09-29 15:22:07 - 2014-10-04 12:09:30  ,  2014-09-29 23:23:33 - 2014-10-04 03:49:32
2014-10-01	9	2014-09-26 23:10:35 - 2014-10-01 06:49:11  ,  2014-09-27 15:13:28 - 2014-10-02 14:09:16  ,  2014-09-27 23:14:55 - 2014-10-02 05:49:19  ,  2014-09-28 07:16:21 - 2014-10-01 21:29:21  ,  2014-09-28 15:17:47 - 2014-10-03 13:09:23  ,  2014-09-28 23:19:14 - 2014-10-03 04:49:25  ,  2014-09-29 07:20:40 - 2014-10-02 20:29:27  ,  2014-09-29 15:22:07 - 2014-10-04 12:09:30  ,  2014-09-29 23:23:33 - 2014-10-04 03:49:32
2014-10-02	7	2014-09-27 15:13:28 - 2014-10-02 14:09:16  ,  2014-09-27 23:14:55 - 2014-10-02 05:49:19  ,  2014-09-28 15:17:47 - 2014-10-03 13:09:23  ,  2014-09-28 23:19:14 - 2014-10-03 04:49:25  ,  2014-09-29 07:20:40 - 2014-10-02 20:29:27  ,  2014-09-29 15:22:07 - 2014-10-04 12:09:30  ,  2014-09-29 23:23:33 - 2014-10-04 03:49:32
2014-10-03	4	2014-09-28 15:17:47 - 2014-10-03 13:09:23  ,  2014-09-28 23:19:14 - 2014-10-03 04:49:25  ,  2014-09-29 15:22:07 - 2014-10-04 12:09:30  ,  2014-09-29 23:23:33 - 2014-10-04 03:49:32
2014-10-04	2	2014-09-29 15:22:07 - 2014-10-04 12:09:30  ,  2014-09-29 23:23:33 - 2014-10-04 03:49:32
2014-10-05	0	 - 
2014-10-06	0	 - 
 

Open in new window



and this is the query I used for that

WITH days
     AS (    SELECT TO_DATE('09/25/2014', 'MM/DD/YYYY') + LEVEL - 1 d
               FROM DUAL
         CONNECT BY TO_DATE('09/25/2014', 'MM/DD/YYYY') + LEVEL - 1 <=
                        TO_DATE('10/06/2014', 'mm/dd/yyyy'))
SELECT d,
         COUNT(en.chp_encounter_id) cnt,
         LISTAGG(
                TO_CHAR(date_encounter, 'yyyy-mm-dd hh24:mi:ss')
             || ' - '
             || TO_CHAR(date_locked, 'yyyy-mm-dd hh24:mi:ss'),
             '  ,  '
         )
         WITHIN GROUP (ORDER BY date_encounter, date_locked)
             dateranges
    FROM days
         LEFT OUTER JOIN chp_encounter en
             ON en.date_encounter <= days.d -- open as of 00:00 of this day
            AND NVL(en.date_locked, SYSDATE) > days.d --  not locked at 00:00 of this day
            AND en.date_locked - en.date_encounter >= 1 -- open at least 24 hours
GROUP BY d, en.provider_rsn
ORDER BY d ASC

Open in new window

but chp_encounter containing only the 10 rows I posted above

Author

Commented:
The encounter that was opened on the 26th at 23:10 could not have been "pending" on the 27th at midnight because it would not have been 24 hours old yet.

I am still reviewing the other examples but I suspect that is why our counts differ.
Most Valuable Expert 2011
Top Expert 2012

Commented:
a slightly different read with slightly different results


date          cnt    date_ranges
2014-09-25	0	 - 
2014-09-26	0	 - 
2014-09-27	0	 - 
2014-09-28	1	2014-09-26 23:10:35 - 2014-10-01 06:49:11
2014-09-29	4	2014-09-26 23:10:35 - 2014-10-01 06:49:11  ,  2014-09-27 07:12:02 - 2014-09-30 22:29:14  ,  2014-09-27 15:13:28 - 2014-10-02 14:09:16  ,  2014-09-27 23:14:55 - 2014-10-02 05:49:19
2014-09-30	7	2014-09-26 23:10:35 - 2014-10-01 06:49:11  ,  2014-09-27 07:12:02 - 2014-09-30 22:29:14  ,  2014-09-27 15:13:28 - 2014-10-02 14:09:16  ,  2014-09-27 23:14:55 - 2014-10-02 05:49:19  ,  2014-09-28 07:16:21 - 2014-10-01 21:29:21  ,  2014-09-28 15:17:47 - 2014-10-03 13:09:23  ,  2014-09-28 23:19:14 - 2014-10-03 04:49:25
2014-10-01	9	2014-09-26 23:10:35 - 2014-10-01 06:49:11  ,  2014-09-27 15:13:28 - 2014-10-02 14:09:16  ,  2014-09-27 23:14:55 - 2014-10-02 05:49:19  ,  2014-09-28 07:16:21 - 2014-10-01 21:29:21  ,  2014-09-28 15:17:47 - 2014-10-03 13:09:23  ,  2014-09-28 23:19:14 - 2014-10-03 04:49:25  ,  2014-09-29 07:20:40 - 2014-10-02 20:29:27  ,  2014-09-29 15:22:07 - 2014-10-04 12:09:30  ,  2014-09-29 23:23:33 - 2014-10-04 03:49:32
2014-10-02	7	2014-09-27 15:13:28 - 2014-10-02 14:09:16  ,  2014-09-27 23:14:55 - 2014-10-02 05:49:19  ,  2014-09-28 15:17:47 - 2014-10-03 13:09:23  ,  2014-09-28 23:19:14 - 2014-10-03 04:49:25  ,  2014-09-29 07:20:40 - 2014-10-02 20:29:27  ,  2014-09-29 15:22:07 - 2014-10-04 12:09:30  ,  2014-09-29 23:23:33 - 2014-10-04 03:49:32
2014-10-03	4	2014-09-28 15:17:47 - 2014-10-03 13:09:23  ,  2014-09-28 23:19:14 - 2014-10-03 04:49:25  ,  2014-09-29 15:22:07 - 2014-10-04 12:09:30  ,  2014-09-29 23:23:33 - 2014-10-04 03:49:32
2014-10-04	2	2014-09-29 15:22:07 - 2014-10-04 12:09:30  ,  2014-09-29 23:23:33 - 2014-10-04 03:49:32
2014-10-05	0	 - 
2014-10-06	0	 - 

Open in new window


produced from the same sample data but with this query


WITH days
     AS (    SELECT TO_DATE('09/25/2014', 'MM/DD/YYYY') + LEVEL - 1 d
               FROM DUAL
         CONNECT BY TO_DATE('09/25/2014', 'MM/DD/YYYY') + LEVEL - 1 <=
                        TO_DATE('10/06/2014', 'mm/dd/yyyy'))
  SELECT d,
         COUNT(en.chp_encounter_id) cnt,
         LISTAGG(
                TO_CHAR(date_encounter, 'yyyy-mm-dd hh24:mi:ss')
             || ' - '
             || TO_CHAR(date_locked, 'yyyy-mm-dd hh24:mi:ss'),
             '  ,  '
         )
         WITHIN GROUP (ORDER BY date_encounter, date_locked)
             dateranges
    FROM days
         LEFT OUTER JOIN chp_encounter en
             ON en.date_encounter <= days.d-1 -- open for 24 hours as of 00:00 of this day
            AND NVL(en.date_locked, SYSDATE) > days.d --  not locked at 00:00 of this day           
GROUP BY d, en.provider_rsn
ORDER BY d ASC

Open in new window

Most Valuable Expert 2011
Top Expert 2012

Commented:
>>> on the 27th at midnight

is that supposed to mean 2014-09-27 00:00:00

or should that be 2014-09-28 00:00:00

?

Author

Commented:
2014-09-27 00:00:00

I have the count for the 28th at zero as well because for any record to be "pending" as of 2014-09-28 00:00:00 it would have to have been opened before 2014-09-26 00:00:00 and have a locked date >= 2014-09-28 00:00:00
Most Valuable Expert 2011
Top Expert 2012

Commented:
ok this produces the same results you posted above but shifted by one day
and this means "pending"  means open for at least 48 hours.  Is that really what you want?

1	2014-09-25	0	 - 
2	2014-09-26	0	 - 
3	2014-09-27	0	 - 
4	2014-09-28	1	2014-09-26 23:10:35 - 2014-10-01 06:49:11
5	2014-09-29	4	2014-09-26 23:10:35 - 2014-10-01 06:49:11  ,  2014-09-27 07:12:02 - 2014-09-30 22:29:14  ,  2014-09-27 15:13:28 - 2014-10-02 14:09:16  ,  2014-09-27 23:14:55 - 2014-10-02 05:49:19
6	2014-09-30	6	2014-09-26 23:10:35 - 2014-10-01 06:49:11  ,  2014-09-27 15:13:28 - 2014-10-02 14:09:16  ,  2014-09-27 23:14:55 - 2014-10-02 05:49:19  ,  2014-09-28 07:16:21 - 2014-10-01 21:29:21  ,  2014-09-28 15:17:47 - 2014-10-03 13:09:23  ,  2014-09-28 23:19:14 - 2014-10-03 04:49:25
7	2014-10-01	7	2014-09-27 15:13:28 - 2014-10-02 14:09:16  ,  2014-09-27 23:14:55 - 2014-10-02 05:49:19  ,  2014-09-28 15:17:47 - 2014-10-03 13:09:23  ,  2014-09-28 23:19:14 - 2014-10-03 04:49:25  ,  2014-09-29 07:20:40 - 2014-10-02 20:29:27  ,  2014-09-29 15:22:07 - 2014-10-04 12:09:30  ,  2014-09-29 23:23:33 - 2014-10-04 03:49:32
8	2014-10-02	4	2014-09-28 15:17:47 - 2014-10-03 13:09:23  ,  2014-09-28 23:19:14 - 2014-10-03 04:49:25  ,  2014-09-29 15:22:07 - 2014-10-04 12:09:30  ,  2014-09-29 23:23:33 - 2014-10-04 03:49:32
9	2014-10-03	2	2014-09-29 15:22:07 - 2014-10-04 12:09:30  ,  2014-09-29 23:23:33 - 2014-10-04 03:49:32
10	2014-10-04	0	 - 
11	2014-10-05	0	 - 
12	2014-10-06	0	 - 

Open in new window



WITH days
     AS (    SELECT TO_DATE('09/25/2014', 'MM/DD/YYYY') + LEVEL - 1 d
               FROM DUAL
         CONNECT BY TO_DATE('09/25/2014', 'MM/DD/YYYY') + LEVEL - 1 <=
                        TO_DATE('10/06/2014', 'mm/dd/yyyy'))
  SELECT d,
         COUNT(en.chp_encounter_id) cnt,
         LISTAGG(
                TO_CHAR(date_encounter, 'yyyy-mm-dd hh24:mi:ss')
             || ' - '
             || TO_CHAR(date_locked, 'yyyy-mm-dd hh24:mi:ss'),
             '  ,  '
         )
         WITHIN GROUP (ORDER BY date_encounter, date_locked)
             dateranges
    FROM days
         LEFT OUTER JOIN chp_encounter en
             ON en.date_encounter <= days.d - 1 -- open 24 hours as of 00:00 of this day
            AND NVL(en.date_locked, SYSDATE) > days.d + 1 --  not locked at 00:00 of the next day
GROUP BY d, en.provider_rsn
ORDER BY d ASC

Open in new window

Author

Commented:
Ok, I think I am doing too many things at once!  My apologies for the confusion...
I do want 24 hours and I have just been looking at the dates wrong.
Most Valuable Expert 2011
Top Expert 2012

Commented:
ok, does that mean your expected results above need updated?

Author

Commented:
I think my version of oracle does not like the "within group"  statement, I get from keyword not found where expected.

Author

Commented:
yes, I need to redo that count
Most Valuable Expert 2011
Top Expert 2012

Commented:
listagg isn't supported in your version,  just remove that entire function, including the withing group part.  if you have stragg, concat_agg or similar user-defined string aggregates they should work albeit without sorting


it's not necessary though

it's only for illustration of the date ranges to make debugging the sample data easier.

Author

Commented:
Ok, here is the recount
calendar_Date	count
9/25/14 0:00	0
9/26/14 0:00	0
9/27/14 0:00	0
9/28/14 0:00	1
9/29/14 0:00	4
9/30/14 0:00	7
10/1/14 0:00	9
10/2/14 0:00	7
10/3/14 0:00	4
10/4/14 0:00	2
10/5/14 0:00	0
10/6/14 0:00	0

Open in new window


The count for the 30th is 7 because the three encounters opened on the 29th were not 24 hours old yet.
the count for the 1st is 9 because everything was opened before 9/30/14 0:00 and only one was closed before 10/1/14 0:00
Most Valuable Expert 2011
Top Expert 2012

Commented:
ok those are the results of the query in

http:#a40366515

here is the query again though - with listagg removed

WITH days
     AS (    SELECT TO_DATE('09/25/2014', 'MM/DD/YYYY') + LEVEL - 1 d
               FROM DUAL
         CONNECT BY TO_DATE('09/25/2014', 'MM/DD/YYYY') + LEVEL - 1 <=
                        TO_DATE('10/06/2014', 'mm/dd/yyyy'))
  SELECT d calendar_date, COUNT(en.chp_encounter_id) cnt
    FROM days
         LEFT OUTER JOIN chp_encounter en
             ON en.date_encounter <= days.d - 1 -- open 24 hours as of 00:00 of this day
            AND NVL(en.date_locked, SYSDATE) > days.d --  not locked at 00:00 of this day
GROUP BY d, en.provider_rsn
ORDER BY d ASC

Open in new window

Most Valuable Expert 2011
Top Expert 2012
Commented:
and if you want to include the provider_rsn just add it to the select

WITH days
     AS (    SELECT TO_DATE('09/25/2014', 'MM/DD/YYYY') + LEVEL - 1 d
               FROM DUAL
         CONNECT BY TO_DATE('09/25/2014', 'MM/DD/YYYY') + LEVEL - 1 <=
                        TO_DATE('10/06/2014', 'mm/dd/yyyy'))
  SELECT d calendar_date, en.provider_rsn provider_id, COUNT(en.chp_encounter_id) cnt
    FROM days
         LEFT OUTER JOIN chp_encounter en
             ON en.date_encounter <= days.d - 1 -- open 24 hours as of 00:00 of this day
         AND NVL(en.date_locked, SYSDATE) > days.d --  not locked at 00:00 of this day
GROUP BY d, en.provider_rsn
ORDER BY d ASC

Open in new window

Author

Commented:
sdstuber, you are amazing!  Thank you for your patience with me working through this and creating an elegant solution to what I thought would be a much more complex query.
This is exactly what I need.
Dave
Most Valuable Expert 2011
Top Expert 2012

Commented:
glad I could help.  I hope performance of the query is acceptable.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial