clear markdown compare help donate or buy diddy dollings comments/suggestions/bugs a leap of faith? bible365
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 582679 fiddles created (13278 in the last week).

-- (1.1) CREATE TABLE Product ( ProductNumber INT NOT NULL, Etcetera CHAR(30) NOT NULL, CONSTRAINT Product_PK PRIMARY KEY (ProductNumber) ); CREATE TABLE Price ( ProductNumber INT NOT NULL, StartDate DATE NOT NULL, Amount INT NOT NULL, -- Retains the amount in cents. CONSTRAINT Price_PK PRIMARY KEY (ProductNumber, StartDate), CONSTRAINT Price_to_Product_FK FOREIGN KEY (ProductNumber) REFERENCES Product (ProductNumber), CONSTRAINT AmountIsValid_CK CHECK (Amount >= 0) ); -- (1.2) CREATE VIEW PriceWithEndDate AS SELECT P.ProductNumber, P.Etcetera AS ProductEtcetera, PR.Amount AS PriceAmount, PR.StartDate, ( SELECT MIN(StartDate) FROM Price InnerPR WHERE P.ProductNumber = InnerPR.ProductNumber AND InnerPR.StartDate > PR.StartDate ) AS EndDate FROM Product P JOIN Price PR ON P.ProductNumber = PR.ProductNumber;
 hidden batch(es)


-- (2) INSERT INTO Product (ProductNumber, Etcetera) VALUES (1750, 'Price time series sample'); INSERT INTO Price (ProductNumber, StartDate, Amount) VALUES (1750, '20170601', 1000), (1750, '20170603', 2000), (1750, '20170605', 3000), (1750, '20170607', 4000);
1 rows affected
4 rows affected
 hidden batch(es)


-- (a) SELECT ProductNumber, ProductEtcetera, PriceAmount, StartDate, EndDate FROM PriceWithEndDate ORDER BY StartDate DESC;
productnumber productetcetera priceamount startdate enddate
1750 Price time series sample 4000 2017-06-07
1750 Price time series sample 3000 2017-06-05 2017-06-07
1750 Price time series sample 2000 2017-06-03 2017-06-05
1750 Price time series sample 1000 2017-06-01 2017-06-03
 hidden batch(es)


-- (b) SELECT ProductNumber, ProductEtcetera, PriceAmount, StartDate, EndDate FROM PriceWithEndDate WHERE ProductNumber = 1750 AND StartDate <= '20170602' AND EndDate >= '20170602';
productnumber productetcetera priceamount startdate enddate
1750 Price time series sample 1000 2017-06-01 2017-06-03
 hidden batch(es)