TSQLproject.sql

/*====================================================================================
    Filename: TSQLproject.sql
    Author: Alex Andrei
--------------------------------------------------------------------------------------
    Description:
    This project represents a fictional company called Corvus Tech Services which 
    provides server maintenance for various clients and types of servers and
    showcases a practical solution through which the implementation of SQL Server
    could streamline the data management and process flows within the company.                                   
====================================================================================*/


USE master;
GO

DROP DATABASE IF EXISTS CorvusTechServices;
GO

CREATE DATABASE CorvusTechServices;
GO

USE CorvusTechServices;
GO

CREATE SCHEMA Sales; 
GO
CREATE SCHEMA Person;
GO
CREATE SCHEMA Maintenance;
GO


-- ======================================= --
--             TABLE CREATION              --
-- ======================================= --


CREATE TABLE Person.Person 
    (
    PersonID INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
    FirstName VARCHAR(256) NOT NULL,
    LastName VARCHAR(256) NOT NULL,
    Email VARCHAR(320) NOT NULL,
    Phone VARCHAR(15) NOT NULL
    );
GO

CREATE TABLE Sales.Salesman
    (
    SalesmanID INT IDENTITY(1,1) PRIMARY KEY NOT NULL, 
    PersonID INT FOREIGN KEY REFERENCES Person.Person(PersonID) NOT NULL
    );
GO

CREATE TABLE Sales.Country 
    (
    CountryID INT IDENTITY(1,1) PRIMARY KEY NOT NULL, 
    CountryName VARCHAR (74) NOT NULL
    );
GO

CREATE TABLE Sales.Client
    (
    ClientID INT IDENTITY(1,1) PRIMARY KEY NOT NULL, 
    SalesmanID INT FOREIGN KEY REFERENCES Sales.Salesman(SalesmanID) NOT NULL,
    PersonID INT FOREIGN KEY REFERENCES Person.Person(PersonID) NOT NULL,
    ClientName VARCHAR(256) NOT NULL,
    Address VARCHAR(256) NOT NULL,
    City VARCHAR(256) NOT NULL,
    CountryID INT FOREIGN KEY REFERENCES Sales.Country(CountryID) NOT NULL
    );
GO

CREATE TABLE Sales.Contract
    (
    ContractNumber INT PRIMARY KEY NOT NULL,
    ClientID INT FOREIGN KEY REFERENCES Sales.Client(ClientID) NOT NULL,
    SalesmanID INT FOREIGN KEY REFERENCES Sales.Salesman(SalesmanID) NOT NULL
    );
GO


CREATE TABLE Maintenance.ProductFamily
    (
    FamilyID INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
    FamilyName VARCHAR(50)
    );
GO

CREATE TABLE Maintenance.Product
    (
    ModelID VARCHAR(25) PRIMARY KEY NOT NULL,
    ModelDescription VARCHAR(256),
    FamilyID INT FOREIGN KEY REFERENCES Maintenance.ProductFamily(FamilyID)
    );
GO

CREATE TABLE Maintenance.MaintenanceLevel
    (
    MaintenanceID INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
    MaintenanceDescription VARCHAR(60) NOT NULL
    );
GO

CREATE TABLE Maintenance.Price
    (
    ModelID VARCHAR(25) FOREIGN KEY REFERENCES Maintenance.Product(ModelID) NOT NULL,
    MaintenanceID INT FOREIGN KEY REFERENCES Maintenance.MaintenanceLevel(MaintenanceID) NOT NULL,
    Price DEC(11,2) NOT NULL
    );
GO

CREATE TABLE Maintenance.Serial
    (
    SerialNumber VARCHAR(12) PRIMARY KEY NOT NULL,
    ModelID VARCHAR(25) FOREIGN KEY REFERENCES Maintenance.Product(ModelID) NOT NULL,
    ClientID INT FOREIGN KEY REFERENCES Sales.Client(ClientID) NOT NULL
    );
GO

CREATE TABLE Sales.ContractDetail
    (
    ContractNumber INT FOREIGN KEY REFERENCES Sales.Contract(ContractNumber) NOT NULL,
    ClientID INT FOREIGN KEY REFERENCES Sales.Client(ClientID) NOT NULL, 
    SerialNumber VARCHAR(12) FOREIGN KEY REFERENCES Maintenance.Serial(SerialNumber),
    ModelID VARCHAR(25) FOREIGN KEY REFERENCES Maintenance.Product(ModelID) NOT NULL,
    MaintenanceID INT FOREIGN KEY REFERENCES Maintenance.MaintenanceLevel(MaintenanceID) NOT NULL,
    Quantity INT NOT NULL,
    StartDate DATE NOT NULL,
    EndDate DATE NOT NULL
    );
GO


-- ======================================= --
--                INSERTS                  --
-- ======================================= --


INSERT INTO Person.Person(FirstName,LastName,Email,Phone)
VALUES
    ('Amos','Caulkins','Amos.Caulkins@choam.com','(878) 883-3697'),
    ('Micheline','Medcalf','Micheline.Medcalf@acme.com','(450) 582-6035'),
    ('Ron','Tardiff','Ron.Tardiff@sirius.com','(743) 541-2562'),
    ('Carlotta','Burg','Carlotta.Burg@momcorp.com','(389) 611-7679'),
    ('Meg','Wimberly','Meg.Wimberly@richind.com','(555) 221-1148'),
    ('Austin','Raatz','Austin.Raatz@soylent.com','(483) 942-3848'),
    ('Perry','Ros','Perry.Ros@verybig.com','(670) 472-8674'),
    ('Jeanetta','Tolman','Jeanetta.Tolman@frobozz.com','(722) 777-5871'),
    ('Bridgette','Kavanaugh','Bridgette.Kavanaugh@warbucks.com','(347) 928-1167'),
    ('Corrie','Brent','Corrie.Brent@tyrell.com','(698) 789-5630'),
    ('Barbara','Odriscoll','Barbara.Odriscoll@wayne.com','(920) 728-9238'),
    ('Jarrett','Stallard','Jarrett.Stallard@virtucon.com','(572) 805-6508'),
    ('Stefany','Mckern','Stefany.Mckern@globex.com','(800) 385-4460'),
    ('Gordon','Bissonnette','Gordon.Bissonnette@umbrella.com','(531) 259-2881'),
    ('Karry','Benz','Karry.Benz@wonka.com','(794) 436-5561'),
    ('Stephany','Patague','Stephany.Patague@dell.com','(249) 221-4290'),
    ('Delaine','Keeton','Delaine.Keeton@dell.com','(329) 462-0157'),
    ('Marlo','Constante','Marlo.Constante@dell.com','(299) 211-5284'),
    ('Pablo','Cluck','Pablo.Cluck@dell.com','(292) 462-7976'),
    ('Bambi','Fekete','Bambi.Fekete@dell.com','(264) 956-0577');
GO

INSERT INTO Sales.Salesman (PersonID)
VALUES (16),(17),(18),(19),(20);
GO

INSERT INTO Sales.Country(CountryName)
VALUES
    ('United States'),
    ('Canada'),
    ('United Kingdom'),
    ('Germany'),
    ('Netherlands');
GO

INSERT INTO Sales.Client (SalesmanID, PersonID, ClientName, Address, City, CountryID)
VALUES
    (1,1,'Choam','869 South Manchester St.','Hoboken',1),
    (1,2,'Acme Corp','48 Carpenter Court','Ocoee',1),
    (1,3,'Sirius Cybernetics Corp','8309 Fordham Street','Gastonia',1),
    (2,4,'MomCorp','483 Summer Drive','Marion Bridge',2),
    (2,5,'Rich Industries','638 South Harvard Street','Quispamsis',2),
    (2,6,'Soylent Corp','190 Durham St.','Capitale-Nationale',2),
    (4,7,'Very Big Corp of America','447 West Cedarwood St.','Kitimat',1),
    (3,8,'Frobozz Magic Co','Beursplein 5','Amsterdam',5),
    (3,9,'Warbucks Industries','Ferdinand-Koenigshofen-Str 12','Cologne',4),
    (3,10,'Tyrell Corp','Groenendaal 225-55','Rotterdam',5),
    (4,11,'Wayne Enterprises','9117 Kirkland St.','New York',1),
    (5,12,'Virtucon','932 Kingsway','London',3),
    (5,13,'Globex','9343 London Road','Milton Keynes',3),
    (5,14,'Umbrella Corp','18 West Street','Bournemouth',3),
    (5,15,'Wonka Industries','61 The Green','Wakefield',3);
GO

INSERT INTO Maintenance.ProductFamily(FamilyName)
VALUES
    ('VMAX'),
    ('VNX'),
    ('Recoverpoint'),
    ('Isilon'),
    ('XtremIO'),
    ('Data Domain'),
    ('VPLEX');
GO

INSERT INTO Maintenance.MaintenanceLevel(MaintenanceDescription)
VALUES
    ('Standard'),
    ('Enhanced'),
    ('Premium');
GO

