All Forums Database
saraider 1 post Joined 10/10
24 Jan 2013
Problem With Non Sequential Dates

I've run into an issue with a rollup I am trying to do and was wondering if anyone has any ideas on how to do it in as few steps as possible.
I have used several olap functions to build a table that has the following data:

PRODUCT  WARR_TYPE      STRT_DT        END DT   
prod1         GOOD            02/03/2011    02/05/2012
prod1         BETTER          01/29/2012    03/01/2013
prod1         GOOD            02/06/2012    05/30/2015
prod2         GOOD            01/24/2012    03/02/2013
 prod2         BETTER          01/24/2012    04/01/2014
My first issue is that for prod1 the BETTER warranty type is right in the middle of 2 GOOD contracts and I want to have a continuous flow of date ranges for a point in time report. The second issue is that the BETTER type totally covers the GOOD type and I would like to only show the BETTER type. Here is how the final data should look.
PRODUCT  WARR_TYPE      STRT_DT        END DT   
prod1         GOOD            02/03/2011    01/29/2012
prod1         BETTER          01/29/2012    03/01/2013
prod1         GOOD            03/01/2013    05/30/2015
prod2         BETTER          01/24/2012    04/01/2014
I can probably do it with several separate statements but wanted to see if there was an easier way.
Thanks

 

You must sign in to leave a comment.