February 9, 2022 at 9:02 pm
Consider the following table that gives the validity dates for which an Item is associated to something called a DU, and a certain Market :
CREATE TABLE Items
(Item_ID VARCHAR(2)
,Start_Date DATE
,End_Date DATE
,DU VARCHAR(2)
,Market VARCHAR(10)
);
INSERT INTO Items VALUES ('X', '2020-06-01', '2020-09-30' , 'A', 'GREECE');
INSERT INTO Items VALUES ('X', '2020-11-01', '2021-01-01' , 'A', 'BELGIUM');
INSERT INTO Items VALUES ('X', '2021-01-01', '2023-12-31' , 'A', 'USA');
INSERT INTO Items VALUES ('Y', '2021-01-01', '2021-06-07' , 'B', 'FRANCE');
INSERT INTO Items VALUES ('Y', '2021-06-07', '2022-01-03' , 'B', 'ITALY');
INSERT INTO Items VALUES ('Y', '2022-01-03', '2023-12-31' , 'B', 'SPAIN');
INSERT INTO Items VALUES ('Z', '2021-05-01', '2021-06-30' , 'C', 'USA');
INSERT INTO Items VALUES ('W', '2021-02-01', '2021-05-30' , 'D', 'SWEDEN');
INSERT INTO Items VALUES ('W', '2023-12-31', '2025-05-30' , 'D', 'DANEMARK');
INSERT INTO Items VALUES ('U', '2021-01-04', '2022-02-07' , 'E', 'RUSSIA');
INSERT INTO Items VALUES ('U', '2022-02-07', '2025-01-05' , NULL, 'RUSSIA');
FYI :You can never have overlapping dates because basically when one Market closes another opens. But it is possible to have non-contiguous periods (like for Item X for example)
Now consider the following table that gives the Classifications of a DU throughout time:
CREATE TABLE Classifications
(DU VARCHAR(2)
,Start_Date DATE
,End_Date DATE
,Classification VARCHAR(2)
);
INSERT INTO Classifications VALUES ('A', '2021-01-01', '2021-04-05', 'N+');
INSERT INTO Classifications VALUES ('A', '2021-04-05', '2023-12-31', 'K-');
INSERT INTO Classifications VALUES ('B', '2021-03-08', '2021-07-26', 'N+');
INSERT INTO Classifications VALUES ('B', '2021-07-26', '2022-11-30', 'L-');
INSERT INTO Classifications VALUES ('C', '2021-01-01', '2023-12-31', 'N-');
INSERT INTO Classifications VALUES ('D', '2021-06-01', '2023-12-31', 'K+');
INSERT INTO Classifications VALUES ('E', '2021-08-02', '2022-01-03', 'N+');
INSERT INTO Classifications VALUES ('E', '2022-01-03', '2025-01-06', 'C-');
FYI : You can only have contiguous periods here. This table is extracted from a Slowly changing dimension.
The goal is to 'slice' the first table to associate to each Item its Classification, while taking into account the time periods. The result should look like this:
| Item_ID | Demand_Unit | Market | Classif_Dt_Begin | Classif_Dt_End | Classif |
|:--------|:-----------:|:---------:|:----------------:|:--------------:|---------:|
| W | D | SWEDEN | 2021-02-01 | 2021-05-30 | (null) |
| X | A | GREECE | 2020-06-01 | 2020-09-30 | (null) |
| X | A | BELGIUM | 2020-11-01 | 2021-01-01 | (null) |
| X | A | USA | 2021-01-01 | 2021-04-05 | N+ |
| X | A | USA | 2021-04-05 | 2023-12-31 | K- |
| Y | B | FRANCE | 2021-01-01 | 2021-03-08 | (null) |
| Y | B | FRANCE | 2021-03-08 | 2021-06-07 | N+ |
| Y | B | ITALY | 2021-06-07 | 2021-07-26 | N+ |
| Y | B | ITALY | 2021-07-26 | 2022-01-03 | L- |
| Y | B | SPAIN | 2022-01-03 | 2022-11-30 | L- |
| Y | B | SPAIN | 2022-11-30 | 2023-12-31 | (null) |
| Z | C | USA | 2021-05-01 | 2021-06-30 | N- |
| U | E | RUSSIA | 2022-02-07 | 2025-01-05 | (null) |
| U | E | RUSSIA | 2021-01-03 | 2021-08-02 | (null) |
| U | E | RUSSIA | 2021-08-02 | 2022-01-03 | N |
| U | E | RUSSIA | 2022-01-03 | 2022-02-07 | C- |
These are some examples with 'tricky' cases (Items X and Y) and a 'simple' case (Item Z) which has its dates completely included in the classifications period, + other cases to try and test possibilities
The idea is Slicing time periods by related time
Any idea or best solution to do that ?
Thanks
February 10, 2022 at 12:48 am
Are you basically trying to find the largest islands you can (contiguous sets of dates)? (Basically where you find the start date and end date for each "island".)
that's a Gaps and Islands question. Pretty sure Dwain Camps has an article about it on here somewhere.
February 10, 2022 at 6:02 am
Here is a link to a post by Drew Allen https://www.sqlservercentral.com/forums/reply/2031829 which covers islands
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply