SQL query Help... missing dates from range

[fmrock]

Hey everyone,

I have a task to pull a query/report that shows missing dates. I only have the ability to query, I dont have the abiltiy to create stored procedures or temp tables on this system (its a vendors).

I need to find out if there are any missing Dates (Service Dates) from the charges table between the Admit date(which will be the same for every row) and the last charge date(aka..service date).

For example, I would like to know admit date = 9/2/08 last charge date = 9/30/08 – missing dates 9/14 and 9/16….

Hope that makes sense, and if you have any ideas that would be great.

SELECT 
	A.ACCTCPCODE Site, 
	A.ACCTCODE Account, 
	to_date(A.ACCTLOGDATE,'j') Log_Date, 
	A.ACCTBALANCE Balance, 
	C.TYPE, 
	C.PRCODE, 
	to_date(C.SERVICEDATE,'j') Service_Date, 
	to_date(C.ADMITDATE,'j') Admit_Date
FROM   
	ACCOUNT A
	JOIN CHARGES C ON ((A.ACCTCPCODE=C.CPCODE) 
		AND (A.ACCTCODE=C.ACCOUNT)
		AND (C.TYPE='C') 
		AND (C.SPLITFLAG IS  NULL))
WHERE   
	(A.ACCTCPCODE='226002') 
	AND (A.ACCTCODE='133')
ORDER BY 
	to_date(C.SERVICEDATE,'j')

 

[Thargy]

fm,

Since I'm from the other side of the pond, can we discuss dates in DD MON YYYY format, otherwise I'm going to get very confused..

You want to get "missing" dates from the CHARGES table. In your example you cite the 14th and 16th September 2008 as being missing, because the admit date was the 2nd Sep 2008 and the last charge date was 30 Sep 2008. On what basis are dates missing? Why were the 3-13th, 15th, and 17-29th not also missing? Are you filtering by weeks or something?

Can you post the create table statement for charges, and some insert statements which provide data that exemplifies "missing" dates. That should set us up to be able to provide better help.

Regards

T

 

[fmrock]

There should be a charge line for every day between the admit and the last charge date.

This report is to find accounts that do not have a charge for every day they were at our facility.

I came up with following query that comes up with the number of days missing, just not which ones.

Since I do not have access to create tables on this system or any oracle system I will try to come up with a create table statement, but its going to be tough for me. I only have the ability to query this vendors system.

SELECT 
	A.ACCTCPCODE Site, 
	A.ACCTCODE Account, 
	to_date(C.ADMITDATE,'j') Admit_Date,
	to_date(to_number(MAX(C.SERVICEDATE)),'j') Last_Service_Date,
	to_number(MAX(C.SERVICEDATE))-C.ADMITDATE Days_Diff,
	COUNT(DISTINCT C.SEQNO) Num_of_Charges,
	(to_number(MAX(C.SERVICEDATE))-C.ADMITDATE)-COUNT(DISTINCT C.SEQNO) Num_Missing_Days
FROM   
	ACCOUNT A
	JOIN CHARGES C ON ((A.ACCTCPCODE=C.CPCODE) 
		AND (A.ACCTCODE=C.ACCOUNT)
		AND (C.TYPE='C') 
		AND (C.SPLITFLAG IS  NULL))
WHERE   
	(A.ACCTCPCODE='226002') 
GROUP BY
	A.ACCTCPCODE, 
	A.ACCTCODE, 
	C.ADMITDATE
HAVING
	(to_number(MAX(C.SERVICEDATE))-C.ADMITDATE)-COUNT(DISTINCT C.SEQNO)>0

Also, all the dates in this system are stored in a Julian format.

 

[Dagon]

You don't seem to have a column which holds the date on which the daily charge was made, so how exactly do you know what dates incurred charges ? Does seqno act like some sort of offset to the admitdate e.g. if admitdate= '15-jun-08' and seqno=1, there was a charge on the 16-jun-08 etc ?

 

