Avoid multiple start date for given product

Question

Hi All,

I have a requirement to load data into target table, before load data to target table for any product giving new startdate with out expiring old one, those records we need to identify by comparing stage and target table.

Stage table:

Product Startdate Enddate
P1 03-01-2020 03-10-2020
P1 03-11-2020 12/31/2020
P3 07-30-2020 12/31/9999
P4 08-15-2020 12/31/9999

Target table(Initial load):

Product Startdate Enddate
P1 03-01-2020 12/31/9999
P2 01-01-2010 12/31/9999
P3 02-02-2020 12/31/9999
P4 04-01-2020 12/31/9999

In target table product P3 and P4 are active records, from stage table again we are getting active records for same product. this is not expected one, we need to return status value as 1 if everything good returns value as 0.

Regards,
PC

Accepted Answer

Is this one okay:

;WITH CTE AS (
    SELECT Product, MIN(Startdate) AS Startdate, MAX(Enddate) AS Enddate
    FROM @StageTable
    GROUP BY Product
)

SELECT t.*, CASE WHEN s.Enddate IS NULL THEN 0 WHEN t.Enddate = s.Enddate AND t.Startdate = s.Startdate THEN 0 ELSE 1 END IsChanged
FROM @TargetTable AS t
LEFT JOIN CTE AS s ON s.Product = t.Product;

Answer

DECLARE @StageTable TABLE (
    Product varchar(20),
    Startdate date,
    Enddate date
);
INSERT INTO @StageTable VALUES
('P1', '03-01-2020', '03-10-2020'),
('P1', '03-11-2020', '12/31/2020'),
('P3', '07-30-2020', '12/31/9999'),
('P4', '08-15-2020', '12/31/9999');

DECLARE @TargetTable TABLE (
    Product varchar(20),
    Startdate date,
    Enddate date
);
INSERT INTO @TargetTable VALUES
('P1', '03-01-2020', '12/31/9999'),
('P2', '01-01-2010', '12/31/9999'),
('P3', '02-02-2020', '12/31/9999'),
('P4', '04-01-2020', '12/31/9999');

SELECT t.*, CASE WHEN t.Enddate = '9999-12-31' AND s.Enddate = '9999-12-31' AND t.Startdate <> s.Startdate THEN 1 ELSE 0 END IsChanged
FROM @TargetTable AS t
INNER JOIN @StageTable AS s ON s.Product = t.Product;

Answer

Why did you fail to post DDL?
Why don't you know the proper form for dates in SQL?
Why are you using a staging table? Remember this is RDBMS and not a magnetic tape file system.
Why don't you know the difference between a row in a table and a record in a file?

Let me try and fix all the things you got wrong or left out.

You need to get a copy of the PDF of the Rick Snodgrass book on temporal queries and SQL. It is a free download from the University of Arizona

> I have a requirement to load data into target table, before load data to target table for any product giving new startdate without expiring old one, those records [sic] we need to identify by comparing stage and target table. <<

No, you don't. Alexander Kuznetov created constraints that guarantee the end and start dates in the table will be contiguous. Before we get into that, let's write some DDL. The first thing you will notice is that the end date is NULL-able. You will also notice there is a check constraint that guarantees nothing ends before it starts.

CREATE TABLE Products -- raw DDL
(product_id CHAR(2) NOT NULL,
product_startdate DATE NOT NULL,
product_enddate DATE,
CHECK (product_startdate < product_enddate),
PRIMARY KEY(product_id, product_startdate)
);

Why couldn't you write insertion statements? Here's a rewrite using the ISO Standard half open interval model of time and the ISO 8601 temporal display format allowed in ISO Standard SQL. You also mixed – and / in your display formats.

INSERT INTO Products
VALUES
('P1' , '2020-03-01', '2020-03-10'),
('P1' , '2020-03-11', '2020-12-31'),
('P3', '2020-07-30', NULL),
('P4', '2020-08-15', NULL);

Now the full answer:
Kuznetsov’s History Table is a SQL idiom which builds a temporal chain from the current row to the previous row. This is easier to show with code:

CREATE TABLE Tasks
(task_id CHAR(5) NOT NULL,
task_score CHAR(1) NOT NULL,
previous_end_date DATE, -- null means first task
current_start_date DATE DEFAULT CURRENT_TIMESTAMP NOT NULL,
CONSTRAINT previous_end_date_and_current_start_in_sequence
CHECK (prev_end_date <= current_start_date),
current_end_date DATE, -- null means unfinished current task
CONSTRAINT current_start_and_end_dates_in_sequence
CHECK (current_start_date <= current_end_date),
CONSTRAINT end_dates_in_sequence
CHECK (previous_end_date <> current_end_date)
PRIMARY KEY (task_id, current_start_date),
UNIQUE (task_id, previous_end_date), -- null first task
UNIQUE (task_id, current_end_date), -- one null current task
FOREIGN KEY (task_id, previous_end_date) -- self-reference
REFERENCES Tasks (task_id, current_end_date));