INSERT INTO Maintenance.Product(ModelID,ModelDescription,FamilyID)
VALUES
    ('E-SYS1A-3D','VMAX 200K SYS BAY1 3D',1),
    ('E-1ENG','VMAX VG SINGLE ENGINE SYS BAY',1),
    ('EL6101200FB','VMAX VG 1200GB 10K SAS DRV R6(14+2)',1),
    ('GL6FM1600SB','VMAX200K 1600GB FLASH SPARE',1),
    ('E-FE80000S','VMAX VG 8MM 16G FC',1),
    ('E-ACON3P-50','ADPTR AC 3PH 50A W3-4IN CONDUIT ADPTR',1),
    ('E-DCH3MCBL','VMAX VG DAISY CHAIN 3 METER',1),
    ('EL6101200SB','VMAX VG 1200GB 10K SAS DRV SPARE',1),
    ('GL6FM16005BT2','VMAX200K 1600GB FLASH R5(3+1) 24-31',1),
    ('E-DIR3MCBL','VMAX VG DIRECT CONNECT 3 METER',1),
    ('EA-2048BASE','VMAX 200K BASE 2048GB',1),
    ('E-DE120','VMAX VG 120 SLT DR ENCL',1),
    ('E-1400MOD','VMAX VG FLASH MOD 1400',1),
    ('E-PCBL3DHR','PWR CBL HBL-RSTOL 3D',1),
    ('E-FE80000E','VMAX VG 8MM 8G FIBRE',1),
    ('X410-SATA-S85','X410-70T+1.6T SSD/64G/2x10GE 2x1GE',4),
    ('851-0170','Switch IB QDR 18 Port 1PS 1U Intel',4),
    ('201-0305-CF','SW: Enterprise Adv Bundle Tier 3 Gen5=ID',4),
    ('200-0986-CF','Enterprise-Smartlock Tier 3 Gen5 =ID',4),
    ('200-0359-CF','Enterprise-Smartdedupe Tier 3 Gen5 =ID',4),
    ('200-0404-CF','InsightIQ For OneFS Gen5 =ID',4),
    ('DLM-VNXB54DPE','DLm VNX5400 DPE w/Vault',2),
    ('DLM-VNXBCS','DLm VNX2 Control Station',2),
    ('DLM-VNXB54DME','DLm VNX5400 DME: 2 DM+SLICs+cbls',2),
    ('DLMVNX54DAEHS','DLm VNX5400 15x3.5 2TB Hot Spare DAE',2),
    ('DLM-VNXBCS2','DLm VNX2 2nd Control Station',2),
    ('DLM-VNX54-DAE','DLm VNX5400 15x3.5 2TB SAS DAE',2),
    ('DLMP40U-60-US','POWER KIT FOR US POWER',2),
    ('DLM-VNX2-10PK','DLm VNX2 2TB Viper Drive 10-pack',2),
    ('MODEM-US','UNITED STATES MODEM',2),
    ('DLM-VNX5400BA','DLm2100 VNX5400 VTEC Base',2),
    ('DLM-5400-REPL-CF','DLM VNX5400 REPLICATOR LICENSE =MA',2),
    ('DLM4-2KVTEOS-CF','DLM2100 - VTE OPERATING SYSTEM LICENSE',2),
    ('DLM-V54CAPTBL-CF','DLM VNX5400 STORAGE CAP LICENSE=CB',2),
    ('DLM-VNX5400L-CF','DLM VNX5400 USAGE LICENSE =MA',2),
    ('E-SYS1R-3D','VMAX 400K SYS BAY1 3D',1),
    ('HL6FM16005BT3','VMAX400K 1600GB FLASH R5(3+1) 48-63',1),
    ('ER-2048ADD','VMAX 400K ADD 2048GB',1),
    ('E-FABR','VMAX 400K FABRIC',1),
    ('E-SYSADD3D','VMAX VG ADD SYS BAY 3D',1),
    ('HL6FM1600SB','VMAX400K 1600GB FLASH SPARE',1),
    ('ER-2048BASE','VMAX 400K BASE 2048GB',1),
    ('E-3METERCBL','VMAX VG 3 METER CONTIGIB ETH CABLE',1),
    ('201-0306-CF','SW: Enterprise Adv Bundle Tier 4 Gen5=ID',4),
    ('612-0028','48GB RAM (Next Gen)',4),
    ('NL410-SAT-006','NL410-144T/48G/2x10GE 2x1GE',4),
    ('200-0351-CF','Enterprise-Smartlock Tier 4 Gen5 =ID',4),
    ('200-0360-CF','Enterprise-Smartdedupe Tier 4 Gen5 =ID',4),
    ('X03-CTRLE-1600-T-F','XtremIO - STORAGE CTRL 1.6TB enc tag fld',5),
    ('X03-CTRLE-1600-F','XtremIO - STORAGE CTRL 1.6TB enc fld',5),
    ('X03-D25-1600-E-F','XtremIO 25x1.6TB Flash Drv enc fld OE SW',5),
    ('X02-MNGT-F','XtremIO - MGMT SERVER W/ SOFTWARE - FLD',5),
    ('X02-UPS-220FP','XtremIO - UPS 220V - FLD - Secondary',5),
    ('XIO-RPHWX-G6T','RPA FOR XTREMIO',3),
    ('X02-MNGT-01-CF','XtremIO-MGMT LICENSE QTY 1=IC',5),
    ('X02-RTU-CF','XtremIO Right to Use- OE=IC',5),
    ('X03-D25-1600-E-U','XtremIO 25x1.6TB Flash Drv enc UPG OE SW',5),
    ('X02-IBSW-U','XTREMIO - IB SWITCH UPG',5),
    ('X02-RTU-U-CF','XTREMIO RIGHT TO USE- OE UPG=IC',5),
    ('X02-MNGT-02-U-CF','XTREMIO-MGMT LICENSE QTY 2 UPG=IC',5),
    ('DLM4-VTECBASE','DLm8100 VTEC rack base',2),
    ('DLM4-8100FSM','DLm8100 FICON Single-Mode VTE',2),
    ('DLM4-8KVTEOS','DLm8100 - VTE Operating System license',2),
    ('DLM8K-10GES','DLM8000 10GIGE SWITCH',2),
    ('DLM8K-GES','DLM8000 - 1 GIGE SWITCHES AT-9924TL',2),
    ('EA-512ADD','VMAX 200K ADD 512GB',1),
    ('E-2METERCBL','VMAX VG 2 METER CONTIGIB ETH CABLE',1),
    ('GL6FM800SB','VMAX200K 800GB FLASH SPARE',1),
    ('GL6FM8005BT1','VMAX200K 800GB FLASH R5(3+1) 32-47',1),
    ('E-2ENG','VMAX VG DUAL ENGINE SYS BAY',1),
    ('EA-512BASE','VMAX 200K BASE 512GB',1),
    ('E-FABLA','VMAX 100K/200K FABRIC',1),
    ('EL61012006B','VMAX VG 1200 10K SAS DRV R6(6+2)',1),
    ('456-107-191','DATA AT REST ENCRYPT OS ARRAY=IC',1),
    ('457-101-884','DATA AT REST ENCRYPT OS NEW LIC 200K=IC',1),
    ('456-107-300','VMAX3 REG SNAPSURE ERTU CARD=IC',1),
    ('456-107-293','VMAX3 LOCAL REP REG 0-50TB=CA',1),
    ('457-101-915','V200K LOCAL REP REG BASE LIC=IC',1),
    ('450-000-791','V200K ADVANCED PACKAGE=IC',1),
    ('456-106-427','VMAX3 HYPERMAX OS BASE 151-300TB=CC',1),
    ('457-101-659','HYPERMAX OS BASE NEW 200K=IC',1),
    ('C-ES30-30-B','OPTIONES30 SHELF15X2TB HDD2XLCC',6),
    ('DD860-2E30','SYSTEM DD860+2ES3060TBNFSCIFS',6),
    ('DD860-CTX-B','SYSTEM DD860CTLXNFSCIFS',6),
    ('U-E32-DC','UPGRADE ES20 STORSHELF32 TB DUALCTRL',6),
    ('C-E32-DC','OPTION ES20 STORSHELF32 TB DUALCTRL',6),
    ('VS2-ENG-BASE','VPLEX VS2 BASE ENGINE',7),
    ('VS2-SERVER','VPLEX VS2 MANAGEMENT SERVER',7),
    ('VS2-FM8GFC','CHINOOK FRONT END I/O OPTION (8GFC)',7),
    ('VS2-SWITCH','VPLEX VS2 FC COM SWITCH',7),
    ('VS2-UPS','350VA UPS ROHS COMPLIANT UPS',7),
    ('VS2-ENG-ADD','VPLEX VS2 ENGINE ADD',7),
    ('VS-LOC-PBASE','VPLEX LOCAL BASE (INCL 10 TB)',7),
    ('VS-GSN-PBASE','VPLEX GEOSYNCHRONY BASE (INCL 10TB)',7),
    ('VS-LOC-T4SCL','VPLEX LOCAL LARGE SCALE - TIER 4',7),
    ('VNXB54DP25M','VNX5400 DPE 25X2.5 DRIVE SLOTS-MIN RA',2),
    ('V4-2S10-600','VNX 600GB 10K SAS 25X2.5 DPE/DAE',2),
    ('VNXBCSM','VNXB CONTROL STATION-MINI RACK',2),
    ('VDMBM1GCUA','VNXB 1GBASE-T DM MODULE 4 PORT',2),
    ('VNXB54DMEM','VNX5400 DME: 1 DM+FC SLIC-MINI RACK',2),
    ('VNXBDMCBLD2','VNXB CABLE KIT FOR DPE + 2 DM+1 CS',2),
    ('VSPBM8GFFEA','VNXB 4 PORT 8G FC IO MODULE PAIR',2),
    ('VNXB54DMM','VNX5400 ADD ON DM+FC SLIC-MINI RACK',2),
    ('VNXB6GSDAE15F','VNXB 15X3.5 6G SAS EXP DAE-FIELD INST',2),
    ('VNXB6GSDAE25F','VNXB 25X2.5 6G SAS EXP DAE-FIELD INST',2),
    ('V4-VS07-020U','VNX 2TB NL SAS 15X3.5 DAE UPG',2),
    ('V4-2S15-300U','VNX 300GB 15K SAS 25X2.5 DPE/DAE UPG',2),
    ('VNXB56DP25F','VNX5600 DPE 25X2.5 DRIVE SLOTS-FLD IN',2),
    ('V4-VS07-020','VNX 2TB NL SAS 15X3.5 DAE',2),
    ('VSPBEXPSASBEA','VNXB SAS BE EXP 4 BUSES',2),
    ('VSPBMXGI2A','VNXB 2 PT OP 10G ISCSI IO MOD PR-SFP',2),
    ('VNXB6GSDAE25W','VNXB 25X2.5 6G SAS PRI DAE-FIELD INST',2),
    ('V4-2S15-300','VNX 300GB 15K SAS 25X2.5 DPE/DAE',2),
    ('V4-2S6FX-400','VNX 400GB FAST VP SSD 25X2.5 DPE/DAEE',2),
    ('FLV42S6F-200','VNX 200GB FAST CACHE 25X2.5 DPE/DAE',2),
    ('VNXOE-55','VNX OE LICENSE MODEL FOR VNX5500',2),
    ('VNX5500DP15F','VNX5500 DPE 15X3.5 DRV SLOTS-FLD INST',2),
    ('VX-VS10-900','3.5 IN 900G 10K DISK DRV FOR 6GSDAE-15',2),
    ('VSPMXGI2TWA','VNX 2 PT OP 10G ISCSI IO MOD PR-TWAX',2),
    ('VX-VS15-600','600GB 15K 520BPS 6GB SAS 3.5 CARRIER',2),
    ('VNX6GSDAE15F','VNX 15X3.5 IN 6GB SAS EXP DAE-FLD INST',2),
    ('VX-VS07-010','VNX 1TB NL SAS DRV-15X3.5IN DRV DPE/DAE',2),
    ('FLVXVS6F-100','VNX 100G F CACHE FLASH-15X3.5 DPE/DAE',2),
    ('V-VX-VS1560','3.5 600G 15K VAULT PCK 6GSDAE/DPE',2),
    ('SFP-TWNACT-5M','SFP+ TO SFP+ 5M ACTIVE 10G CABLE',2),
    ('V55-PPAK','Total Protection Pack for VNX5500=IC',2),
    ('VS2-SWITCH-FD','VPLEX VS2 FC COM SWITCH (RACKABLE)',7),
    ('VS2-ENG-AD-FD','VPLEX VS2 ENGINE ADD (RACKABLE)',7),
    ('VS2-UPS-FD','VPLEX VS2 350VA ROHS UPS (RACKABLE)',7),
    ('VS2-SERVER-FD','VPLEX VS2 MANAGEMENT SERVER (RACKABLE)',7),
    ('VS2-CM10GE','VPLEX VS2 10GB ETHERNET COM MODULES',7),
    ('VS2-ENG-BS-FD','VPLEX VS2 BASE ENGINE (RACKABLE)',7),
    ('456-107-873','VPLEX METRO BASE NEW LICENSE=CA',7),
    ('456-107-849','VPLEX GEOSYNCHRONY BASE INCL 10TB=CA',7),
    ('456-107-824','VPLEX Base VNX5500 Frame Based LIC=IC',7),
    ('456-107-837','VPLEX Metro VNX5500 Frame Based LIC=IC',7);
GO

