· 8 years ago · Jan 08, 2018, 03:00 PM
1ALTER DATABASE [AuctionDB] SET TEMPORAL_HISTORY_RETENTION ON
2GO
3
4IF EXISTS ( SELECT *
5 FROM sys.objects
6 WHERE object_id = OBJECT_ID(N'up_Auction_BidsByUser')
7 AND type IN ( N'P', N'PC' ) )
8BEGIN
9 DROP PROC up_Auction_BidsByUser;
10END
11
12IF OBJECT_ID('dbo.Auction', 'U') IS NOT NULL
13BEGIN
14 ALTER TABLE dbo.Auction SET (SYSTEM_VERSIONING = OFF);
15
16 ALTER TABLE dbo.Auction DROP PERIOD FOR SYSTEM_TIME;
17
18 DROP TABLE dbo.AuctionHistory;
19
20 DROP TABLE dbo.Auction;
21END
22
23IF OBJECT_ID('dbo.Category', 'U') IS NOT NULL
24 DROP TABLE dbo.Category
25
26IF OBJECT_ID('dbo.User', 'U') IS NOT NULL
27 DROP TABLE dbo.[User]
28
29CREATE TABLE dbo.Category
30(
31 [ID] int not null PRIMARY KEY CLUSTERED
32 , [Name] nvarchar(100) NOT NULL
33 , [Deleted] bit NOT NULL DEFAULT (0)
34)
35GO
36
37CREATE TABLE dbo.[User]
38(
39 [ID] int not null PRIMARY KEY CLUSTERED
40 , [Name] nvarchar(100) NOT NULL
41 , [Deleted] bit NOT NULL DEFAULT (0)
42)
43GO
44
45CREATE TABLE dbo.Auction
46(
47 [ID] int not null PRIMARY KEY CLUSTERED
48 , [Name] nvarchar(100) NOT NULL
49 , [CategoryID] int not null
50 , [OwnerID] int NOT NULL
51 , [Deleted] bit NOT NULL DEFAULT (0)
52 , [MinimumBid] decimal (12, 5) NOT NULL
53 , [Bid] decimal (12, 5) NULL
54 , [BidderID] int NULL
55 , [ValidFrom] datetime2 (0) GENERATED ALWAYS AS ROW START
56 , [ValidTo] datetime2 (0) GENERATED ALWAYS AS ROW END
57 , PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
58 , CONSTRAINT FK_Category_Auction FOREIGN KEY (CategoryID) REFERENCES Category (ID)
59 , CONSTRAINT FK_Owner_Auction FOREIGN KEY (OwnerID) REFERENCES [User] (ID)
60 , CONSTRAINT FK_Bidder_Auction FOREIGN KEY (BidderID) REFERENCES [User] (ID)
61)
62WITH
63(
64 SYSTEM_VERSIONING = ON
65 (
66 HISTORY_TABLE = dbo.AuctionHistory,
67 -- we can define a different retention here (i.e. 6 MONTHS)
68 HISTORY_RETENTION_PERIOD = INFINITE
69 )
70);
71GO
72
73
74-- Add users John, Peter, Mark and Mary
75INSERT INTO [User] (ID, Name) VALUES (1, 'John'), (2, 'Peter'), (3, 'Mark'), (4, 'Mary')
76
77-- Add categories Adult Toys and Toys
78INSERT INTO Category (ID, Name) VALUES (1, 'Adult Toys'), (2, 'Toys')
79
80-- Add 'Lego' auction in category 'Adult Toys' (wrong; fixed later) and minimu bid 25
81INSERT INTO Auction (ID, Name, OwnerID, CategoryID, MinimumBid) VALUES (1, 'Lego', 1, 1, 25)
82
83-- Peter bids 25
84declare @interval varchar(8) = '00:00:05'
85WAITFOR DELAY @interval
86UPDATE Auction SET Bid = 25, BidderID = 2 WHERE ID = 1
87
88-- Admin corrects the category (Adult Toys -> Toys)
89WAITFOR DELAY @interval
90UPDATE Auction SET CategoryID = 2 WHERE ID = 1
91
92-- Mark bids 28
93WAITFOR DELAY @interval
94UPDATE Auction SET Bid = 28, BidderID = 3 WHERE ID = 1
95
96-- Peter bids 30
97WAITFOR DELAY @interval
98UPDATE Auction SET Bid = 30, BidderID = 2 WHERE ID = 1
99
100-- Mary bids 35
101WAITFOR DELAY @interval
102UPDATE Auction SET Bid = 35, BidderID = 4 WHERE ID = 1
103
104
105-- 4. Display owner report (auction changes) / with actual category and the one at the time of the bid
106SELECT
107 Auction.Name AS Auction
108 , currentCategory.Name as CurrentCategory
109 , Category.Name As BidTimeCategory
110 , Auction.ValidFrom As BidDate
111 , Auction.Bid
112 , [User].Name AS Bidder
113FROM Auction FOR SYSTEM_TIME ALL
114 JOIN Category ON Auction.CategoryID = Category.ID
115 JOIN Auction currentAuction
116 JOIN Category currentCategory ON currentCategory.ID = currentAuction.CategoryID
117 ON currentAuction.ID = Auction.ID
118 JOIN [User] ON [User].ID = Auction.BidderID
119WHERE
120 Auction.ID = 1
121 AND Auction.Bid IS NOT NULL
122ORDER BY
123 Auction.ValidFrom
124
125-- 5. Display Auction Admin reports: per second sum and count of new bids per category (with the category during bid time)
126declare @endDateTime datetime = getdate()
127set @endDateTime = DATEADD(MILLISECOND, -DATEPART(ms, @endDateTime), @endDateTime) -- remove miliseconds
128
129declare @date datetime = DATEADD(SECOND, -30, @endDateTime) -- start 30 seconds ago
130
131; WITH DateIntervalsCTE AS
132(
133 SELECT 0 i, @date AS Date
134 UNION ALL
135 SELECT i + 1, DATEADD(SECOND, i, @date )
136 FROM DateIntervalsCTE
137 WHERE DATEADD(SECOND, i, @date ) <= @endDateTime
138)
139
140SELECT
141 DateIntervalsCTE.Date
142 , Category.Name AS Category
143 , SUM(ISNULL(Auction.Bid, 0)) AS BidTotal
144 , COUNT(Auction.Bid) AS BidCount
145FROM DateIntervalsCTE
146 CROSS JOIN Category
147 LEFT JOIN Auction FOR SYSTEM_TIME ALL ON DateIntervalsCTE.Date = (DATEADD(MILLISECOND, -DATEPART(MILLISECOND, Auction.ValidFrom), Auction.ValidFrom)) AND Category.ID = Auction.CategoryID
148
149GROUP BY
150 DateIntervalsCTE.Date, Category.Name
151ORDER BY
152 DateIntervalsCTE.Date, Category.Name
153GO
154
155-- 6. Display Mary and Peter bids (only taking rows where the Bidder changed).
156CREATE PROC up_Auction_BidsByUser
157 @userID int
158AS
159BEGIN
160 WITH cte_BidsFromUser AS
161 (
162 SELECT
163 Auction.Name As AuctionName
164 , Auction.ValidFrom AS BidTime
165 , Auction.Bid
166 , Auction.BidderID
167 , LAG(BidderID, 1) OVER (ORDER BY ValidFrom DESC) as PreviousBidder
168 , IIF (YEAR(ValidTo) = 9999, 1, 0) AS IsCurrentBid
169
170 FROM Auction FOR SYSTEM_TIME ALL
171 )
172
173 SELECT
174 AuctionName
175 , BidderName = [User].Name
176 , BidTime
177 , Bid
178 , IsCurrentBid
179 FROM cte_BidsFromUser
180 JOIN [User] ON [User].ID = BidderID
181 WHERE
182 BidderID = @userID
183 AND (PreviousBidder IS NULL OR BidderID != PreviousBidder)
184 ORDER BY
185 BidTime
186END
187GO
188
189exec up_Auction_BidsByUser 2
190exec up_Auction_BidsByUser 4