Well, that looks complicated! Let’s look at it column by column. Task_id explains itself. The previous_end_date will not have a value for the first task in the chain, so it is NULL-able. The current_start_date and current_end_date are the same data elements, temporal sequence and PRIMARY KEY constraints we had in the simple history table schema.

The two UNIQUE constraints will allow one NULL in their pairs of columns and prevent duplicates. Remember that UNIQUE is not like PRIMARY KEY, which implies UNIQUE NOT NULL.

Finally, the FOREIGN KEY is the real trick. Obviously, the previous task has to end when the current task started for them to abut., so there is another constraint. This constraint is a self-reference that makes sure this is true. Modifying data in this type of table is easy, but requires some thought.. Fortunately, Alex has written a Simple Talk article to explain in more detail how it is done.

Disabling Constraints

Just one little problem with that FOREIGN KEY constraint. It will not let you put the first task into the table. There is nothing for the constraint to reference. In Standard SQL, we can declare constraints to be DEFERABLE with some other options. The idea is that you can turn a constraint ON or OFF during a session so the database can be in state that would otherwise be illegal. But at the end of the session all constraints have to be TRUE. or UNKNOWN.

In SQL Server, you can disable constraints and then turn them back on. It actually is restricted to disabling FOREIGN KEY constraint, and CHECK constraints. PRIMARY KEY, UNIQUE, and DEFAULT constraints are always enforced. The syntax for this is part of the ALTER TABLE statement. The syntax is simple:

ALTER TABLE

NOCHECK CONSTRAINT [ | ALL];
This is why you want to name the constraints; without user given names, you have to look up what the system gave you and they are always long and messy.. The ALL option will disable all of the constraints in the entire schema. Be careful with it.

To re-enable, the syntax is similar and explains itself:

ALTER TABLE

CHECK CONSTRAINT [ | ALL];
When a disabled constraint is re-enabled, the database does not check to ensure any of the existing data meets the constraints. So for this table, The body of a procedure to get things started would look like this:

BEGIN
ALTER TABLE Tasks NOCHECK CONSTRAINT ALL;
INSERT INTO Tasks (task_id, task_score, current_start_date, current_end_date, previous_end_date)
VALUES (1, 'A', '2010-11-01', '2010-11-03', NULL);
ALTER TABLE Tasks CHECK CONSTRAINT ALL;
END;

Answer

Thanks for your quick help Guoxiong,

From sample data the Product P3 and P4 are invalid records because in target table these records are already active.
Where as the product P1 is a valid record because initial load for P1 startdate is '03-01-2020' and end date is '12/31/9999' in incremental load same product is deactivated in '03-10-2020' again activated in '03-11-2020'.

Our query is returns P1 is also an invalid record.

Answer

Hi anonymous user，

Please also refer below and check whether it is helpful to you. If not, please provide more sample data and expected output. Thanks.

DECLARE @StageTable TABLE (  
     Product varchar(20),  
     Startdate date,  
     Enddate date  
 );  
 INSERT INTO @StageTable VALUES  
 ('P1', '03-01-2020', '03-10-2020'),  
 ('P1', '03-11-2020', '12/31/2020'),  
 ('P3', '07-30-2020', '12/31/9999'),  
 ('P4', '08-15-2020', '12/31/9999');  
      
 DECLARE @TargetTable TABLE (  
     Product varchar(20),  
     Startdate date,  
     Enddate date  
 );  
 INSERT INTO @TargetTable VALUES  
 ('P1', '03-01-2020', '12/31/9999'),  
 ('P2', '01-01-2010', '12/31/9999'),  
 ('P3', '02-02-2020', '12/31/9999'),  
 ('P4', '04-01-2020', '12/31/9999');  
   
;WITH CTE AS (  
     SELECT Product, MAX(Startdate) AS Startdate  
     FROM @StageTable  
     GROUP BY Product  
 )  
      
 SELECT t.*, CASE WHEN t.Enddate = '9999-12-31' AND t.Startdate <> s.Startdate THEN 1 ELSE 0 END Invalid  
 FROM @TargetTable AS t  
 LEFT JOIN CTE AS s ON s.Product = t.Product;

Output:

Product	Startdate	Enddate	Invalid  
P1	2020-03-01	9999-12-31	1  
P2	2010-01-01	9999-12-31	0  
P3	2020-02-02	9999-12-31	1  
P4	2020-04-01	9999-12-31	1

Best regards
Melissa

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Hot issues November--What can I do if my transaction log is full?--- Hot issues November
Hot issues November--How to convert Profiler trace into a SQL Server table -- Hot issues November

Share via

Avoid multiple start date for given product

4 additional answers

Your answer