INSERT INTO Maintenance.Price (ModelID,MaintenanceID,Price)
VALUES
    ('E-SYS1A-3D',1,5004),
    ('E-1ENG',1,0),
    ('EL6101200FB',1,249.75),
    ('GL6FM1600SB',1,3390),
    ('E-FE80000S',1,810),
    ('E-ACON3P-50',1,0),
    ('E-DCH3MCBL',1,0),
    ('EL6101200SB',1,249.75),
    ('GL6FM16005BT2',1,2300.25),
    ('E-DIR3MCBL',1,0),
    ('EA-2048BASE',1,92581.5),
    ('E-DE120',1,3795),
    ('E-1400MOD',1,5239.5),
    ('E-PCBL3DHR',1,0),
    ('E-FE80000E',1,310.5),
    ('X410-SATA-S85',1,22441.8),
    ('851-0170',1,698),
    ('201-0305-CF',1,5351.18),
    ('200-0986-CF',1,606.38),
    ('200-0359-CF',1,1952.55),
    ('200-0404-CF',1,0),
    ('DLM-VNXB54DPE',1,2084.38),
    ('DLM-VNXBCS',1,0),
    ('DLM-VNXB54DME',1,1706.63),
    ('DLMVNX54DAEHS',1,995.38),
    ('DLM-VNXBCS2',1,241.88),
    ('DLM-VNX54-DAE',1,436),
    ('DLMP40U-60-US',1,0),
    ('DLM-VNX2-10PK',1,1342.5),
    ('MODEM-US',1,0),
    ('DLM-VNX5400BA',1,0),
    ('DLM-5400-REPL-CF',1,3445.43),
    ('DLM4-2KVTEOS-CF',1,10687.5),
    ('DLM-V54CAPTBL-CF',1,90),
    ('DLM-VNX5400L-CF',1,2417.4),
    ('E-SYS1R-3D',1,5004),
    ('HL6FM16005BT3',1,2058),
    ('ER-2048ADD',1,63357),
    ('E-FABR',1,6984),
    ('E-SYSADD3D',1,2083.5),
    ('HL6FM1600SB',1,3390),
    ('ER-2048BASE',1,168951),
    ('E-3METERCBL',1,0),
    ('201-0306-CF',1,6909.08),
    ('612-0028',1,0),
    ('NL410-SAT-006',1,15978.3),
    ('200-0351-CF',1,783),
    ('200-0360-CF',1,2771.33),
    ('X03-CTRLE-1600-T-F',1,33210.9),
    ('X03-CTRLE-1600-F',1,0),
    ('X03-D25-1600-E-F',1,0),
    ('X02-MNGT-F',1,0),
    ('X02-UPS-220FP',1,0),
    ('XIO-RPHWX-G6T',1,2406.25),
    ('X02-MNGT-01-CF',1,0),
    ('X02-RTU-CF',1,0),
    ('X03-D25-1600-E-U',1,3980.62),
    ('X02-IBSW-U',1,0),
    ('X02-RTU-U-CF',1,0),
    ('X02-MNGT-02-U-CF',1,0),
    ('DLM4-VTECBASE',1,0),
    ('DLM4-8100FSM',1,16250),
    ('DLM4-8KVTEOS',1,22050),
    ('DLM8K-10GES',1,9150),
    ('DLM8K-GES',1,1429.69),
    ('EA-512ADD',1,37983),
    ('E-2METERCBL',1,0),
    ('GL6FM800SB',1,1964.25),
    ('GL6FM8005BT1',1,1402.5),
    ('E-2ENG',1,0),
    ('EA-512BASE',1,37983),
    ('E-FABLA',1,4102.5),
    ('EL61012006B',1,249.75),
    ('456-107-191',1,0),
    ('457-101-884',1,8750),
    ('456-107-300',1,0),
    ('456-107-293',1,479.25),
    ('457-101-915',1,5656.5),
    ('450-000-791',1,7232.63),
    ('456-106-427',1,65.63),
    ('457-101-659',1,790.13),
    ('C-ES30-30-B',1,0),
    ('DD860-2E30',1,43800),
    ('DD860-CTX-B',1,0),
    ('U-E32-DC',1,15277.5),
    ('C-E32-DC',1,13860),
    ('VS2-ENG-BASE',1,5501.25),
    ('VS2-SERVER',1,751.88),
    ('VS2-FM8GFC',1,579.38),
    ('VS2-SWITCH',1,868.13),
    ('VS2-UPS',1,144.38),
    ('VS2-ENG-ADD',1,5501.25),
    ('VS-LOC-PBASE',1,0),
    ('VS-GSN-PBASE',1,0),
    ('VS-LOC-T4SCL',1,0),
    ('VNXB54DP25M',1,1550.63),
    ('V4-2S10-600',1,161.25),
    ('VNXBCSM',1,0),
    ('VDMBM1GCUA',1,277.13),
    ('VNXB54DMEM',1,0),
    ('VNXBDMCBLD2',1,0),
    ('VSPBM8GFFEA',1,693),
    ('VNXB54DMM',1,342.94),
    ('VNXB6GSDAE15F',1,654),
    ('VNXB6GSDAE25F',1,926.63),
    ('V4-VS07-020U',1,168.74),
    ('V4-2S15-300U',1,233.31),
    ('VNXB56DP25F',1,2560.69),
    ('V4-VS07-020',1,167.81),
    ('VSPBEXPSASBEA',1,2439),
    ('VSPBMXGI2A',1,1663.13),
    ('VNXB6GSDAE25W',1,926.63),
    ('V4-2S15-300',1,233.44),
    ('V4-2S6FX-400',1,635.63),
    ('FLV42S6F-200',1,720.94),
    ('VNXOE-55',1,0),
    ('VNX5500DP15F',1,1892.66),
    ('VX-VS10-900',1,327.19),
    ('VSPMXGI2TWA',1,1087.04),
    ('VX-VS15-600',1,302.81),
    ('VNX6GSDAE15F',1,641.25),
    ('VX-VS07-010',1,129.38),
    ('FLVXVS6F-100',1,662.81),
    ('V-VX-VS1560',1,1212.19),
    ('SFP-TWNACT-5M',1,0),
    ('V55-PPAK',1,11110.05),
    ('VS2-SWITCH-FD',1,868.13),
    ('VS2-ENG-AD-FD',1,5501.25),
    ('VS2-UPS-FD',1,144.38),
    ('VS2-SERVER-FD',1,751.88),
    ('VS2-CM10GE',1,579.38),
    ('VS2-ENG-BS-FD',1,5501.25),
    ('456-107-873',1,2025),
    ('456-107-849',1,0),
    ('456-107-824',1,9454.5),
    ('456-107-837',1,22774.5),
    ('E-SYS1A-3D',2,4403.52),
    ('E-1ENG',2,0),
    ('EL6101200FB',2,219.78),
    ('GL6FM1600SB',2,2983.2),
    ('E-FE80000S',2,712.8),
    ('E-ACON3P-50',2,0),
    ('E-DCH3MCBL',2,0),
    ('EL6101200SB',2,219.78),
    ('GL6FM16005BT2',2,2024.22),
    ('E-DIR3MCBL',2,0),
    ('EA-2048BASE',2,81471.72),
    ('E-DE120',2,3339.6),
    ('E-1400MOD',2,4610.76),
    ('E-PCBL3DHR',2,0),
    ('E-FE80000E',2,273.24),
    ('X410-SATA-S85',2,19748.78),
    ('851-0170',2,614.24),
    ('201-0305-CF',2,4709.03),
    ('200-0986-CF',2,533.61),
    ('200-0359-CF',2,1718.24),
    ('200-0404-CF',2,0),
    ('DLM-VNXB54DPE',2,1834.25),
    ('DLM-VNXBCS',2,0),
    ('DLM-VNXB54DME',2,1501.83),
    ('DLMVNX54DAEHS',2,875.93),
    ('DLM-VNXBCS2',2,212.85),
    ('DLM-VNX54-DAE',2,383.68),
    ('DLMP40U-60-US',2,0),
    ('DLM-VNX2-10PK',2,1181.4),
    ('MODEM-US',2,0),
    ('DLM-VNX5400BA',2,0),
    ('DLM-5400-REPL-CF',2,3031.97),
    ('DLM4-2KVTEOS-CF',2,9405),
    ('DLM-V54CAPTBL-CF',2,79.2),
    ('DLM-VNX5400L-CF',2,2127.31),
    ('E-SYS1R-3D',2,4403.52),
    ('HL6FM16005BT3',2,1811.04),
    ('ER-2048ADD',2,55754.16),
    ('E-FABR',2,6145.92),
    ('E-SYSADD3D',2,1833.48),
    ('HL6FM1600SB',2,2983.2),
    ('ER-2048BASE',2,148676.88),
    ('E-3METERCBL',2,0),
    ('201-0306-CF',2,6079.99),
    ('612-0028',2,0),
    ('NL410-SAT-006',2,14060.9),
    ('200-0351-CF',2,689.04),
    ('200-0360-CF',2,2438.77),
    ('X03-CTRLE-1600-T-F',2,29225.59),
    ('X03-CTRLE-1600-F',2,0),
    ('X03-D25-1600-E-F',2,0),
    ('X02-MNGT-F',2,0),
    ('X02-UPS-220FP',2,0),
    ('XIO-RPHWX-G6T',2,2117.5),
    ('X02-MNGT-01-CF',2,0),
    ('X02-RTU-CF',2,0),
    ('X03-D25-1600-E-U',2,3502.94),
    ('X02-IBSW-U',2,0),
    ('X02-RTU-U-CF',2,0),
    ('X02-MNGT-02-U-CF',2,0),
    ('DLM4-VTECBASE',2,0),
    ('DLM4-8100FSM',2,14300),
    ('DLM4-8KVTEOS',2,19404),
    ('DLM8K-10GES',2,8052),
    ('DLM8K-GES',2,1258.13),
    ('EA-512ADD',2,33425.04),
    ('E-2METERCBL',2,0),
    ('GL6FM800SB',2,1728.54),
    ('GL6FM8005BT1',2,1234.2),
    ('E-2ENG',2,0),
    ('EA-512BASE',2,33425.04),
    ('E-FABLA',2,3610.2),
    ('EL61012006B',2,219.78),
    ('456-107-191',2,0),
    ('457-101-884',2,7700),
    ('456-107-300',2,0),
    ('456-107-293',2,421.74),
    ('457-101-915',2,4977.72),
    ('450-000-791',2,6364.71),
    ('456-106-427',2,57.75),
    ('457-101-659',2,695.31),
    ('C-ES30-30-B',2,0),
    ('DD860-2E30',2,38544),
    ('DD860-CTX-B',2,0),
    ('U-E32-DC',2,13444.2),
    ('C-E32-DC',2,12196.8),
    ('VS2-ENG-BASE',2,4841.1),
    ('VS2-SERVER',2,661.65),
    ('VS2-FM8GFC',2,509.85),
    ('VS2-SWITCH',2,763.95),
    ('VS2-UPS',2,127.05),
    ('VS2-ENG-ADD',2,4841.1),
    ('VS-LOC-PBASE',2,0),
    ('VS-GSN-PBASE',2,0),
    ('VS-LOC-T4SCL',2,0),
    ('VNXB54DP25M',2,1364.55),
    ('V4-2S10-600',2,141.9),
    ('VNXBCSM',2,0),
    ('VDMBM1GCUA',2,243.87),
    ('VNXB54DMEM',2,0),
    ('VNXBDMCBLD2',2,0),
    ('VSPBM8GFFEA',2,609.84),
    ('VNXB54DMM',2,301.79),
    ('VNXB6GSDAE15F',2,575.52),
    ('VNXB6GSDAE25F',2,815.43),
    ('V4-VS07-020U',2,148.49),
    ('V4-2S15-300U',2,205.32),
    ('VNXB56DP25F',2,2253.41),
    ('V4-VS07-020',2,147.68),
    ('VSPBEXPSASBEA',2,2146.32),
    ('VSPBMXGI2A',2,1463.55),
    ('VNXB6GSDAE25W',2,815.43),
    ('V4-2S15-300',2,205.43),
    ('V4-2S6FX-400',2,559.35),
    ('FLV42S6F-200',2,634.43),
    ('VNXOE-55',2,0),
    ('VNX5500DP15F',2,1665.54),
    ('VX-VS10-900',2,287.93),
    ('VSPMXGI2TWA',2,956.59),
    ('VX-VS15-600',2,266.48),
    ('VNX6GSDAE15F',2,564.3),
    ('VX-VS07-010',2,113.85),
    ('FLVXVS6F-100',2,583.28),
    ('V-VX-VS1560',2,1066.73),
    ('SFP-TWNACT-5M',2,0),
    ('V55-PPAK',2,9776.84),
    ('VS2-SWITCH-FD',2,763.95),
    ('VS2-ENG-AD-FD',2,4841.1),
    ('VS2-UPS-FD',2,127.05),
    ('VS2-SERVER-FD',2,661.65),
    ('VS2-CM10GE',2,509.85),
    ('VS2-ENG-BS-FD',2,4841.1),
    ('456-107-873',2,1782),
    ('456-107-849',2,0),
    ('456-107-824',2,8319.96),
    ('456-107-837',2,20041.56),
    ('E-SYS1A-3D',3,4003.2),
    ('E-1ENG',3,0),
    ('EL6101200FB',3,199.8),
    ('GL6FM1600SB',3,2712),
    ('E-FE80000S',3,648),
    ('E-ACON3P-50',3,0),
    ('E-DCH3MCBL',3,0),
    ('EL6101200SB',3,199.8),
    ('GL6FM16005BT2',3,1840.2),
    ('E-DIR3MCBL',3,0),
    ('EA-2048BASE',3,74065.2),
    ('E-DE120',3,3036),
    ('E-1400MOD',3,4191.6),
    ('E-PCBL3DHR',3,0),
    ('E-FE80000E',3,248.4),
    ('X410-SATA-S85',3,17953.44),
    ('851-0170',3,558.4),
    ('201-0305-CF',3,4280.94),
    ('200-0986-CF',3,485.1),
    ('200-0359-CF',3,1562.04),
    ('200-0404-CF',3,0),
    ('DLM-VNXB54DPE',3,1667.5),
    ('DLM-VNXBCS',3,0),
    ('DLM-VNXB54DME',3,1365.3),
    ('DLMVNX54DAEHS',3,796.3),
    ('DLM-VNXBCS2',3,193.5),
    ('DLM-VNX54-DAE',3,348.8),
    ('DLMP40U-60-US',3,0),
    ('DLM-VNX2-10PK',3,1074),
    ('MODEM-US',3,0),
    ('DLM-VNX5400BA',3,0),
    ('DLM-5400-REPL-CF',3,2756.34),
    ('DLM4-2KVTEOS-CF',3,8550),
    ('DLM-V54CAPTBL-CF',3,72),
    ('DLM-VNX5400L-CF',3,1933.92),
    ('E-SYS1R-3D',3,4003.2),
    ('HL6FM16005BT3',3,1646.4),
    ('ER-2048ADD',3,50685.6),
    ('E-FABR',3,5587.2),
    ('E-SYSADD3D',3,1666.8),
    ('HL6FM1600SB',3,2712),
    ('ER-2048BASE',3,135160.8),
    ('E-3METERCBL',3,0),
    ('201-0306-CF',3,5527.26),
    ('612-0028',3,0),
    ('NL410-SAT-006',3,12782.64),
    ('200-0351-CF',3,626.4),
    ('200-0360-CF',3,2217.06),
    ('X03-CTRLE-1600-T-F',3,26568.72),
    ('X03-CTRLE-1600-F',3,0),
    ('X03-D25-1600-E-F',3,0),
    ('X02-MNGT-F',3,0),
    ('X02-UPS-220FP',3,0),
    ('XIO-RPHWX-G6T',3,1925),
    ('X02-MNGT-01-CF',3,0),
    ('X02-RTU-CF',3,0),
    ('X03-D25-1600-E-U',3,3184.5),
    ('X02-IBSW-U',3,0),
    ('X02-RTU-U-CF',3,0),
    ('X02-MNGT-02-U-CF',3,0),
    ('DLM4-VTECBASE',3,0),
    ('DLM4-8100FSM',3,13000),
    ('DLM4-8KVTEOS',3,17640),
    ('DLM8K-10GES',3,7320),
    ('DLM8K-GES',3,1143.75),
    ('EA-512ADD',3,30386.4),
    ('E-2METERCBL',3,0),
    ('GL6FM800SB',3,1571.4),
    ('GL6FM8005BT1',3,1122),
    ('E-2ENG',3,0),
    ('EA-512BASE',3,30386.4),
    ('E-FABLA',3,3282),
    ('EL61012006B',3,199.8),
    ('456-107-191',3,0),
    ('457-101-884',3,7000),
    ('456-107-300',3,0),
    ('456-107-293',3,383.4),
    ('457-101-915',3,4525.2),
    ('450-000-791',3,5786.1),
    ('456-106-427',3,52.5),
    ('457-101-659',3,632.1),
    ('C-ES30-30-B',3,0),
    ('DD860-2E30',3,35040),
    ('DD860-CTX-B',3,0),
    ('U-E32-DC',3,12222),
    ('C-E32-DC',3,11088),
    ('VS2-ENG-BASE',3,4401),
    ('VS2-SERVER',3,601.5),
    ('VS2-FM8GFC',3,463.5),
    ('VS2-SWITCH',3,694.5),
    ('VS2-UPS',3,115.5),
    ('VS2-ENG-ADD',3,4401),
    ('VS-LOC-PBASE',3,0),
    ('VS-GSN-PBASE',3,0),
    ('VS-LOC-T4SCL',3,0),
    ('VNXB54DP25M',3,1240.5),
    ('V4-2S10-600',3,129),
    ('VNXBCSM',3,0),
    ('VDMBM1GCUA',3,221.7),
    ('VNXB54DMEM',3,0),
    ('VNXBDMCBLD2',3,0),
    ('VSPBM8GFFEA',3,554.4),
    ('VNXB54DMM',3,274.35),
    ('VNXB6GSDAE15F',3,523.2),
    ('VNXB6GSDAE25F',3,741.3),
    ('V4-VS07-020U',3,134.99),
    ('V4-2S15-300U',3,186.65),
    ('VNXB56DP25F',3,2048.55),
    ('V4-VS07-020',3,134.25),
    ('VSPBEXPSASBEA',3,1951.2),
    ('VSPBMXGI2A',3,1330.5),
    ('VNXB6GSDAE25W',3,741.3),
    ('V4-2S15-300',3,186.75),
    ('V4-2S6FX-400',3,508.5),
    ('FLV42S6F-200',3,576.75),
    ('VNXOE-55',3,0),
    ('VNX5500DP15F',3,1514.13),
    ('VX-VS10-900',3,261.75),
    ('VSPMXGI2TWA',3,869.63),
    ('VX-VS15-600',3,242.25),
    ('VNX6GSDAE15F',3,513),
    ('VX-VS07-010',3,103.5),
    ('FLVXVS6F-100',3,530.25),
    ('V-VX-VS1560',3,969.75),
    ('SFP-TWNACT-5M',3,0),
    ('V55-PPAK',3,8888.04),
    ('VS2-SWITCH-FD',3,694.5),
    ('VS2-ENG-AD-FD',3,4401),
    ('VS2-UPS-FD',3,115.5),
    ('VS2-SERVER-FD',3,601.5),
    ('VS2-CM10GE',3,463.5),
    ('VS2-ENG-BS-FD',3,4401),
    ('456-107-873',3,1620),
    ('456-107-849',3,0),
    ('456-107-824',3,7563.6),
    ('456-107-837',3,18219.6);
