Date values

Database
Enthusiast

Date values

one employee take leave from
1) start leave No.1 from 1/12/2014 - 31/01/2015
2) start leave No.2 from 15/12/2015 - 15/01/2016

how many days between 1/1/2015 - 31/12/2015

select emp_code, startday,returnday,actualday from tb1
emp_code startday returnday actualday
101 null null 0
102 1/12/2014 31/01/2015 31
102 15/12/2015 15/01/2016 31
103 null null 0

the answer should become 48 days
emp_code days2015

101 0
102 48
103 0

3 REPLIES
Senior Apprentice

Re: Date values

There's no visible relation between your narrative(s), your Select and your expected answer.

Enthusiast

Re: Date values

Hi Dnoeth,

 

I have to calculate cumulative of  both start day and return dayin 2015.

 

In this case where empcode is 102 returnday is 31/01/2015 so the number of days is 31(01/01/2015 to 31/01/2015)

and in 2nd case where empcode is 102 actualday is 15/12/2015 so the number of days is 17 (from 15/12/2015 to 31/12/2015).Total number of days=31+17=48 days

Senior Apprentice

Re: Date values

Ok, now I got it, you want the number of days on leave in 2015 only.

-- CASE to return only dates in 2015
   Sum(CASE WHEN returnday > DATE '2015-12-31' THEN DATE '2015-12-31' ELSE returnday END -
       CASE WHEN startday  < DATE '2015-01-01' THEN DATE '2015-01-01' ELSE startday  END +1)

Another solution utilizes Periods:

-- Intersect the date range with the year 2015 and caclculate the difference between start and end in days
-- (need to add +1 because periods exclude the end date) Sum(INTERVAL (PERIOD(startday, returnday+1) P_INTERSECT PERIOD(DATE '2015-01-01', DATE '2016-01-01')) DAY(4))