Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 238
  • Last Modified:

Difference between 2 dates for holiday report

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
DarrenJackson
Asked:
DarrenJackson
3 Solutions
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
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
 
DarrenJacksonAuthor Commented:
if you take 'holidays started' less 'holidays ended' it equals 7.

Sorry If I didn't explain
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
so you can just do this right, try this

select trunc(holidays_end - holidays_start ) +1 "No_Of_Days"
from your_table ;
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
Mark GeerlingsDatabase AdministratorCommented:
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
 
sdstuberCommented:
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
 
DarrenJacksonAuthor Commented:
Guys thank you all for helping sdstuber has given me exactly what I need.

Thankyou all for a quick respionse
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now