GO

INSERT INTO Sales.Contract(ContractNumber,ClientID,SalesmanID)
VALUES
    (578935775,5,2),
    (576678990,13,5),
    (545324997,1,1),
    (560066200,13,5),
    (578898666,6,2),
    (578890096,7,4),
    (568987444,4,2),
    (556897354,4,2),
    (512567053,14,5),
    (512567060,14,5),
    (599800123,10,3),
    (543210567,11,4),
    (564765898,9,3),
    (567893079,12,5),
    (543980751,8,3),
    (509090100,2,1),
    (567098771,15,5),
    (543433198,3,1);
GO

INSERT INTO Maintenance.Serial(SerialNumber,ModelID,ClientID)
VALUES
    ('FF34R0097878','E-SYS1A-3D',5),
    ('FF34R0067895','X410-SATA-S85',13),
    ('FF34R0067896','X410-SATA-S85',13),
    ('FF34R0067897','X410-SATA-S85',13),
    ('FF34R0067898','X410-SATA-S85',13),
    ('FF34R0088907','DLM-VNXB54DPE',1),
    ('FF34R0067876','E-SYS1R-3D',13),
    ('FF34R0019009','NL410-SAT-006',6),
    ('FF34R0019010','NL410-SAT-006',6),
    ('FF34R0019011','NL410-SAT-006',6),
    ('FF34R0019012','NL410-SAT-006',6),
    ('FF34R0099755','NL410-SAT-006',7),
    ('FF34R0099756','NL410-SAT-006',7),
    ('FF34R0099757','NL410-SAT-006',7),
    ('FF34R0099758','NL410-SAT-006',7),
    ('FF34R0099759','NL410-SAT-006',7),
    ('FF34R0099760','NL410-SAT-006',7),
    ('FF34R0099761','NL410-SAT-006',7),
    ('FF34R0087811','X03-CTRLE-1600-T-F',4),
    ('FF34R0010001','XIO-RPHWX-G6T',4),
    ('FF34R0010002','XIO-RPHWX-G6T',4),
    ('FF35R0011299','DLM4-VTECBASE',14),
    ('FF35R0011300','DLM4-VTECBASE',14),
    ('FF35R0098976','E-SYS1A-3D',14),
    ('FF35R0076583','DD860-2E30',10), 
    ('FF35R0009720','DD860-2E30',10),  -- added for fix
    ('FF35R0076578','VS2-ENG-BASE',11),
    ('FF35R0076579','VS2-ENG-BASE',11),
    ('FF35R0076580','VS2-ENG-BASE',11),
    ('FF35R0076581','VS2-ENG-BASE',11),
    ('FF35R0014535','VNXB54DP25M',9),
    ('FF39R0019967','VNXB56DP25F',12),
    ('FF39R0098761','VNXB56DP25F',8),
    ('FF39R0078543','VNX5500DP15F',2),
    ('FF33R0088601','VNX5500DP15F',15),
    ('FF33R0087760','VS2-ENG-BS-FD',3);
GO

