Credit for sales report filing time

Question

I have two date_time fields. The first is the date_time of the sale and the second is the date_time the sales report is filed.

In order for the salesperson to obtain credit, the sales report has to be filed by midnight on the date after the sale was made.

I'm currently calculating the difference, and know that anything over 24 hours could be out of a qualifying time period. Also, there are times when the sales report is filed prior to the date_time of the sale.

I searched through previous answers and couldn't find anything similar.

Oracle SQL. Basically I'm just calculating the difference between sales date and reporting date and flagging anything over 24 hours. Luckily it is currently a small data set. But it will get bigger. — Anna, Mar 25 '15 at 20:10
What exactly is your question? How are you flagging these items? Is this a StoredProc? You're not including enough information for us to provide an answer. — JNYRanger, Mar 25 '15 at 20:16
No, I'm builiding a query. It might become a stored procedure. How can I calculate the difference between the two dates to identify as a Y/N whether the sales report was filed by midnight the next day? In the select statement I have (Sales.Report_Time - Sales.Sales_Time) *24 as Difference — Anna, Mar 25 '15 at 20:33
Welcome to SO. Please show sample input data and expected output. — OldProgrammer, Mar 25 '15 at 20:48
Sample Sales.Report_Time would be 01/15/2015 11:59:00 and Sample Sales.Sales_Time would be 01/15/2015 11:45:00 so this should be a Y because the sales report was filed prior to midnight of January 16th. Thank you for the welcome OldProgrammer! — Anna, Mar 25 '15 at 21:00

score 0 · Accepted Answer · answered Mar 25 '15 at 23:11

You need something like:

select to_char(report_time, 'mm/dd/yyyy hh24:mi:ss') report_time,
    to_char(sales_time, 'mm/dd/yyyy hh24:mi:ss') sales_time,
    round((report_time - sales_time) * 24, 6) diff_in_hours,
    case when report_time < trunc(sales_time) + 2 then 'Y' else 'N' end decision
  from sales

Function trunc() cuts hours, minutes and seconds from sales_time. For instance for date 01/15/2015 11:59:00 it returns 01/15/2015 00:00:00. Next we add 2 days to this result. Report_date has to be lower then this value. So:

report_time < trunc(sales_time) + 2

Test with sample data:

with sales as (select 
    to_date('01/15/2015 11:59:00', 'mm/dd/yyyy hh24:mi:ss') report_time, 
    to_date('01/15/2015 11:45:00', 'mm/dd/yyyy hh24:mi:ss') sales_time from dual
  union all select 
    to_date('01/16/2015 23:59:00', 'mm/dd/yyyy hh24:mi:ss'), 
    to_date('01/15/2015 12:45:00', 'mm/dd/yyyy hh24:mi:ss') from dual
  union all select 
    to_date('01/17/2015 00:00:01', 'mm/dd/yyyy hh24:mi:ss'), 
    to_date('01/15/2015 23:59:00', 'mm/dd/yyyy hh24:mi:ss') from dual)
select to_char(report_time, 'mm/dd/yyyy hh24:mi:ss') report_time,
    to_char(sales_time, 'mm/dd/yyyy hh24:mi:ss') sales_time,
    round((report_time - sales_time) * 24, 6) diff_in_hours,
    case when report_time < trunc(sales_time) + 2 then 'Y' else 'N' end decision
  from sales

Output:

REPORT_TIME         SALES_TIME          DIFF_IN_HOURS DECISION
------------------- ------------------- ------------- --------
01/15/2015 11:59:00 01/15/2015 11:45:00      0,233333 Y        
01/16/2015 23:59:00 01/15/2015 12:45:00     35,233333 Y        
01/17/2015 00:00:01 01/15/2015 23:59:00     24,016944 N

Thank you so much Ponder Stibbons. I'll try this and let you know. Very much appreciated! — Anna, Mar 26 '15 at 19:47
Glad I could help. Please read some basic SQL tutorials, but at first please read [this](http://stackoverflow.com/tour) and this [this](http://stackoverflow.com/help/someone-answers) ;-). — Ponder Stibbons, Mar 26 '15 at 20:46

Credit for sales report filing time

1 Answers1