3U4Et9cx

1IF db_id('kesaap_hw2') IS NOT NULL BEGIN
2 USE master
3 DROP DATABASE "kesaap_hw2"
4END
5GO
6CREATE DATABASE "kesaap_hw2"
7GO
8USE "kesaap_hw2"
9GO
10
11DROP TABLE IF EXISTS Breed
12DROP TABLE IF EXISTS Animal
13
14CREATE TABLE Breed (
15 Id INT NOT NULL,
16 Name VARCHAR(255) NOT NULL,
17 CreatedAt DATETIME2 NOT NULL,
18 DeletedAt DATETIME2 NOT NULL,
19 CONSTRAINT PK_Track PRIMARY KEY (Id, DeletedAt)
20)
21
22CREATE TABLE Animal (
23 Id INT NOT NULL,
24 Name VARCHAR(255) NOT NULL,
25 Age INT,
26 CreatedAt DATETIME2 NOT NULL,
27 DeletedAt DATETIME2 NOT NULL,
28 Breed_Id INT NOT NULL,
29 Breed_DeletedAt DATETIME2
30 CONSTRAINT PK_Animal PRIMARY KEY (Id, DeletedAt),
31 CONSTRAINT FK_Animal_Breed FOREIGN KEY (Breed_Id, Breed_DeletedAt) REFERENCES Breed(Id, DeletedAt)
32)
33
34DECLARE @Time1 DATETIME2
35SELECT @Time1 = '2017-07-30'
36
37INSERT INTO Breed (Id, Name, CreatedAt, DeletedAt) 
38VALUES (1, 'Border Collie', @Time1, '9999-12-31')
39
40INSERT INTO Breed (Id, Name, CreatedAt, DeletedAt) 
41VALUES (2, 'Jack Russell Terrier', @Time1, '9999-12-31')
42
43INSERT INTO Animal (Id, Name, Age, CreatedAt, DeletedAt, Breed_Id, Breed_DeletedAt) 
44VALUES (1, 'Yes', 2, @Time1, '9999-12-31', 1, '9999-12-31')
45
46INSERT INTO Animal (Id, Name, Age, CreatedAt, DeletedAt, Breed_Id, Breed_DeletedAt) 
47VALUES (2, 'Fii', 1, @Time1, '9999-12-31', 1, '9999-12-31')
48
49
50DECLARE @Time2 DATETIME2
51SELECT @Time2 = '2018-07-30'
52DECLARE @OriginalId INT
53SELECT @OriginalId = Id FROM Breed WHERE Name LIKE 'Border Collie'
54
55INSERT INTO Breed (Id, Name, CreatedAt, DeletedAt) SELECT Id, Name, CreatedAt, @Time2 FROM Breed WHERE Id = @OriginalId
56UPDATE Breed SET CreatedAt = @Time2 WHERE Id = @OriginalId AND DeletedAt = '9999-12-31'
57UPDATE Breed SET Name = 'Border Collie v2' WHERE Id = @OriginalId AND DeletedAt = '9999-12-31'
58SELECT * FROM BREED
59
60DECLARE @Time3 DATETIME2
61SELECT @Time3 = '2019-03-21'
62DECLARE @OriginalId2 INT
63SELECT @OriginalId2 = Id FROM Animal WHERE Name LIKE 'Yes'
64
65INSERT INTO Animal (Id, Name, Age, CreatedAt, DeletedAt, Breed_Id, Breed_DeletedAt) 
66SELECT Id, Name, Age, CreatedAt, @Time3, Breed_Id, Breed_DeletedAt FROM Animal WHERE Id = @OriginalId2
67UPDATE Animal SET CreatedAt = @Time3 WHERE Id = @OriginalId2 AND DeletedAt = '9999-12-31'
68UPDATE Animal SET Name = 'No' WHERE Id = @OriginalId2 AND DeletedAt = '9999-12-31'
69SELECT * FROM Animal
70
71DECLARE @TimeNow DATETIME2
72SELECT @TimeNow = '2018-03-20'
73
74SELECT 'Before change'
75SELECT * FROM Breed LEFT JOIN Animal ON Breed.Id = Animal.Breed_Id
76WHERE Breed.CreatedAt <= @TimeNow 
77AND Breed.DeletedAt > @TimeNow 
78AND ((Animal.Id IS NOT NULL 
79AND Animal.CreatedAt <= @TimeNow 
80AND Animal.DeletedAt > @TimeNow)  
81OR (Animal.Id IS NULL)) 
82
83DECLARE @TimeNow2 DATETIME2
84SELECT @TimeNow2 = '2019-03-22'
85
86SELECT 'After change'
87SELECT * FROM Breed LEFT JOIN Animal ON Breed.Id = Animal.Breed_Id
88WHERE Breed.CreatedAt <= @TimeNow2 
89AND Breed.DeletedAt > @TimeNow2
90AND ((Animal.Id IS NOT NULL 
91AND Animal.CreatedAt <= @TimeNow2 
92AND Animal.DeletedAt > @TimeNow2)  
93OR (Animal.Id IS NULL))