[Dagon]

Here's a mocked up example of how generally you'd approach the problem. You need to generate a list of dates using a row-generator query and then join that to your account table using a between so that you generate a list of all possible dates for that account. You then outer join that to the actual dates to find the missing ones.

drop table zz;

create table zz (account number, servicedate date, admitdate date, seqno number);

insert into zz values (1, TRUNC(SYSDATE)-10, TRUNC(SYSDATE)-30, 1);
insert into zz values (1, TRUNC(SYSDATE)-10, TRUNC(SYSDATE)-30, 2);
insert into zz values (2, TRUNC(SYSDATE), TRUNC(SYSDATE)-2, 2);
insert into zz values (2, TRUNC(SYSDATE), TRUNC(SYSDATE)-2, 1);
insert into zz values (2, TRUNC(SYSDATE), TRUNC(SYSDATE)-2, 0);


select b.account, b.admitdate, a.cdate, b.adate, b.servicedate
from
(select account, admitdate, admitdate+seqno cdate, servicedate
from zz) a,
(select z.account, d.adate, z.servicedate, z.admitdate
from
(select distinct account, servicedate, admitdate
from zz) z,
(select trunc(sysdate)-50+level as adate
from dual
connect by level <=50) d
where d.adate between z.admitdate and z.servicedate) b
where a.account(+) = b.account
and   a.cdate(+) = b.adate
order by b.account, b.adate;

This assumes seqno represents an offset to the admitdate.

 

[Thargy]

I've made some assumptions here, so bear with me. As Dagon says, there seems to be no programmatic means of detecting when a charge was made - or is it the fact that there's an entry in the table?

Anyway, on the assumption that the vendors don't know their elbow from their fundament, and have made the fundamental error of storing dates as strings I have done date to string conversions and vice versa below. If the dates are stored as dates, then chop out all the TO_CHAR'ing etc.

CREATE TABLE FMROCK
(
 MAGIC_NUMBER       INTEGER  NOT NULL,
 CRAPPY_DATE_STRING VARCHAR2(20) NOT NULL
);

--Make entries for each daY of the month
INSERT INTO FMROCK
(
SELECT LEVEL L, LPAD(TO_CHAR(LEVEL),2,'0')||' MAY 2009'
  FROM DUAL
CONNECT BY LEVEL < 32);

--Make some dates "missing"
DELETE FROM FMROCK WHERE MAGIC_NUMBER = 5;
DELETE FROM FMROCK WHERE MAGIC_NUMBER = 6;
DELETE FROM FMROCK WHERE MAGIC_NUMBER = 7;
DELETE FROM FMROCK WHERE MAGIC_NUMBER = 22;

SELECT LPAD(TO_CHAR(LEVEL),2,'0')||' MAY 2009' MISSING_DATES
  FROM DUAL
CONNECT BY LEVEL < TO_DATE('31 MAY 2009','DD MON YYYY')- TO_DATE('01 MAY 2009','DD MON YYYY')+2
MINUS
SELECT CRAPPY_DATE_STRING
  FROM
FMROCK;

The last select statement yields

MISSING_DATES       
05 MAY 2009         
06 MAY 2009         
07 MAY 2009         
22 MAY 2009

which shows the 'holes' I made in the sample FMROCK table. Is that what you had in mind? Note that to get the dates you want, substitute your start and end dates in the "connect by" part of the last select statement.

Regards

T

 

[fmrock]

seqno is just a unique row identifier.
Service date is the that there should be a date for each day a person was at the facility.

All dates in this system are stored as a juliandate (some as numbers, some as strings) and are allways a pain in the neck.

I have never used the Connect by but is there a way to pull a list of all the dates from DUAL that are between my start/end date?

 

[Thargy]

Yes

just use

SELECT LEVEL L, LPAD(TO_CHAR(LEVEL),2,'0')||' MAY 2009'
  FROM DUAL
