Improve company productivity with a Business Account.Sign Up

x
Solved

# Difference between 2 dates for holiday report

Posted on 2015-01-12
Medium Priority
243 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
Question by:DarrenJackson
6 Comments

LVL 28

Expert Comment

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

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

Sorry If I didn't explain
0

LVL 28

Assisted Solution

Naveen Kumar earned 200 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

LVL 36

Assisted Solution

Mark Geerlings earned 200 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 74

Accepted Solution

sdstuber earned 1600 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

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

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

## Join & Write a Comment Already a member? Login.

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
###### Suggested Courses
Course of the Month3 days, 21 hours left to enroll

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

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