INSERT INTO Sales.ContractDetail(ContractNumber,ClientID,SerialNumber,ModelID,MaintenanceID,Quantity,StartDate,EndDate)
VALUES
    (578935775,5,'FF34R0097878','E-SYS1A-3D',2,1,'2019-08-01','2020-07-31'),
    (578935775,5,'FF34R0097878','E-1ENG',2,1,'2019-08-01','2020-07-31'),
    (578935775,5,'FF34R0097878','EL6101200FB',2,96,'2019-08-01','2020-07-31'),
    (578935775,5,'FF34R0097878','GL6FM1600SB',2,1,'2019-08-01','2020-07-31'),
    (578935775,5,'FF34R0097878','E-FE80000S',2,3,'2019-08-01','2020-07-31'),
    (578935775,5,'FF34R0097878','E-ACON3P-50',2,2,'2019-08-01','2020-07-31'),
    (578935775,5,'FF34R0097878','E-DCH3MCBL',2,2,'2019-08-01','2020-07-31'),
    (578935775,5,'FF34R0097878','EL6101200SB',2,6,'2019-08-01','2020-07-31'),
    (578935775,5,'FF34R0097878','GL6FM16005BT2',2,8,'2019-08-01','2020-07-31'),
    (578935775,5,'FF34R0097878','E-DIR3MCBL',2,2,'2019-08-01','2020-07-31'),
    (578935775,5,'FF34R0097878','EA-2048BASE',2,1,'2019-08-01','2020-07-31'),
    (578935775,5,'FF34R0097878','E-DE120',2,4,'2019-08-01','2020-07-31'),
    (578935775,5,'FF34R0097878','GL6FM16005BT2',2,8,'2019-08-01','2020-07-31'),
    (578935775,5,'FF34R0097878','EL6101200FB',2,95,'2019-08-01','2020-07-31'),
    (578935775,5,'FF34R0097878','E-1400MOD',2,4,'2019-08-01','2020-07-31'),
    (578935775,5,'FF34R0097878','GL6FM16005BT2',2,3,'2019-08-01','2020-07-31'),
    (578935775,5,'FF34R0097878','EL6101200FB',2,93,'2019-08-01','2020-07-31'),
    (578935775,5,'FF34R0097878','E-PCBL3DHR',2,1,'2019-08-01','2020-07-31'),
    (578935775,5,'FF34R0097878','E-FE80000E',2,1,'2019-08-01','2020-07-31'),
    (578935775,5,'FF34R0097878','EL6101200SB',2,2,'2019-08-01','2020-07-31'),
    (578935775,5,'FF34R0097878','EL6101200FB',2,100,'2019-08-01','2020-07-31'),
    (578935775,5,'FF34R0097878','GL6FM16005BT2',2,5,'2019-08-01','2020-07-31'),
    (576678990,13,'FF34R0067895','X410-SATA-S85',1,1,'2019-03-20','2020-03-19'),
    (576678990,13,'FF34R0067896','X410-SATA-S85',1,1,'2019-03-20','2020-03-19'),
    (576678990,13,'FF34R0067897','X410-SATA-S85',1,1,'2019-03-20','2020-03-19'),
    (576678990,13,'FF34R0067898','X410-SATA-S85',1,1,'2019-03-20','2020-03-19'),
    (576678990,13,'FF34R0067895','851-0170',1,1,'2019-03-20','2020-03-19'),
    (576678990,13,'FF34R0067897','851-0170',1,1,'2019-03-20','2020-03-19'),
    (576678990,13,'FF34R0067895','201-0305-CF',1,4,'2019-03-20','2020-03-19'),
    (576678990,13,'FF34R0067896','200-0986-CF',1,4,'2019-03-20','2020-03-19'),
    (576678990,13,'FF34R0067897','200-0359-CF',1,4,'2019-03-20','2020-03-19'),
    (576678990,13,'FF34R0067898','200-0404-CF',1,4,'2019-03-20','2020-03-19'),
    (545324997,1,'FF34R0088907','DLM-VNXB54DPE',2,1,'2019-01-01','2019-12-31'),
    (545324997,1,'FF34R0088907','DLM-VNXBCS',2,1,'2019-01-01','2019-12-31'),
    (545324997,1,'FF34R0088907','DLM-VNXB54DME',2,1,'2019-01-01','2019-12-31'),
    (545324997,1,'FF34R0088907','DLMVNX54DAEHS',2,1,'2019-01-01','2019-12-31'),
    (545324997,1,'FF34R0088907','DLM-VNXBCS2',2,1,'2019-01-01','2019-12-31'),
    (545324997,1,'FF34R0088907','DLM-VNX54-DAE',2,3,'2019-01-01','2019-12-31'),
    (545324997,1,'FF34R0088907','DLMP40U-60-US',2,1,'2019-01-01','2019-12-31'),
    (545324997,1,'FF34R0088907','DLM-VNX2-10PK',2,5,'2019-01-01','2019-12-31'),
    (545324997,1,'FF34R0088907','MODEM-US',2,1,'2019-01-01','2019-12-31'),
    (545324997,1,'FF34R0088907','DLM-VNX5400BA',2,1,'2019-01-01','2019-12-31'),
    (545324997,1,'FF34R0088907','DLM-5400-REPL-CF',2,1,'2019-01-01','2019-12-31'),
    (545324997,1,'FF34R0088907','DLM4-2KVTEOS-CF',2,2,'2019-01-01','2019-12-31'),
    (545324997,1,'FF34R0088907','DLM-V54CAPTBL-CF',2,65,'2019-01-01','2019-12-31'),
    (545324997,1,'FF34R0088907','DLM-VNX5400L-CF',2,1,'2019-01-01','2019-12-31'),
    (560066200,13,'FF34R0067876','E-SYS1R-3D',3,1,'2020-01-01','2020-12-31'),
    (560066200,13,'FF34R0067876','E-1400MOD',3,3,'2020-01-01','2020-12-31'),
    (560066200,13,'FF34R0067876','EL6101200FB',3,100,'2020-01-01','2020-12-31'),
    (560066200,13,'FF34R0067876','E-DCH3MCBL',3,2,'2020-01-01','2020-12-31'),
    (560066200,13,'FF34R0067876','EL6101200SB',3,1,'2020-01-01','2020-12-31'),
    (560066200,13,'FF34R0067876','E-1400MOD',3,3,'2020-01-01','2020-12-31'),
    (560066200,13,'FF34R0067876','E-DIR3MCBL',3,2,'2020-01-01','2020-12-31'),
    (560066200,13,'FF34R0067876','E-DE120',3,4,'2020-01-01','2020-12-31'),
    (560066200,13,'FF34R0067876','HL6FM16005BT3',3,8,'2020-01-01','2020-12-31'),
    (560066200,13,'FF34R0067876','EL6101200FB',3,104,'2020-01-01','2020-12-31'),
    (560066200,13,'FF34R0067876','HL6FM16005BT3',3,5,'2020-01-01','2020-12-31'),
    (560066200,13,'FF34R0067876','EL6101200SB',3,5,'2020-01-01','2020-12-31'),
    (560066200,13,'FF34R0067876','HL6FM16005BT3',3,8,'2020-01-01','2020-12-31'),
    (560066200,13,'FF34R0067876','E-DCH3MCBL',3,2,'2020-01-01','2020-12-31'),
    (560066200,13,'FF34R0067876','EL6101200FB',3,89,'2020-01-01','2020-12-31'),
    (560066200,13,'FF34R0067876','ER-2048ADD',3,1,'2020-01-01','2020-12-31'),
    (560066200,13,'FF34R0067876','E-FABR',3,1,'2020-01-01','2020-12-31'),
    (560066200,13,'FF34R0067876','EL6101200FB',3,103,'2020-01-01','2020-12-31'),
    (560066200,13,'FF34R0067876','EL6101200FB',3,100,'2020-01-01','2020-12-31'),
    (560066200,13,'FF34R0067876','EL6101200FB',3,92,'2020-01-01','2020-12-31'),
    (560066200,13,'FF34R0067876','HL6FM16005BT3',3,4,'2020-01-01','2020-12-31'),
    (560066200,13,'FF34R0067876','E-ACON3P-50',3,4,'2020-01-01','2020-12-31'),
    (560066200,13,'FF34R0067876','HL6FM16005BT3',3,5,'2020-01-01','2020-12-31'),
    (560066200,13,'FF34R0067876','E-SYSADD3D',3,1,'2020-01-01','2020-12-31'),
    (560066200,13,'FF34R0067876','HL6FM16005BT3',3,4,'2020-01-01','2020-12-31'),
    (560066200,13,'FF34R0067876','E-FE80000S',3,6,'2020-01-01','2020-12-31'),
    (560066200,13,'FF34R0067876','E-DE120',3,4,'2020-01-01','2020-12-31'),
    (560066200,13,'FF34R0067876','HL6FM16005BT3',3,7,'2020-01-01','2020-12-31'),
    (560066200,13,'FF34R0067876','E-1ENG',3,1,'2020-01-01','2020-12-31'),
    (560066200,13,'FF34R0067876','EL6101200FB',3,101,'2020-01-01','2020-12-31'),
    (560066200,13,'FF34R0067876','E-FE80000E',3,1,'2020-01-01','2020-12-31'),
    (560066200,13,'FF34R0067876','E-FE80000S',3,1,'2020-01-01','2020-12-31'),
    (560066200,13,'FF34R0067876','EL6101200FB',3,95,'2020-01-01','2020-12-31'),
    (560066200,13,'FF34R0067876','EL6101200SB',3,10,'2020-01-01','2020-12-31'),
    (560066200,13,'FF34R0067876','HL6FM1600SB',3,1,'2020-01-01','2020-12-31'),
    (560066200,13,'FF34R0067876','HL6FM16005BT3',3,7,'2020-01-01','2020-12-31'),
    (560066200,13,'FF34R0067876','E-PCBL3DHR',3,2,'2020-01-01','2020-12-31'),
    (560066200,13,'FF34R0067876','E-DIR3MCBL',3,2,'2020-01-01','2020-12-31'),
    (560066200,13,'FF34R0067876','ER-2048BASE',3,1,'2020-01-01','2020-12-31'),
    (560066200,13,'FF34R0067876','HL6FM1600SB',3,1,'2020-01-01','2020-12-31'),
    (560066200,13,'FF34R0067876','E-3METERCBL',3,1,'2020-01-01','2020-12-31'),
    (578898666,6,'FF34R0019009','201-0306-CF',1,4,'2017-06-14','2018-06-15'),
    (578898666,6,'FF34R0019011','200-0404-CF',1,4,'2017-06-14','2018-06-15'),
    (578898666,6,'FF34R0019009','612-0028',1,1,'2017-06-14','2018-06-15'),
    (578898666,6,'FF34R0019010','612-0028',1,1,'2017-06-14','2018-06-15'),
    (578898666,6,'FF34R0019011','612-0028',1,1,'2017-06-14','2018-06-15'),
    (578898666,6,'FF34R0019012','612-0028',1,1,'2017-06-14','2018-06-15'),
    (578898666,6,'FF34R0019009','NL410-SAT-006',1,1,'2017-06-14','2018-06-15'),
    (578898666,6,'FF34R0019010','NL410-SAT-006',1,1,'2017-06-14','2018-06-15'),
    (578898666,6,'FF34R0019011','NL410-SAT-006',1,1,'2017-06-14','2018-06-15'),
    (578898666,6,'FF34R0019012','NL410-SAT-006',1,1,'2017-06-14','2018-06-15'),
    (578890096,7,'FF34R0099755','200-0351-CF',2,7,'2019-04-01','2020-03-31'),
    (578890096,7,'FF34R0099757','200-0404-CF',2,7,'2019-04-01','2020-03-31'),
    (578890096,7,'FF34R0099760','200-0360-CF',2,7,'2019-04-01','2020-03-31'),
    (578890096,7,'FF34R0099761','201-0306-CF',2,7,'2019-04-01','2020-03-31'),
    (578890096,7,'FF34R0099755','NL410-SAT-006',2,1,'2019-04-01','2020-03-31'),
    (578890096,7,'FF34R0099755','612-0028',2,1,'2019-04-01','2020-03-31'),
    (578890096,7,'FF34R0099756','612-0028',2,1,'2019-04-01','2020-03-31'),
    (578890096,7,'FF34R0099757','612-0028',2,1,'2019-04-01','2020-03-31'),
    (578890096,7,'FF34R0099758','612-0028',2,1,'2019-04-01','2020-03-31'),
    (578890096,7,'FF34R0099759','612-0028',2,1,'2019-04-01','2020-03-31'),
    (578890096,7,'FF34R0099760','612-0028',2,1,'2019-04-01','2020-03-31'),
    (578890096,7,'FF34R0099761','612-0028',2,1,'2019-04-01','2020-03-31'),
    (578890096,7,'FF34R0099756','NL410-SAT-006',2,1,'2019-04-01','2020-03-31'),
    (578890096,7,'FF34R0099757','NL410-SAT-006',2,1,'2019-04-01','2020-03-31'),
    (578890096,7,'FF34R0099758','NL410-SAT-006',2,1,'2019-04-01','2020-03-31'),
    (578890096,7,'FF34R0099759','NL410-SAT-006',2,1,'2019-04-01','2020-03-31'),
    (578890096,7,'FF34R0099760','NL410-SAT-006',2,1,'2019-04-01','2020-03-31'),
    (578890096,7,'FF34R0099761','NL410-SAT-006',2,1,'2019-04-01','2020-03-31'),
    (568987444,4,'FF34R0087811','X03-CTRLE-1600-T-F',3,1,'2018-05-17','2019-05-16'),
    (568987444,4,'FF34R0087811','X03-CTRLE-1600-F',3,1,'2018-05-17','2019-05-16'),
    (568987444,4,'FF34R0087811','X03-D25-1600-E-F',3,1,'2018-05-17','2019-05-16'),
    (568987444,4,'FF34R0087811','X02-MNGT-F',3,1,'2018-05-17','2019-05-16'),
    (568987444,4,'FF34R0087811','X02-UPS-220FP',3,2,'2018-05-17','2019-05-16'),
    (568987444,4,'FF34R0087811','XIO-RPHWX-G6T',3,1,'2018-05-17','2019-05-16'),
    (568987444,4,'FF34R0087811','XIO-RPHWX-G6T',3,1,'2018-05-17','2019-05-16'),
    (568987444,4,'FF34R0087811','X02-MNGT-01-CF',3,1,'2018-05-17','2019-05-16'),
    (568987444,4,'FF34R0087811','X02-RTU-CF',3,1,'2018-05-17','2019-05-16'),
    (556897354,4,'FF34R0087811','X03-D25-1600-E-U',3,2,'2020-01-01','2020-12-31'),
    (556897354,4,'FF34R0087811','X02-IBSW-U',3,2,'2020-01-01','2020-12-31'),
    (556897354,4,'FF34R0087811','X02-RTU-U-CF',3,1,'2020-01-01','2020-12-31'),
    (556897354,4,'FF34R0087811','X02-MNGT-02-U-CF',3,1,'2020-01-01','2020-12-31'),
    (512567053,14,'FF35R0011299','DLM4-VTECBASE',2,1,'2020-02-01','2021-01-31'),
    (512567053,14,'FF35R0011299','DLM4-8100FSM',2,5,'2020-02-01','2021-01-31'),
    (512567053,14,'FF35R0011299','DLMP40U-60-US',2,1,'2020-02-01','2021-01-31'),
    (512567053,14,'FF35R0011300','DLM4-VTECBASE',2,1,'2020-02-01','2021-01-31'),
    (512567053,14,'FF35R0011300','DLM4-8100FSM',2,2,'2020-02-01','2021-01-31'),
    (512567053,14,'FF35R0011300','DLMP40U-60-US',2,1,'2020-02-01','2021-01-31'),
    (512567053,14,'FF35R0011299','DLM4-8KVTEOS',2,2,'2020-02-01','2021-01-31'),
    (512567053,14,'FF35R0011300','DLM4-8KVTEOS',2,5,'2020-02-01','2021-01-31'),
    (512567053,14,'FF35R0011299','DLM8K-10GES',2,2,'2020-02-01','2021-01-31'),
    (512567053,14,'FF35R0011300','DLM8K-GES',2,2,'2020-02-01','2021-01-31'),
    (512567060,14,'FF35R0098976','E-SYS1A-3D',2,1,'2017-06-01','2018-05-31'),
    (512567060,14,'FF35R0098976','EA-512ADD',2,1,'2017-06-01','2018-05-31'),
    (512567060,14,'FF35R0098976','E-2METERCBL',2,1,'2017-06-01','2018-05-31'),
    (512567060,14,'FF35R0098976','E-DIR3MCBL',2,2,'2017-06-01','2018-05-31'),
    (512567060,14,'FF35R0098976','GL6FM800SB',2,1,'2017-06-01','2018-05-31'),
    (512567060,14,'FF35R0098976','GL6FM8005BT1',2,16,'2017-06-01','2018-05-31'),
    (512567060,14,'FF35R0098976','E-2ENG',2,1,'2017-06-01','2018-05-31'),
    (512567060,14,'FF35R0098976','E-PCBL3DHR',2,1,'2017-06-01','2018-05-31'),
    (512567060,14,'FF35R0098976','E-1400MOD',2,1,'2017-06-01','2018-05-31'),
    (512567060,14,'FF35R0098976','E-FE80000S',2,64,'2017-06-01','2018-05-31'),
    (512567060,14,'FF35R0098976','E-DE120',2,2,'2017-06-01','2018-05-31'),
    (512567060,14,'FF35R0098976','E-1400MOD',2,1,'2017-06-01','2018-05-31'),
    (512567060,14,'FF35R0098976','GL6FM8005BT1',2,16,'2017-06-01','2018-05-31'),
    (512567060,14,'FF35R0098976','EL6101200SB',2,6,'2017-06-01','2018-05-31'),
    (512567060,14,'FF35R0098976','EA-512BASE',2,1,'2017-06-01','2018-05-31'),
    (512567060,14,'FF35R0098976','GL6FM800SB',2,1,'2017-06-01','2018-05-31'),
    (512567060,14,'FF35R0098976','E-FABLA',2,1,'2017-06-01','2018-05-31'),
    (512567060,14,'FF35R0098976','E-ACON3P-50',2,2,'2017-06-01','2018-05-31'),
    (512567060,14,'FF35R0098976','EL61012006B',2,338,'2017-06-01','2018-05-31'),
    (512567060,14,'FF35R0098976','456-107-191',2,1,'2017-06-01','2018-05-31'),
    (512567060,14,'FF35R0098976','457-101-884',2,1,'2017-06-01','2018-05-31'),
    (512567060,14,'FF35R0098976','456-107-300',2,1,'2017-06-01','2018-05-31'),
    (512567060,14,'FF35R0098976','456-107-293',2,20,'2017-06-01','2018-05-31'),
    (512567060,14,'FF35R0098976','457-101-915',2,1,'2017-06-01','2018-05-31'),
    (512567060,14,'FF35R0098976','450-000-791',2,1,'2017-06-01','2018-05-31'),
    (512567060,14,'FF35R0098976','456-106-427',2,167,'2017-06-01','2018-05-31'),
    (512567060,14,'FF35R0098976','457-101-659',2,1,'2017-06-01','2018-05-31'),
    (599800123,10,'FF35R0009720','C-ES30-30-B',2,1,'2019-03-01','2020-02-29'),
    (599800123,10,'FF35R0009720','DD860-2E30',2,1,'2019-03-01','2020-02-29'),
    (599800123,10,'FF35R0009720','C-ES30-30-B',2,1,'2019-03-01','2020-02-29'),
    (599800123,10,'FF35R0009720','DD860-CTX-B',2,1,'2019-03-01','2020-02-29'),
    (599800123,10,'FF35R0009720','C-ES30-30-B',2,1,'2019-03-01','2020-02-29'),
    (599800123,10,'FF35R0076583','DD860-2E30',2,1,'2019-03-01','2020-02-29'),
    (599800123,10,'FF35R0076583','C-ES30-30-B',2,1,'2019-03-01','2020-02-29'),
    (599800123,10,'FF35R0076583','DD860-CTX-B',2,1,'2019-03-01','2020-02-29'),
    (599800123,10,'FF35R0076583','U-E32-DC',2,1,'2019-03-01','2020-02-29'),
    (599800123,10,'FF35R0076583','C-E32-DC',2,1,'2019-03-01','2020-02-29'),
    (599800123,10,'FF35R0076583','C-E32-DC',2,1,'2019-03-01','2020-02-29'),
    (599800123,10,'FF35R0076583','C-E32-DC',2,1,'2019-03-01','2020-02-29'),
    (543210567,11,'FF35R0076578','VS2-ENG-BASE',1,1,'2019-11-20','2020-11-19'),
    (543210567,11,'FF35R0076578','VS2-SERVER',1,1,'2019-11-20','2020-11-19'),
    (543210567,11,'FF35R0076578','VS2-FM8GFC',1,1,'2019-11-20','2020-11-19'),
    (543210567,11,'FF35R0076579','VS2-ENG-BASE',1,1,'2019-11-20','2020-11-19'),
    (543210567,11,'FF35R0076579','VS2-SWITCH',1,1,'2019-11-20','2020-11-19'),
    (543210567,11,'FF35R0076579','VS2-FM8GFC',1,4,'2019-11-20','2020-11-19'),
    (543210567,11,'FF35R0076579','VS2-UPS',1,2,'2019-11-20','2020-11-19'),
    (543210567,11,'FF35R0076579','VS2-ENG-ADD',1,3,'2019-11-20','2020-11-19'),
    (543210567,11,'FF35R0076579','VS2-SERVER',1,1,'2019-11-20','2020-11-19'),
    (543210567,11,'FF35R0076579','VS2-SWITCH',1,1,'2019-11-20','2020-11-19'),
    (543210567,11,'FF35R0076580','VS2-ENG-BASE',1,1,'2019-11-20','2020-11-19'),
    (543210567,11,'FF35R0076580','VS2-SWITCH',1,1,'2019-11-20','2020-11-19'),
    (543210567,11,'FF35R0076580','VS2-SWITCH',1,1,'2019-11-20','2020-11-19'),
    (543210567,11,'FF35R0076580','VS2-SWITCH',1,1,'2019-11-20','2020-11-19'),
    (543210567,11,'FF35R0076580','VS2-SWITCH',1,1,'2019-11-20','2020-11-19'),
    (543210567,11,'FF35R0076580','VS2-ENG-ADD',1,3,'2019-11-20','2020-11-19'),
    (543210567,11,'FF35R0076580','VS2-UPS',1,2,'2019-11-20','2020-11-19'),
    (543210567,11,'FF35R0076580','VS2-FM8GFC',1,4,'2019-11-20','2020-11-19'),
    (543210567,11,'FF35R0076580','VS2-SERVER',1,1,'2019-11-20','2020-11-19'),
    (543210567,11,'FF35R0076581','VS2-ENG-BASE',1,1,'2019-11-20','2020-11-19'),
    (543210567,11,'FF35R0076581','VS2-FM8GFC',1,4,'2019-11-20','2020-11-19'),
    (543210567,11,'FF35R0076581','VS2-UPS',1,2,'2019-11-20','2020-11-19'),
    (543210567,11,'FF35R0076581','VS2-SERVER',1,1,'2019-11-20','2020-11-19'),
    (543210567,11,'FF35R0076581','VS2-ENG-ADD',1,3,'2019-11-20','2020-11-19'),
    (543210567,11,'FF35R0076578','VS-LOC-PBASE',1,1,'2019-11-20','2020-11-19'),
    (543210567,11,'FF35R0076578','VS-GSN-PBASE',1,1,'2019-11-20','2020-11-19'),
    (543210567,11,'FF35R0076579','VS-GSN-PBASE',1,1,'2019-11-20','2020-11-19'),
    (543210567,11,'FF35R0076579','VS-LOC-PBASE',1,1,'2019-11-20','2020-11-19'),
    (543210567,11,'FF35R0076580','VS-GSN-PBASE',1,1,'2019-11-20','2020-11-19'),
    (543210567,11,'FF35R0076580','VS-LOC-PBASE',1,1,'2019-11-20','2020-11-19'),
    (543210567,11,'FF35R0076581','VS-GSN-PBASE',1,1,'2019-11-20','2020-11-19'),
    (543210567,11,'FF35R0076581','VS-LOC-PBASE',1,1,'2019-11-20','2020-11-19'),
    (543210567,11,'FF35R0076581','VS-LOC-T4SCL',1,2000,'2019-11-20','2020-11-19'),
    (564765898,9,'FF35R0014535','VNXB54DP25M',3,1,'2018-11-01','2019-10-31'),
    (564765898,9,'FF35R0014535','V4-2S10-600',3,10,'2018-11-01','2019-10-31'),
    (564765898,9,'FF35R0014535','VNXBCSM',3,1,'2018-11-01','2019-10-31'),
    (564765898,9,'FF35R0014535','VDMBM1GCUA',3,2,'2018-11-01','2019-10-31'),
    (564765898,9,'FF35R0014535','VNXB54DMEM',3,1,'2018-11-01','2019-10-31'),
    (564765898,9,'FF35R0014535','VNXBDMCBLD2',3,1,'2018-11-01','2019-10-31'),
    (564765898,9,'FF35R0014535','VSPBM8GFFEA',3,1,'2018-11-01','2019-10-31'),
    (564765898,9,'FF35R0014535','VNXB54DMM',3,1,'2018-11-01','2019-10-31'),
    (567893079,12,'FF39R0019967','VNXB6GSDAE15F',2,3,'2020-01-01','2020-12-31'),
    (567893079,12,'FF39R0019967','VNXB6GSDAE25F',2,2,'2020-01-01','2020-12-31'),
    (567893079,12,'FF39R0019967','V4-VS07-020U',2,16,'2020-01-01','2020-12-31'),
    (567893079,12,'FF39R0019967','V4-2S15-300U',2,26,'2020-01-01','2020-12-31'),
    (567893079,12,'FF39R0019967','VNXB56DP25F',2,1,'2020-01-01','2020-12-31'),
    (567893079,12,'FF39R0019967','V4-VS07-020',2,9,'2020-01-01','2020-12-31'),
    (567893079,12,'FF39R0019967','VSPBEXPSASBEA',2,1,'2020-01-01','2020-12-31'),
    (567893079,12,'FF39R0019967','VSPBMXGI2A',2,1,'2020-01-01','2020-12-31'),
    (567893079,12,'FF39R0019967','VNXB6GSDAE25W',2,1,'2020-01-01','2020-12-31'),
    (567893079,12,'FF39R0019967','V4-2S15-300U',2,26,'2020-01-01','2020-12-31'),
    (567893079,12,'FF39R0019967','V4-VS07-020U',2,16,'2020-01-01','2020-12-31'),
    (567893079,12,'FF39R0019967','V4-2S15-300',2,30,'2020-01-01','2020-12-31'),
    (567893079,12,'FF39R0019967','V4-2S6FX-400',2,10,'2020-01-01','2020-12-31'),
    (567893079,12,'FF39R0019967','FLV42S6F-200',2,5,'2020-01-01','2020-12-31'),
    (543980751,8,'FF39R0098761','V4-VS07-020U',1,15,'2019-07-01','2020-06-30'),
    (543980751,8,'FF39R0098761','V4-2S15-300U',1,25,'2019-07-01','2020-06-30'),
    (543980751,8,'FF39R0098761','VNXB56DP25F',1,1,'2019-07-01','2020-06-30'),
    (543980751,8,'FF39R0098761','V4-2S6FX-400',1,10,'2019-07-01','2020-06-30'),
    (543980751,8,'FF39R0098761','VNXB6GSDAE15F',1,2,'2019-07-01','2020-06-30'),
    (543980751,8,'FF39R0098761','FLV42S6F-200',1,5,'2019-07-01','2020-06-30'),
    (543980751,8,'FF39R0098761','VSPBEXPSASBEA',1,1,'2019-07-01','2020-06-30'),
    (543980751,8,'FF39R0098761','VSPBMXGI2A',1,1,'2019-07-01','2020-06-30'),
    (543980751,8,'FF39R0098761','V4-2S15-300',1,56,'2019-07-01','2020-06-30'),
    (543980751,8,'FF39R0098761','VNXB6GSDAE25F',1,2,'2019-07-01','2020-06-30'),
    (543980751,8,'FF39R0098761','VNXB6GSDAE25W',1,1,'2019-07-01','2020-06-30'),
    (543980751,8,'FF39R0098761','V4-VS07-020',1,9,'2019-07-01','2020-06-30'),
    (509090100,2,'FF39R0078543','VNXOE-55',2,1,'2018-07-19','2019-07-18'),
    (509090100,2,'FF39R0078543','VNX5500DP15F',2,1,'2018-07-19','2019-07-18'),
    (509090100,2,'FF39R0078543','VX-VS10-900',2,2,'2018-07-19','2019-07-18'),
    (509090100,2,'FF39R0078543','VX-VS10-900',2,4,'2018-07-19','2019-07-18'),
    (509090100,2,'FF39R0078543','VSPMXGI2TWA',2,1,'2018-07-19','2019-07-18'),
    (509090100,2,'FF39R0078543','VX-VS15-600',2,15,'2018-07-19','2019-07-18'),
    (509090100,2,'FF39R0078543','VNX6GSDAE15F',2,6,'2018-07-19','2019-07-18'),
    (509090100,2,'FF39R0078543','VX-VS07-010',2,13,'2018-07-19','2019-07-18'),
    (509090100,2,'FF39R0078543','VX-VS07-010',2,12,'2018-07-19','2019-07-18'),
    (509090100,2,'FF39R0078543','FLVXVS6F-100',2,7,'2018-07-19','2019-07-18'),
    (509090100,2,'FF39R0078543','VX-VS15-600',2,15,'2018-07-19','2019-07-18'),
    (509090100,2,'FF39R0078543','V-VX-VS1560',2,1,'2018-07-19','2019-07-18'),
    (509090100,2,'FF39R0078543','VX-VS15-600',2,4,'2018-07-19','2019-07-18'),
    (509090100,2,'FF39R0078543','VX-VS15-600',2,11,'2018-07-19','2019-07-18'),
    (509090100,2,'FF39R0078543','VX-VS10-900',2,15,'2018-07-19','2019-07-18'),
    (509090100,2,'FF39R0078543','SFP-TWNACT-5M',2,4,'2018-07-19','2019-07-18'),
    (509090100,2,'FF39R0078543','V55-PPAK',2,1,'2018-07-19','2019-07-18'),
    (567098771,15,'FF33R0088601','VNXOE-55',3,1,'2019-04-17','2020-04-16'),
    (567098771,15,'FF33R0088601','VNX5500DP15F',3,1,'2019-04-17','2020-04-16'),
    (567098771,15,'FF33R0088601','VX-VS10-900',3,2,'2019-04-17','2020-04-16'),
    (567098771,15,'FF33R0088601','VX-VS10-900',3,4,'2019-04-17','2020-04-16'),
    (567098771,15,'FF33R0088601','VSPMXGI2TWA',3,1,'2019-04-17','2020-04-16'),
    (567098771,15,'FF33R0088601','VX-VS15-600',3,15,'2019-04-17','2020-04-16'),
    (567098771,15,'FF33R0088601','VNX6GSDAE15F',3,6,'2019-04-17','2020-04-16'),
    (567098771,15,'FF33R0088601','VX-VS07-010',3,13,'2019-04-17','2020-04-16'),
    (567098771,15,'FF33R0088601','VX-VS07-010',3,12,'2019-04-17','2020-04-16'),
    (567098771,15,'FF33R0088601','FLVXVS6F-100',3,7,'2019-04-17','2020-04-16'),
    (567098771,15,'FF33R0088601','VX-VS15-600',3,15,'2019-04-17','2020-04-16'),
    (567098771,15,'FF33R0088601','V-VX-VS1560',3,1,'2019-04-17','2020-04-16'),
    (567098771,15,'FF33R0088601','VX-VS15-600',3,4,'2019-04-17','2020-04-16'),
    (567098771,15,'FF33R0088601','VX-VS15-600',3,11,'2019-04-17','2020-04-16'),
    (567098771,15,'FF33R0088601','VX-VS10-900',3,15,'2019-04-17','2020-04-16'),
    (567098771,15,'FF33R0088601','SFP-TWNACT-5M',3,4,'2019-04-17','2020-04-16'),
    (567098771,15,'FF33R0088601','V55-PPAK',3,1,'2019-04-17','2020-04-16'),
    (543433198,3,'FF33R0087760','VS2-SWITCH-FD',1,1,'2019-10-10','2020-10-09'),
    (543433198,3,'FF33R0087760','VS2-ENG-AD-FD',1,1,'2019-10-10','2020-10-09'),
    (543433198,3,'FF33R0087760','VS2-UPS-FD',1,2,'2019-10-10','2020-10-09'),
    (543433198,3,'FF33R0087760','VS2-SERVER-FD',1,1,'2019-10-10','2020-10-09'),
    (543433198,3,'FF33R0087760','VS2-FM8GFC',1,1,'2019-10-10','2020-10-09'),
    (543433198,3,'FF33R0087760','VS2-FM8GFC',1,1,'2019-10-10','2020-10-09'),
    (543433198,3,'FF33R0087760','VS2-CM10GE',1,2,'2019-10-10','2020-10-09'),
    (543433198,3,'FF33R0087760','VS2-ENG-BS-FD',1,1,'2019-10-10','2020-10-09'),
    (543433198,3,'FF33R0087760','VS2-SWITCH-FD',1,1,'2019-10-10','2020-10-09'),
    (543433198,3,'FF33R0087760','456-107-873',1,1,'2019-10-10','2020-10-09'),
    (543433198,3,'FF33R0087760','456-107-849',1,1,'2019-10-10','2020-10-09'),
    (543433198,3,'FF33R0087760','456-107-824',1,1,'2019-10-10','2020-10-09'),
    (543433198,3,'FF33R0087760','456-107-837',1,1,'2019-10-10','2020-10-09');
