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 :)
Cheers!
d3V1L
Experience is what we get, when we don't get what we want!
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
Regards
Srividhya
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.
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
so what is your expected result for this example?
Der Beginn aller Wissenschaften ist das Erstaunen, dass die Dinge sind, wie sie sind.-Aristoteles
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
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
Cheers!
d3V1L
Experience is what we get, when we don't get what we want!
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.
check dieters post on
http://forums.teradata.com/forum/general/need-help-merging-consecutive-and-overlapping-date-spans
Der Beginn aller Wissenschaften ist das Erstaunen, dass die Dinge sind, wie sie sind.-Aristoteles
Thanks Ulrich!!!
Cheers!
d3V1L
Experience is what we get, when we don't get what we want!
Hi ulrich,
Thanks a lot for the link.
I am using TD13 and have tested with sample data and it is working fine. I hope it works for other records also.
Thanks for the forum and I am very much glad to be part of this excellent forum.
Regards,
Sagar
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.