Need help creating a SQL statement that will count the amount of days between two values

Need help creating a SQL statement that will count the amount of days when a RC_NUMBER is last set to DISP_CODE= QUARANTINE AND DISP_CODE = OK.
Example is attached and the SQL statement I currently have is below. Help is greatly appreciated.
SELECT RC_NUMBER
      ,DISP_CODE
	  ,TIME_STAMP
  FROM RC_AUDIT_LOG
  GROUP BY RC_NUMBER
      ,DISP_CODE
	  ,TIME_STAMP
	  ORDER BY RC_NUMBER, TIME_STAMP 

Open in new window

Capture.JPG
maximus1974Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

johnsoneSenior Oracle DBACommented:
One of the tags on this is Oracle.  The posted query looks a lot like SQL Server syntax.  Which is it?  As the answer for something like this could be different between the two.
maximus1974Author Commented:
Oracle
johnsoneSenior Oracle DBACommented:
There is always the brute force method:
SELECT al1.rc_number, 
       al1.timestamp                                      quarantine_time, 
       al2.time_stamp                                     ok_time, 
       Extract (day FROM al2.times_tamp - al1.time_stamp) day_diff 
FROM   (SELECT rc_number, 
               disp_code, 
               Max(time_stamp) time_stamp 
        FROM   rc_audit_log 
        WHERE  disp_code = 'QUARANTINE' 
        GROUP  BY rc_number, 
                  disp_code) al1 
       join (SELECT rc_number, 
                    disp_code, 
                    Max(time_stamp) time_stamp 
             FROM   rc_audit_log 
             WHERE  disp_code = 'OK' 
             GROUP  BY rc_number, 
                       disp_code) al2 
         ON al1.rc_number = al2.rc_number 

Open in new window

Untested.  Probably a better way to do it, but not going to test from data in an image.  If you can post text test data, ideally in the form of SQL statements to create a table and sample data that would be best.

That assumes that the TIME_STAMP field is actually a TIMESTAMP datatype as shown in the attached image.  If they are DATE datatype, then it is a lot easier, just do the subtraction and it will return a number of days.
Price Your IT Services for Profit

Managed service contracts are great - when they're making you money. Yes, you’re getting paid monthly, but is it actually profitable? Learn to calculate your hourly overhead burden so you can master your IT services pricing strategy.

Mark GeerlingsDatabase AdministratorCommented:
Oracle can do date arithmetic for you in SQL queries, like this for example:
SELECT TIME_STAMP - sysdate "Days"
  FROM RC_AUDIT_LOG
where [some condition is true]

Note that if your date (or timestamp) column includes a non-midnight time-of-day, the value that oracle returns from a query like this will usually include a decimal value.  If you want the result(s) to be in integers only, you will need to use "trunc" on both your column value and on sysdate, or use "round...,0)" like these examples:

SELECT trunc(TIME_STAMP) - trunc(sysdate) "Days"
  FROM RC_AUDIT_LOG
where [some condition is true]

SELECT round(TIME_STAMP - sysdate,0) "Days"
  FROM RC_AUDIT_LOG
where [some condition is true]

In your question, I understand that you want to "count the amount of days [between] when a RC_NUMBER is last set to DISP_CODE= QUARANTINE AND DISP_CODE = OK" and something.  But we don't know what that "something else" is.  Is that always simply the current date, or something else?
johnsoneSenior Oracle DBACommented:
When doing date math, Oracle only returns a number of days when one of the columns is a DATE datatype.  In the example, it appears that both will be TIMESTAMPs, so Oracle will return an INTERVAL, not a number of days.

You also shouldn't mix data types.  SYSDATE is a DATE, you should do your math with like data types to cut down on implicit type conversions.  When using timestamps, you should use SYSTIMESTAMP.
slightwv (䄆 Netminder) Commented:
First:  You don't have any aggregates in your query so the GROUP BY isn't necessary.