GO


-- ======================================= --
--             DATABASE CHECK              --
-- ======================================= --


SELECT * FROM Person.Person;
SELECT * FROM Sales.Salesman;
SELECT * FROM Sales.Country;
SELECT * FROM Sales.Client;
SELECT * FROM Maintenance.ProductFamily;
SELECT * FROM Maintenance.MaintenanceLevel;
SELECT * FROM Maintenance.Product;
SELECT * FROM Maintenance.Price;
SELECT * FROM Sales.Contract;
SELECT * FROM Maintenance.Serial;
SELECT * FROM Sales.ContractDetail;
GO


-- ======================================= --
--          RUNNING INTERROGATIONS         --
-- ======================================= --


/*
1. Creating a join to see the maintenance price per year for products from the 'Data Domain' family.
Tables used are Product, Price and ProductFamily from the Maintenance schema.
Columns to be displayed are FamilyName, ModelID, MaintenanceID and Price.
*/

SELECT 
    pf.FamilyName,
    p.ModelID,
    pr.MaintenanceID,
    pr.Price
FROM Maintenance.Product p 
JOIN Maintenance.Price pr 
    ON p.ModelID = pr.ModelID
JOIN Maintenance.ProductFamily pf
ON p.FamilyID = pf.FamilyID
WHERE pf.FamilyName LIKE 'Data Domain';
GO

/*
2. Creating a query to find the average price per product family for all maintenance levels.
Tables used are Product, Price, ProductFamily and MaintenanceLevel from the Maintenance schema.
Columns to be displayed are FamilyName, MaintenanceDescription and AveragePrice.
*/

SELECT 
    pf.FamilyName,
    ml.MaintenanceDescription,
    AVG(pr.Price) AS AveragePrice
FROM Maintenance.Product p 
JOIN Maintenance.Price pr 
    ON p.ModelID = pr.ModelID
JOIN Maintenance.ProductFamily pf
    ON p.FamilyID = pf.FamilyID
JOIN Maintenance.MaintenanceLevel ml
    ON pr.MaintenanceID = ml.MaintenanceID
GROUP BY pf.FamilyName, ml.MaintenanceDescription;
GO

/*
3. Creating a query to find the total maintenance amount per contract only for contracts which 
are currently valid. Tables to be used are Maintenance.Price, Maintenance.MaintenanceLevel 
and Sales.ContractDetail. Columns to be displayed are ContractNumber, MaintenanceDescription 
and ContractAmount and ordered by ContractAmount descending.
*/

SELECT
    cl.ClientName,
    ml.MaintenanceDescription,
    SUM(p.Price * cd.Quantity) AS ContractAmount
FROM Sales.ContractDetail cd
JOIN Maintenance.Price p
    ON p.ModelID = cd.ModelID
    AND p.MaintenanceID = cd.MaintenanceID
