seminar data 5

/*SalesOrder */

/* modify and fill sales orders */

-- remove some columns
SET sql_safe_updates=0;

DELETE FROM SalesOrders;
ALTER TABLE SalesOrders
AUTO_INCREMENT 1;

ALTER TABLE SalesOrders
DROP COLUMN LastChangeDate,
DROP COLUMN OrderNumber,
DROP COLUMN VehicleRegistrationNumber,
DROP COLUMN shipdate,
DROP COLUMN     IncludeTransportation,
DROP COLUMN     DriverName,
DROP COLUMN     DriverIdentificationNumber,
DROP COLUMN     TotalVolume,
DROP COLUMN     TransportationAmount;

SELECT * FROM SalesOrders;

-- execute once

INSERT INTO SalesOrders
( OrderDate,TotalAmount, TotalWeight, CustomerId, PriceList_PriceListId, ResponsibleID)
VALUES
(Now(),200,205,1,1,3),
(Now(),400,200,2,1,2),
(Now(),300,205,2,1,3),
(Now(),500,304,2,1,11),
(Now(),700,107,1,1,12),
(Now(),400,204,1,1,17),
(Now(),800,406,2,1,9),
(Now(),550,109,1,1,13),
(Now(),150,205,1,1,2),
(Now(),402,205,2,1,3);

/* SalesorderLines modification*/



DELETE FROM SalesorderLines;
ALTER TABLE SalesorderLines
AUTO_INCREMENT 1;

ALTER TABLE SalesorderLines
DROP COLUMN TaxPercent,
DROP COLUMN DiscountAmount,
DROP COLUMN PriceWithoutTax;


SELECT * FROM SalesorderLines;
SELECT * FROM UnitOfMeasures;
SELECT * FROM Products;

 INSERT INTO SalesorderLines
 (SalesOrderId,ProductId, UnitOfMeasureId, Quantity,SalesPrice, TaxAmount, Subtotal)
 VALUES
 (1,1,1,22,1.8,0.2, 22*3),
 (1,2,1,40,1.8,0.2, 22*3),
 (1,3,1,2,6.8,0.2, 2*7),
 (2,1,1,22,1.8,0.2, 22*3),
 (2,2,1,40,1.8,0.2, 22*3),
 (2,3,1,1,6.8,0.2, 7),
 (2,4,1,1,6.8,0.2, 7),
 (3,1,1,22,1.8,0.2, 22*3),
 (3,2,1,40,1.8,0.2, 22*3),
 (3,3,1,1,6.8,0.2, 7),
 (3,4,1,1,6.8,0.2, 7),
 (4,1,1,22,1.8,0.2, 22*3),
 (4,2,1,40,1.8,0.2, 22*3),
 (4,3,1,2,6.8,0.2, 2*7),
 (5,1,1,22,1.8,0.2, 22*3),
 (5,2,1,40,1.8,0.2, 22*3),
 (5,3,1,2,6.8,0.2, 2*7),
 (6,1,1,22,1.8,0.2, 22*3),
 (6,2,1,40,1.8,0.2, 22*3),
 (6,3,1,2,6.8,0.2, 2*7),
 (7,1,1,22,1.8,0.2, 22*3),
 (7,2,1,40,1.8,0.2, 22*3),
 (7,3,1,2,6.8,0.2, 2*7),
 (8,1,1,22,1.8,0.2, 22*3),
 (8,2,1,40,1.8,0.2, 22*3),
 (8,3,1,10,6.8,0.2, 10*7);