DaveJBaldwin
asked on
Count of pending records by date(Day/Month/Year)
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
FROM CHP_ENCOUNTER EN
WHERE
(nvl(en.date_locked,sysdat
and en.date_encounter > to_date('01/01/2012','MM/D
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
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
FROM CHP_ENCOUNTER EN
WHERE
(nvl(en.date_locked,sysdat
and en.date_encounter > to_date('01/01/2012','MM/D
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
ASKER
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
Once date locked is set it cannot be reversed
Perhaps this will help the discussion.
Using COUNT() OVER() partitioned by TRUNC( a date , 'YYYY') or TRUNC( a date , 'MM')
demo http://sqlfiddle.com/#!4/9e34f/2
setup:
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')
;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
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
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
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 NULLThis 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 NULLASKER
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!
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!
ASKER
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
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
>>> 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))
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))
ASKER
Thanks! That saves me some time.
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
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.
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-1if 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.
ASKER
a count for every day regardless of if there is data
ok, the WITH version should give you what you want then
ASKER
It looks like this is what I need
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
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.
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 ascI 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 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.
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?
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?
ASKER
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:
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
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) > 24As 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
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:32ASKER
Ok, here is what it would look like with that sample
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
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
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 2I 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 6If 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
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
ASKER
Sorry, I missed those but they would be 0 as well.
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?
and this is the query I used for that
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 -
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 ASCASKER
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.
I am still reviewing the other examples but I suspect that is why our counts differ.
a slightly different read with slightly different results
produced from the same sample data but with this query
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 - 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
>>> 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
?
is that supposed to mean 2014-09-27 00:00:00
or should that be 2014-09-28 00:00:00
?
ASKER
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
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
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?
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 - 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 ASCASKER
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.
I do want 24 hours and I have just been looking at the dates wrong.
ok, does that mean your expected results above need updated?
ASKER
I think my version of oracle does not like the "within group" statement, I get from keyword not found where expected.
ASKER
yes, I need to redo that count
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.
it's not necessary though
it's only for illustration of the date ranges to make debugging the sample data easier.
ASKER
Ok, here is the recount
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
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 0The 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
ok those are the results of the query in
http:#a40366515
here is the query again though - with listagg removed
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 ASCASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
This is exactly what I need.
Dave
glad I could help. I hope performance of the query is acceptable.
is there one row for each status?
can something open/close/open/close/open
if something opens and closes in the same day, what is that status in your summary?