You don't say what your expected results are from that data but is is 0 days.

I agree that text sample data will get answers faster since we can use it to create test cases without having to manually type in all the data.

Since the images shows the factional seconds, I assume a timestamp data type.

See if this gives you what you want:
with max_times as (
	select 
		rc_number,
		max(case when disp_code='QUARANTINE' then time_stamp end) max_quarantine,
		max(case when disp_code='OK' then time_stamp end) max_ok
	from rc_audit_log
	group by rc_number
)
select rc_number, extract(day from max_ok-max_quarantine) days_between
from max_times
/

Open in new window

maximus1974Author Commented:
Thank you Mark. There's no "and after OK. RC_number gets placed into DISP_CODE Quarantine, then placed into DISP_CODE OK.  Once the RC_NUMBER is placed into DISP_CODE ok, it continue son to the next step. Need to know the days the RC_NUMBER has spent between Quarantine and OK.
maximus1974Author Commented:
Thanks Slightwv but I am receiving this error when runnig your statement:

ORA-30076: invalid extract field for extract source
30076. 00000 -  "invalid extract field for extract source"
*Cause:    The extract source does not contain the specified extract field.
*Action:
Error at Line: 9 Column: 42
slightwv (䄆 Netminder) Commented:
The ORA-30076 likely means you are using DATE data types not TIMESTAMP data types.

We don't know unless you tell us.

The use what others have posted:  Subtracting dates returns number of days.

Again we have to assume what you want because you still haven't provided the expected results SO, the data you provided will return 0 days.

with max_times as (
	select 
		rc_number,
		max(case when disp_code='QUARANTINE' then time_stamp end) max_quarantine,
		max(case when disp_code='OK' then time_stamp end) max_ok
	from rc_audit_log
	group by rc_number
)
select rc_number, trunc(max_ok-max_quarantine) days_between
from max_times
/

Open in new window

maximus1974Author Commented:
The column named TIME_STAMP  is a data type: DATE in the RC_AUDIT_LOG table. Thanks for your statement. Received no errors after running your complete statement but I am receiving NULL values in days_between.  When I run this statement below on it's own, I am receiving date values.

select 
		rc_number,
		max(case when disp_code='QUARANTINE' then time_stamp end) max_quarantine,
		max(case when disp_code='OK' then time_stamp end) max_ok
	from rc_audit_log
	group by rc_number

Open in new window

Capture.JPG
slightwv (䄆 Netminder) Commented:
If you get a null value then one of the MAX values returned is null.  

In the image you posted, if either or both of the values is null, the subtraction will be null.

Based on the question, I assume you don't want nulls returned?  Then you need to provided a default value for the null data.

For rc RCA1000203 what do you want the max_quarantie value to be for the null?
Same for RCA1000181, you need a default for QUARANTINE and OK.

Whatever you decide just add NVL to it.

For example if you want today:
trunc(nvl(max_ok,sysdate)-nvl(max_quarantine,sysdate))

If you want the defaults to be 01/01/1901:
trunc(nvl(max_ok,to_date('01/01/1901','MM/DD/YYYY))-nvl(max_quarantine,to_date('01/01/1901','MM/DD/YYYY)))

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Mark GeerlingsDatabase AdministratorCommented:
"Need to know the days the RC_NUMBER has spent between Quarantine and OK."  So you want to know the difference in time between two different records in your table for the same RC_NUMBER, not between one of those records and the current date/time.

From the high-lighted data you posted in the Capture.JPG screen shot, it looks like this difference can be much less than a whole day.  How do want portions of a day to be indicated?  By default in Oracle, if you subtract one DATE value from another DATE value, the result will be a number that contains decimal values for the portion of a day difference between the two DATE values.  With the small time difference between these two records in your sample data (of only about one minute) Oracle would display this as .0007 days.  But this could get rounded to simply 0 (zero) depending on how many significant digits your application (or your database) is configured to display by default.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.