0

Is there a way to get all the distinct contiguous ranges from some overlapping records and in case of overlapping take the records with Type different by "history"?

Here data in Input where key is a combination of account and product used to join history, current and future:

Input reported below:

key start end value type
C1P1M1 12-Jul-21 01-Aug-21 10 history
C1P1M1 02-Aug-21 22-Aug-21 20 history
C1P1M1 23-Aug-21 31-Dec-99 30 history
C1P1M1 19-Jul-21 25-Jul-21 15 current
C1P1M1 30-Aug-21 31-Dec-99 40 future

Here the script for such input records:

DROP TABLE mytable;
CREATE TABLE mytable(
   key   VARCHAR(6) NOT NULL
  ,start DATE  NOT NULL
  ,end   DATE  NOT NULL
  ,value INT NOT NULL
  ,type  VARCHAR(7) NOT NULL
);
INSERT INTO mytable(key,start,end,value,type) VALUES ('C1P1M1','12-Jul-21','01-Aug-21',10,'history');
INSERT INTO mytable(key,start,end,value,type) VALUES ('C1P1M1','02-Aug-21','22-Aug-21',20,'history');
INSERT INTO mytable(key,start,end,value,type) VALUES ('C1P1M1','23-Aug-21','31-Dec-99',30,'history');
INSERT INTO mytable(key,start,end,value,type) VALUES ('C1P1M1','19-Jul-21','25-Jul-21',15,'current');
INSERT INTO mytable(key,start,end,value,type) VALUES ('C1P1M1','30-Aug-21','31-Dec-99',40,'future');

Output is as reported below:

key start end value type
C1P1M1 12-Jul-21 18-Jul-21 10 history
C1P1M1 19-Jul-21 25-Jul-21 15 current
C1P1M1 26-Jul-21 01-Aug-21 10 history
C1P1M1 02-Aug-21 22-Aug-21 20 history
C1P1M1 23-Aug-21 29-Aug-21 30 history
C1P1M1 30-Aug-21 31-Dec-99 40 future

I am out of idea how to implement this requirement and therefore seeking help from this expert group. Thanks for the help.

Note: I have found an old post here: Get distinct consecutive date ranges from overlapping date ranges.

However I do not need summing the values as per that old post but simply get the ones from either current or future line if history overlaps with either current or future. Current and Future can never overlap.

Pasquale Gagliano
  • 1
  • 2
  • Please avoid using image. Post the sample data and expected result as formatted text – Squirrel Mar 21 '21 at 12:03
  • Please read [this](https://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/) for some tips on improving your question. We shouldn't need to type sample data from images while guessing at data types in order to help you. What have _you_ tried? How did it fail to meet your needs? – HABO Mar 21 '21 at 12:37
  • I have replaced the images using the input and expected output in table format. Hope this is what you asked for. Sorry but first time I am posting here! – Pasquale Gagliano Mar 21 '21 at 12:44
  • @HABO thanks a lot for your comment: I learnt very useful tools our of that! – Pasquale Gagliano Mar 21 '21 at 15:17
  • There are numerous issues with you script. If your **key** column is a primary key then its values must be unique. What database platform are you using? Maybe "NUMBER" is a valid datatype in some platforms, but not SQL Server. – Isaac Mar 21 '21 at 16:06
  • @Isaac myTable has no Primary Key. I have updated the script sorry. Value is a numeric field and I corrected this as well – Pasquale Gagliano Mar 21 '21 at 17:43
  • Is there anyone helping me here please? – Pasquale Gagliano Mar 23 '21 at 20:49

0 Answers0