CONNECT BY LEVEL < 32;

modified to be

SELECT LPAD(TO_CHAR(LEVEL),2,'0')||' MAY 2009'
  FROM DUAL
CONNECT BY LEVEL < (end_date-start_date);

Try running this and see what you get. Messing around with it a bit should help you to understand what's going on.

as per my instructions above.

Regards

T

 

[SantaMufasa]

is there a way to pull a list of all the dates from DUAL that are between my start/end date?

FM,


Tharg's and Dagon's uses of "...level...dual...connect by level" is a very efficient method to generate an unbroken series of numbers. If you run the following excerpt of Dagon's code, you can see what the above construct does:

select trunc(sysdate)-50+level as adate
from dual
connect by level <=50;

ADATE
---------
19-MAR-09
20-MAR-09
21-MAR-09
22-MAR-09
23-MAR-09
...
03-MAY-09
04-MAY-09
05-MAY-09
06-MAY-09
07-MAY-09

50 rows selected.

So, using Dagon's (and Tharg's) clever use of this construct, the answer to your question is, effectively, "Yes, it can/does 'pull a list of all the dates', but not directly from dual...it produces the dates algorithmically."

Let us know if you have questions about this valuable coding construct (available only in Oracle 10g and above).

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[fmrock]

When I run this
...

select trunc(sysdate)-50+level as adate
from dual
connect by level <=50;

I only get

ADATE
---------
19-MAR-09


When I run

SELECT TRUNC(SYSDATE,'Y')+ROWNUM-1 THEDATE
FROM   ( SELECT 1 just_a_column
         FROM dual
         CONNECT BY LEVEL <= 366
       )
WHERE  ROWNUM <= ADD_MONTHS(TRUNC(SYSDATE,'Y'),12)-TRUNC(SYSDATE,'Y')

I get all the dates for this year. Why does this first query only return one row? Maybe because this database is Oracle9i Release 9.2.0.8.0?

 

[fmrock]

Geezzz.. im sorry.. I am in the wrong forum.

 

[Dagon]

As far as I recall, that technique worked in Oracle 9 as well, so I'm a bit puzzled why you're having problems with it.

You can also use all_objects as an alternative e.g.

select trunc(sysdate)-50+rownum
from all_objects
where rownum <= 50

In terms of the date ranges, you just need to pull enough dates that you can cover the entire range. The BETWEEN on the join back to the account table ensures the table is restricted to the correct dates for those accounts. Just find the lowest and highest service dates of all the data you are interested in and make the date generator query covers all of those.

 

[taupirho]

For Oracle 9 you need to use this construct

select adate from
(
select trunc(sysdate)-50+level as adate
from dual
connect by level <=50
)



In order to understand recursion, you must first understand recursion.

 

[fmrock]

How would you best link this up

SELECT 
	A.ACCTCPCODE Site, 
	A.ACCTCODE Account, 
	C.TYPE, 
	C.PRCODE, 
	to_date(C.ADMITDATE,'j') Admit_Date,
	to_date(C.SERVICEDATE,'j') Service_Date
FROM   
	ACCOUNT A
	JOIN CHARGES C ON ((A.ACCTCPCODE=C.CPCODE) 
		AND (A.ACCTCODE=C.ACCOUNT)
		AND (C.TYPE='C') 
		AND (C.SPLITFLAG IS  NULL))
WHERE   
	(A.ACCTCPCODE='999999')

with all the dates between the Admit date and the max service date?

select adate from
(
select trunc(sysdate)-50+level as adate
from dual
connect by level <=50
)

 

[taupirho]

Well, now you have go back to Dagons post where explains and indeed pretty much shows what you need to do.

Where he has

(select trunc(sysdate)-50+level as adate
from dual
connect by level <=50) d

You will need

(select adate from
(
select trunc(sysdate)-50+level as adate
from dual
connect by level <=50
)) d



In order to understand recursion, you must first understand recursion.

 

[Dagon]

