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

maximus1974
maximus1974 used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
johnsoneSenior Oracle DBA

Commented:
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.

Author

Commented:
Oracle
johnsoneSenior Oracle DBA

Commented:
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.
Ensure you’re charging the right price for your IT

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!

Mark GeerlingsDatabase Administrator

Commented:
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 DBA

Commented:
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.
Most Valuable Expert 2012
Distinguished Expert 2018

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

Author

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.

Author

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
Most Valuable Expert 2012
Distinguished Expert 2018

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

Author

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
Most Valuable Expert 2012
Distinguished Expert 2018
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)))
Mark GeerlingsDatabase Administrator

Commented:
"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.

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