Solved

Difference between 2 dates for holiday report

Posted on 2015-01-12
6
223 Views
Last Modified: 2015-01-13
Guys, I have a report to write and need a little help.

I need to work out the number of days between 2 dates and any holidays that are booked within the 2 dates I need to return 'no of days' there are. Looking below is an example you will see that the column at the far right 'No of Days' shows how many days there are between the 'employment start date' and 'employment end date' where the 'holidays started' and 'holidays ended' are between

ie..

Emp_no   |   employment start date |  employment end date |   holidays started  |  holidays ended   | No of Days

0001        |           01/01/2015              |           12/01/2015           |         02/01/2015    |       08/01/2015      |        7
0001        |           03/01/2015              |           12/01/2015           |         12/01/2015    |       12/01/2015      |        1
0002        |           04/01/2015              |           12/01/2015           |         10/01/2015    |       15/01/2015      |        3
0003        |           02/01/2015              |           12/01/2015           |         18/01/2015    |       22/01/2015      |        0
0004        |           01/01/2015              |           12/01/2015           |         03/01/2015    |       18/01/2015      |        10
0004        |           01/01/2015              |           12/01/2015           |         22/01/2015    |       25/01/2015      |        0


Hope this helps

Regards
0
Comment
Question by:DarrenJackson
6 Comments
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 40544276
sorry, I do not get it exactl. How did you get 7 in the output for your first test record ? can you explain pls.
0
 

Author Comment

by:DarrenJackson
ID: 40544282
if you take 'holidays started' less 'holidays ended' it equals 7.

Sorry If I didn't explain
0
 
LVL 28

Assisted Solution

by:Naveen Kumar
Naveen Kumar earned 50 total points
ID: 40544299
so you can just do this right, try this

select trunc(holidays_end - holidays_start ) +1 "No_Of_Days"
from your_table ;
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 34

Assisted Solution

by:Mark Geerlings
Mark Geerlings earned 50 total points
ID: 40544309
Oracle SQL supports calculating the number of days between any two dates, but that doesn't allow for weekends or holidays.

And we may need you to clarify whether by "holiday" you mean the British English defination, which includes what we Americans call "vacation", so they may be different for almost every person, or if this is just those days that Americans call "holidays" (Christmas, Easter, etc.) which are likely to be the same for most people in a particular country.  In either case, you will need a table that clearly indicates which dates should be considered "holidays" for the person being evaluated at the time.  You can do a sub-query from this table to count the number of holidays, then subtract that from the result of the simple Oracle SQL difference between two calendar days.

Do weekend dates also need to be counted differently than "normal" Mon-Fri. days for this report?
0
 
LVL 73

Accepted Solution

by:
sdstuber earned 400 total points
ID: 40544559
SELECT emp_no,
       employment_start_date,
       employment_end_date,
       holidays_started,
       holidays_ended,
       CASE WHEN hdays < 0 THEN 0 ELSE hdays END hdays
  FROM (SELECT t.*,
                 (  LEAST(employment_end_date, holidays_ended)
                  - GREATEST(employment_start_date, holidays_started))
               + 1
                   hdays
          FROM yourtable t)
0
 

Author Comment

by:DarrenJackson
ID: 40546168
Guys thank you all for helping sdstuber has given me exactly what I need.

Thankyou all for a quick respionse
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now