a) To get the date ranges you are interested in, you could use something like:

SELECT
min(to_date(C.ADMITDATE,'j')) as Lowest_Date,
max(to_date(C.SERVICEDATE,'j')) Service_Date,
max(to_date(C.SERVICEDATE,'j')) - min(to_date(C.ADMITDATE,'j')) AS NO_OF_DAYS
FROM
ACCOUNT A
JOIN CHARGES C ON ((A.ACCTCPCODE=C.CPCODE)
AND (A.ACCTCODE=C.ACCOUNT)
AND (C.TYPE='C')
AND (C.SPLITFLAG IS NULL))
WHERE
(A.ACCTCPCODE='999999')

That would enable you to work out the upper and lower bounds of the date generator query. So it would be something like:

(select adate from
(
select <Lowest_Date> +level as adate
from dual
connect by level <= <no_of_days>
)) d

where you substitute the values from the query.

2) In the second stage, you join this to a query which gets the date range for each account:

SELECT
A.ACCTCPCODE Site,
A.ACCTCODE Account,
to_date(C.ADMITDATE,'j') Start_Date,
MAX(to_date(C.SERVICEDATE,'j')) End_Date
FROM
ACCOUNT A
JOIN CHARGES C ON ((A.ACCTCPCODE=C.CPCODE)
AND (A.ACCTCODE=C.ACCOUNT)
AND (C.TYPE='C')
AND (C.SPLITFLAG IS NULL))
WHERE
(A.ACCTCPCODE='999999')
GROUP BY
A.ACCTCPCODE,
A.ACCTCODE,
to_date(C.ADMITDATE,'j')

This would be joined to the resultset from a using a condition like:

adate between start_date and end_date

c) In the last stage you outer join it to your query which returns the date, which is basically just the query you gave.

The whole thing would look something like:

select at.site,
       at.account,
       at.start_date,
       at.charge_date,
       ad.service_date
from
(SELECT 
    A.ACCTCPCODE Site, 
    A.ACCTCODE Account, 
    to_date(C.ADMITDATE,'j') Admit_Date,
    to_date(C.SERVICEDATE,'j') Service_Date
FROM   
    ACCOUNT A
    JOIN CHARGES C ON ((A.ACCTCPCODE=C.CPCODE) 
        AND (A.ACCTCODE=C.ACCOUNT)
        AND (C.TYPE='C') 
        AND (C.SPLITFLAG IS  NULL))
WHERE   
    A.ACCTCPCODE='999999') ad,
(
select
a.site,
a.account,
a.start_date,
d.charge_date
from
(SELECT 
    A.ACCTCPCODE Site, 
    A.ACCTCODE Account, 
    to_date(C.ADMITDATE,'j') Start_Date,
    MAX(to_date(C.SERVICEDATE,'j')) End_Date
FROM   
    ACCOUNT A
    JOIN CHARGES C ON ((A.ACCTCPCODE=C.CPCODE) 
        AND (A.ACCTCODE=C.ACCOUNT)
        AND (C.TYPE='C') 
        AND (C.SPLITFLAG IS  NULL))
WHERE   
    (A.ACCTCPCODE='999999') 
GROUP BY
    A.ACCTCPCODE, 
    A.ACCTCODE, 
    to_date(C.ADMITDATE,'j')) a,
(select adate as charge_date from
(
select <Lowest_Date> +level as adate
from dual
connect by level <= <no_of_days>
)) d
where d.adate between a.start_date and a.end_date) at
where at.site = ad.site (+)
and   at.account = ad.account(+)
and   at.charge_date = ad.service_date(+)

When it's working you will be able to exclude days where there is a match by adding the condition:

and ad.service_date is null

 

[fmrock]

How do you get <Lowest_Date> Abd <no_of_days> into the final query?

 

[fmrock]

I have tried several ways, but allwyas get invalid Identifiery in the part where you add the <lowest_date> and the <no_of_days>