Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

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

How do we count the number of months in overlapping periods?

Hi,

Suppose we have a table:
Employer | Begin_Date | End_Date
A             | Jan 2010     | Mar 2010
A             | Feb 2010     | Apr 2010
A             | Oct 2010     | Dec 2010
B             | Jan 2010     | May 2010
C             | Aug 2010    | Sep 2010
...

Where the begin date and end date are the date when a project begins and ends.
I want a script to find out how many months in year 2010 employer A will spend on projects. Number of projects can change each year. Difficulty is how we would know that there is an overlap month between Jan2010-Mar2010 and Feb2010-Apr2010.

Thanks in advance
0
shepp_it
Asked:
shepp_it
  • 3
  • 3
  • 2
  • +2
1 Solution
 
tigin44Commented:
simply
select Employer,  MONTHS_BETWEEN(End_Date,  Begin_Date)
from yourTable

Open in new window

0
 
shepp_itAuthor Commented:
I want the total number of month in 2010 for employer A, which should be 7 (Jan, Feb, Mar, Apr, Oct, Nov, Dec).
0
 
Reza RadCommented:
select Employer,  sum(MONTHS_BETWEEN(End_Date,  Begin_Date))
from yourTable
group by Employer
0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
tigin44Commented:
so..
SELECT Employer, SUM(duration)
FROM	(select Employer,  MONTHS_BETWEEN(End_Date,  Begin_Date) AS duration
		from yourTable) A
group by Employer

Open in new window

0
 
HainKurtSr. System AnalystCommented:
lets work on something like this

say M = to_char(sysdate,'mm')

select Employer,  
case when 1 between M1 and M2 then 1 else 0 end E1,
case when 1 between M1 and M2 then 1 else 0 end E2,
..
case when 12 between M1 and M2 then 1 else 0 end E12
from yourTable

say this is Q. Then this will give you

select employer, max(e1) me1, max(e2) me2,..., max(e12) me12 from yourTable group by employer

A 110110000001
B 000111010101

then the count of 1's here will give you the result that you are looking for, right?

say above is R

select employer, (me1 + me2 + me12) MonthsWorked from R
0
 
shepp_itAuthor Commented:
reza and tigin, both of you are  incorrect. Duplicate month shouldn't be included.
For example, if I insert another row (A, Jan 2010, Apr 2010), the number of months should not change because this period overlaps the existing first two rows.
0
 
HainKurtSr. System AnalystCommented:
@ above solution

M1 = to_char(Begin_Date,'mm')
M2 = to_char(Begin_Date,'mm')

Q :

select Employer,  
case when 1 between M1 and M2 then 1 else 0 end E1,
case when 1 between M1 and M2 then 1 else 0 end E2,
..
case when 12 between M1 and M2 then 1 else 0 end E12
from yourTable

R :
select employer, max(e1) me1, max(e2) me2,..., max(e12) me12 from (Q) group by employer

solution :
select employer, (me1 + me2 + .. + me12) MonthsWorked from (R)
0
 
tigin44Commented:
in order to achive your desired result set you should find out the overlapping date intervals and merge them to produce a single record. After that you may get the results by using the commands that I SUPPL0ED
0
 
HainKurtSr. System AnalystCommented:
did you try 26287818? it should give what you want...
0
 
Shaju KumbalathDeputy General Manager - ITCommented:
I don't have a database right now to test it down try something like this
select distinct count(months) from
(select trunc(begin_dt,'mm')+level-1 as months from table where employer'='A' connect by trunc(begin_dt,'mm')+level-1<=trunc(end_date,'mm'));
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

  • 3
  • 3
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now