duration calculation between dates which has overlap with other date ranges for same product

Database
Enthusiast

duration calculation between dates which has overlap with other date ranges for same product

Hi,

I have a number of similar products took and activated on different dates like below:

Product Name - Usage Start Date - Usage End Date

PrePaid - 02-Mar-2011 - 10-JUN-2011

PrePaid - 01-JAN-2011 - 31-DEC-2011

PrePaid - 04-JUL-2011 - 15-SEP-2011

PostPaid - 01-JAN-2010 - 31-DEC-2011

PostPaid - 10-JUN-2010 - 20-OCT-2011

Here I have two products only one is PrePaid and PostPaid but with different start dates and different end dates and my requirement is I should calculate the duration by grouping product. my output for above source data is as below

ProductName - Duration of Product in Months

PrePaid - 12

PostPaid - 24 

Below are my comments on the calculation:

Product Name - Usage Start Date - Usage End Date - Comments

PrePaid - 02-Mar-2011 - 10-JUN-2011 - Not taken for calculation as this is overlapping period of next record

PrePaid - 01-JAN-2011 - 31-DEC-2011 - Taken for calculation as this is longest period

PrePaid - 04-JUL-2011 - 15-SEP-2011 - Not taken for calculation as this is overlapping period of previous record

PostPaid - 01-JAN-2010 - 31-DEC-2011 - Taken for calculation as this is longest period

PostPaid - 10-JUN-2010 - 20-OCT-2011 - Not taken for calculation as this is overlapping period of next record

Hope you understood my requirement. Can anyone help me in doing this using query.

Thank you in advance.

11 REPLIES
Enthusiast

Re: duration calculation between dates which has overlap with other date ranges for same product

SEL a.conn_type, a.duration FROM
(SELECT conn_type, start_dt, end_dt, MIN(start_dt) OVER (PARTITION BY conn_type ORDER BY start_dt
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS dt_min, MAX(end_dt) OVER (PARTITION BY conn_type ORDER BY end_dt
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS dt_max, (dt_max - dt_min)/30 AS duration
FROM exercise1) a
QUALIFY (RANK() OVER (PARTITION BY conn_type ORDER BY duration DESC)) =1 ;

Not sure if this is the best solution. Pls share if u get a better one :)

Enthusiast

Re: duration calculation between dates which has overlap with other date ranges for same product

Hi

If you want to avoid OLAP functions then,

You can create a intermidiate table with columns :

Product_Name and Date.

Convert the start and End date to single column and multiple rows and eliminate duplicate rows  by Group by. Then apply count on number of days grouping by product.

This would give the number of days this product was used. However what is the requirement when the data is as follows (observe the year number changed)?

PrePaid - 02-Mar-2012 - 10-JUN-2012

PrePaid - 01-JAN-2011 - 31-DEC-2011

PrePaid - 04-JUL-2011 - 15-SEP-2011

PostPaid - 01-JAN-2010 - 31-DEC-2011

PostPaid - 10-JUN-2010 - 20-OCT-2011

Regards

Srividhya

Enthusiast

Re: duration calculation between dates which has overlap with other date ranges for same product

Hi Srividhya,

I can use OLAP function only. I shall try the solution given by D2V1L.

I will be thankful to you if you share any other best solution.

Regards,

Sagar.

Enthusiast

Re: duration calculation between dates which has overlap with other date ranges for same product

Hi D3V1L and Srividhya,

Thanks for your time answering my query. But, can I get solution for below one in additon to what I stated above:

PrePaid - 02-Mar-2012 - 10-JUN-2012

PrePaid - 01-JAN-2011 - 31-DEC-2011

PrePaid - 04-JUL-2011 - 15-SEP-2011

PrePaid - 01-MAR-2012 - 01-May-2012

There is a gap between 2nd and 4th record which I dont want to calculate.

Thank you in advance.

Regards,

Pavan

Senior Supporter

Re: duration calculation between dates which has overlap with other date ranges for same product

so what is your expected result for this example?

Enthusiast

Re: duration calculation between dates which has overlap with other date ranges for same product

Hi ulrich,

Thanks for your response. The expected result for below mentioned entries is explained below:

PrePaid - 01-JAN-2011 - 31-DEC-2011 - should be considered for calculation

PrePaid - 04-JUL-2011 - 15-SEP-2011 - shouldn't be considered for calculation as it is overlap with previous date combination

PrePaid - 01-FEB-2012 - 31-MAY-2012 - should be considered for calculation

PrePaid - 01-MAR-2012 - 01-MAY-2012 - shouldn't be considered for calculation as it is overlap with previous date combination

The 2nd and 4th entry are excluded for the calculation as these are overlap with previous date combinations.

Hence, the final calculation is as below:

PrePaid - 01-JAN-2011 - 31-DEC-2011 - 365

PrePaid - 01-FEB-2012 - 31-MAY-2012 – 121

The total duration is – 365 + 121 = 486

Hope you understood my requirement.

Thank you in advance.

Regards,

Sagar 

Enthusiast

Re: duration calculation between dates which has overlap with other date ranges for same product

Hi,

Are you expecting to get a situation like below too?

prepaid 01-JAN-2011 - 31-DEC-2011

prepaid 01-FEB-2012 - 01-MAY-2012

prepaid 01-APR-2012 - 31-MAY-2012

Enthusiast

Re: duration calculation between dates which has overlap with other date ranges for same product

Hi D3V1L,

For the stated example, calculation is as below:

prepaid 01-JAN-2011 - 31-DEC-2011 - Different is calculated.

prepaid 01-FEB-2012 - 01-MAY-2012 - Different is calculated.

prepaid 01-APR-2012 - 31-MAY-2012 - Difference is calculated only from 01-MAY-2012 till 31-MAY-2012 as 01-APR-2012 till 01-MAY-2012 is overlap with previous date range.

Hope it is clear.

Thank you in advance.

Regards,

Sagar.

Senior Supporter

Re: duration calculation between dates which has overlap with other date ranges for same product