JOIN Maintenance.MaintenanceLevel ml 
    ON cd.MaintenanceID = ml.MaintenanceID
JOIN Sales.Client cl
    ON cl.ClientID = cd.ClientID
WHERE cd.StartDate <= GETDATE()
AND GETDATE() <= cd.EndDate
GROUP BY cl.ClientName, ml.MaintenanceDescription
ORDER BY ContractAmount DESC;
GO

/*
4. Creating a query to find the top 3 clients ordered by ContractAmount for which EndDate indicates 
that contract has expired.
Tables to be used are Price, MaintenanceLevel from the Maintenance schema and Sales.ContractDetail
*/

SELECT
    TOP 3 c.ClientName,
    ml.MaintenanceDescription,
    cd.EndDate,
    SUM(p.Price * cd.Quantity) AS ContractAmount
FROM Sales.Client c 
JOIN Sales.ContractDetail cd 
    ON c.clientID = cd.ClientID
JOIN Maintenance.Price p 
    ON p.ModelID = cd.ModelID
JOIN Maintenance.MaintenanceLevel ml 
    ON cd.MaintenanceID = ml.MaintenanceID
WHERE cd.EndDate < GETDATE() -- defines what an expired contract is
GROUP BY c.ClientName, cd.EndDate, ml.MaintenanceDescription
ORDER BY ContractAmount DESC;
GO



/*
5. Creating a query to find the top salesman who sold the most in 2020 in descending order.
Tables to be used are Price from Maintenance schema, Contract, ContractDetail, Salesman from 
Sales schema and Person from Person schema.
For the ease of controlling the flow of data 
*/

SELECT  
    DISTINCT t.SalesmanID,p.FirstName, p.LastName,
    SUM(t.TotalPrice) OVER (PARTITION BY t.SalesmanID) AS TotalSold
FROM
    (
    SELECT 
    cd.ContractNumber, cd.ClientID, c.SalesmanID, cd.MaintenanceID, 
    cd.Quantity, cd.StartDate, cd.EndDate, (p.Price * cd.Quantity) as TotalPrice
    FROM Sales.ContractDetail cd
    JOIN Maintenance.Price p
        ON p.MaintenanceID = cd.MaintenanceID
        AND p.ModelID = cd.ModelID
    JOIN Sales.Contract c
        ON c.ContractNumber = cd.ContractNumber
    WHERE cd.StartDate >= '2020-01-01'
    AND cd.EndDate < '2021-01-01'
    ) AS t
JOIN Sales.Salesman s
    ON t.SalesmanID = s.SalesmanID
JOIN Person.Person p
    ON p.PersonID = s.PersonID
ORDER BY TotalSold DESC;
GO

/*
6. Creating a query that returns the most popular service levels and showing a count for how many times
that respective service level has been contracted. The result will be showed in descending order.
*/

SELECT
    DISTINCT cd.MaintenanceID,
    ml.MaintenanceDescription,
    COUNT(cd.MaintenanceID) OVER (PARTITION BY cd.MaintenanceID) AS TimesContracted
FROM Sales.ContractDetail cd
JOIN Maintenance.MaintenanceLevel ml
    ON cd.MaintenanceID = ml.MaintenanceID
ORDER BY TimesContracted DESC;
GO

/*
7. Creating a query that returns the models for which price is under the 5000$ threshold on Enhanced 
maintenance level (MaintenanceID 2).
*/

SELECT 
prod.ModelID, prod.ModelDescription, pr.MaintenanceID, pr.Price
FROM Maintenance.Product prod
JOIN Maintenance.Price pr
    ON prod.ModelID = pr.ModelID
WHERE pr.MaintenanceID = '2'
AND price <= 5000;
GO

/*
8. Creating two queries that return the minimum and maximum maintnance price that's different from 0.
*/

SELECT MIN(NULLIF(Price,0)) as MinPrice
FROM Maintenance.Price;
GO

SELECT MAX(Price) as MaxPrice
FROM Maintenance.Price;
GO

/*
9. Procedure that produces a maintenance quote for sales team for one year for all equipment of a client
*/

CREATE OR ALTER PROCEDURE Sales.uspQuoteAllClientAssets
@ClientID INT
AS 
BEGIN
    SELECT
        c.ClientName AS BillToCustomer,
        cd.ContractNumber,
        ml.MaintenanceDescription,
        s.SerialNumber,
        cd.ModelID,
        prod.ModelDescription,
        cd.Quantity,
        (p.Price * cd.Quantity) AS QuotedMtncAmount,
        DATEADD(yy,1,cd.StartDate) AS NewStartDate,
        DATEADD(yy,1,cd.EndDate) AS NewEndDate
    FROM Sales.ContractDetail cd 
        JOIN Sales.Client c
            ON c.ClientID = cd.ClientID
        JOIN Maintenance.MaintenanceLevel ml
            ON ml.MaintenanceID = cd.MaintenanceID
        LEFT JOIN Maintenance.Serial s 
            ON cd.SerialNumber = s.SerialNumber
            AND cd.ModelID = s.ModelID
            AND cd.ClientID = s.ClientID
        JOIN Maintenance.Price p
            ON  p.ModelID = cd.ModelID
            AND p.MaintenanceID = cd.MaintenanceID
        JOIN Maintenance.Product prod
            ON prod.ModelID = cd.ModelID
    WHERE cd.ClientID = @ClientID;
END;
GO

EXEC Sales.uspQuoteAllClientAssets 4;
GO

/*
10. Procedure that produces a maintenance quote for sales team for one year for a certain contract number only
*/

CREATE OR ALTER PROCEDURE Sales.uspQuoteContract
@ContractNumber INT
AS 
BEGIN
    SELECT
        c.ClientName AS BillToCustomer,
        cd.ContractNumber,
        ml.MaintenanceDescription,
        s.SerialNumber,
        cd.ModelID,
        prod.ModelDescription,
        cd.Quantity,
        (p.Price * cd.Quantity) AS QuotedMtncAmount,
        DATEADD(yy,1,cd.StartDate) AS NewStartDate,
        DATEADD(yy,1,cd.EndDate) AS NewEndDate
    FROM Sales.ContractDetail cd 
        JOIN Sales.Client c
            ON c.ClientID = cd.ClientID
        JOIN Maintenance.MaintenanceLevel ml
            ON ml.MaintenanceID = cd.MaintenanceID
        LEFT JOIN Maintenance.Serial s 
            ON cd.SerialNumber = s.SerialNumber
            AND cd.ModelID = s.ModelID
            AND cd.ClientID = s.ClientID
        JOIN Maintenance.Price p
            ON  p.ModelID = cd.ModelID
            AND p.MaintenanceID = cd.MaintenanceID
        JOIN Maintenance.Product prod
            ON prod.ModelID = cd.ModelID
    WHERE cd.ContractNumber = @ContractNumber;
END;
GO

EXEC Sales.uspQuoteContract 568987444;
GO

/*
11. A view that shows the connections between clients and salesman
*/

CREATE OR ALTER VIEW ClientConnections AS
    SELECT
    t.ClientID, t.ClientName, t.ClientLocation, t.ClientContact, t.ClientEmail, t.ClientPhone,
    CONCAT (p.FirstName, ' ',p.LastName) AS 'SalesmanName', p.Email AS 'SalesmanEmail', p.Phone as 'SalesmanPhone'
    FROM 
        (
        SELECT 
        c.ClientID, c.ClientName,
        CONCAT(c.Address, ', ', c.City, ', ', co.CountryName) AS 'ClientLocation',
        CONCAT(p.FirstName, ' ',p.LastName) AS 'ClientContact',
        p.Email AS 'ClientEmail', p.Phone AS 'ClientPhone', c.SalesmanID
        FROM Sales.Client c
        JOIN Sales.Country co
            ON c.CountryID = co.CountryID
        JOIN Person.Person p
            ON c.PersonID = p.PersonID
        ) AS t
    JOIN Sales.Salesman s
        ON s.SalesmanID = t.SalesmanID
    JOIN Person.Person p
        ON p.PersonID = s.PersonID;
GO

SELECT * FROM ClientConnections;
GO

-- ======================================= --
--               END OF CODE               --
-- ======================================= --