0

I searched the forum posts for similar issue to mine but could not find one. There are other posts on gaps and islands problem with date ranges, but not that works for my case.

Here is the create table and insert queries:

create table test_0907 (PRODUCT_TIER4_DESC nvarchar(15), PRODUCT_TIER5_DESC nvarchar(15), BEGIN_EFFECTIVE_DT date, END_EFFECTIVE_DT date)

insert into test_0907 values ('Cloud', 'Other', '2018-03-01 00:00:00.000', '2020-12-31 00:00:00.000')
insert into test_0907 values ('Cloud', 'Other', '2019-07-01 00:00:00.000', '2020-12-31 00:00:00.000')
insert into test_0907 values ('Cloud', 'Other', '2020-12-01 00:00:00.000', '2020-12-31 00:00:00.000')
insert into test_0907 values ('Cloud', 'Other', '2021-01-01 00:00:00.000', '2021-06-30 00:00:00.000')
insert into test_0907 values ('Other', 'Other', '2021-09-01 00:00:00.000', '2021-09-30 00:00:00.000')
insert into test_0907 values ('Cloud', 'Other', '2021-09-01 00:00:00.000', '9999-12-31 00:00:00.000')
insert into test_0907 values ('Cloud', 'Other', '2022-02-01 00:00:00.000', '9999-12-31 00:00:00.000')

And my output should be :

PRODUCT_TIER4_DESC  | PRODUCT_TIER5_DESC    | BEGIN_EFFECTIVE_DT    | END_EFFECTIVE_DT      |
--------------------------------------------------------------------------------------------|
Cloud               | Other                 | 2018-03-01            | 2021-06-30            |
Other               | Other                 | 2021-09-01            | 2021-09-30            |
Cloud               | Other                 | 2021-09-01            | 9999-12-31            |

I am trying to use lag or lead but cant get the desired output. Is there any other analytical function I should be looking at?

EDIT : This is not same as the related post because the start date in the other post does not overlap. In the example I have the begin_effective_dt for 1st 3 rows has value less than the end_effective_dt of the 3rd row. Hence, this is not the same data set and query will also be different.

Improve this question
2
  • 2
    dbfiddle.uk/Jq1UQDs1 similar to stackoverflow.com/questions/70604632/… Commented Sep 9, 2022 at 11:03
  • This is not same as the related post because the start date in the other post does not overlap. In the example I have the begin_effective_dt for 1st 3 rows has value less than the end_effective_dt of the 3rd row. Hence, this is not the same data set and query will also be different. Commented Sep 9, 2022 at 12:37

1 Answer 1

Reset to default
1

Try the following:

WITH CTE AS
  (
  SELECT *, 
     ROW_NUMBER() OVER (PARTITION BY PRODUCT_TIER5_DESC ORDER BY BEGIN_EFFECTIVE_DT) - 
     ROW_NUMBER() OVER (PARTITION BY PRODUCT_TIER5_DESC,PRODUCT_TIER4_DESC ORDER BY BEGIN_EFFECTIVE_DT)  GRP
  FROM test_0907
  )
SELECT PRODUCT_TIER4_DESC, PRODUCT_TIER5_DESC, 
  MIN(BEGIN_EFFECTIVE_DT) BEGIN_EFFECTIVE_DT, MAX(END_EFFECTIVE_DT) END_EFFECTIVE_DT
FROM CTE
GROUP BY PRODUCT_TIER4_DESC, PRODUCT_TIER5_DESC,GRP

See a demo.

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

This definitely is an alternative to using LAG so not sure why the downvote. Possibly adding an ORDER BY BEGIN_EFFECTIVE_DT,END_EFFECTIVE_DT to get the exact order requested by the OP.
row_number() will not work if there is an earlier BEGIN_EFFECTIVE_DT which covers many BEGIN_EFFECTIVE_DT,END_EFFECTIVE_DT ranges .. dbfiddle.uk/swU8UOjR
The added row doesn't make sense, it's clear that the dates are unique and not overlapped within each group of PRODUCT_TIER4_DESC, PRODUCT_TIER5_DESC

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.