We help IT Professionals succeed at work.

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

110 Views
Last Modified: 2019-02-01
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

johnsoneSenior Oracle DBA
CERTIFIED EXPERT

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
CERTIFIED EXPERT

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.
Mark GeerlingsDatabase Administrator
CERTIFIED EXPERT

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
CERTIFIED EXPERT

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.
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

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
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

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
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
Mark GeerlingsDatabase Administrator
CERTIFIED EXPERT

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.

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions