Wednesday, December 7, 2016

TEst post

---------------Creating tables for Database--------------

create table Supplier (
SupplierID           INT    IDENTITY(200,1) Primary Key,
SName                VARCHAR(35),
SEmail               VARCHAR(50),
SContactFName VARCHAR(20),
SContactLName VARCHAR(20),
SContactPhone VARCHAR(20),
SNotes               VARCHAR(MAX),
SAddress             VARCHAR(35),
SWebsite             VARCHAR(60));

create table Item (
ItemSN               VARCHAR(25) Primary Key,
ItemName             VARCHAR(35),
ItemDesc             VARCHAR(400),
ItemPrice            Decimal(8,2),
ItemWeight           Decimal(8,2));

create table SupplierItem (
SupplierID INT references Supplier(SupplierID),
ItemSN    VARCHAR(25) references Item(ItemSN));

create table InvLocation (
LocID  INT Primary Key,
LAisle VARCHAR(10),
LColumn       VARCHAR(15),
LHeight       VARCHAR(10));

create table Inventory (
InvtID        INT IDENTITY(1000,1) Primary Key,
Quantity      INT,
DateIN        DATETIME,
DateOUT              DATETIME,
ItemSN        VARCHAR(25) references Item(ItemSN),
LocID         INT    references InvLocation(LocID));

---------------Inserting DATA into TABLES-------------------



------------------CREATE TABLEs for DataWarehouse-------------------



CREATE Table dwItems(
       ItemSN        VARCHAR(25) Primary key,
       IName         VARCHAR(35),
       IPrice        DECIMAL(8,2),
       IWeight              DECIMAL(8,2),
       QUANTITY      INT,
       DateID        INT,
       SupID         INT,
       InvtID        INT
);


create table dwSupplier (
       dwSup         INT IDENTITY (1,1) Primary Key,
       SupID         INT,
       SName         VARCHAR(35),
       SContact      VARCHAR(45),
       SPhone        VARCHAR(20),
       IName         VARCHAR(35)
);

Create Table dwInvOUT (
       InvtID INT Primary Key,
       IName  VARCHAR(35),
       Quantity      INT,
       DateOUT       DAtETIME
);

CREATE TABLE dwInventoryDates (
       DateID               INT           IDENTITY(1,1) PRIMARY KEY,
       RawDate                    DATE,
       Day_of_Week          VARCHAR(9),
       Month_of_Year VARCHAR(9),
       Quarter_of_Year      VARCHAR(2),
       Inv_Year             INT
);


------------------MAKE DATES for date table-------------------------


create proc dwMakeDates
       @vStartDate DATE
as
declare @vCounter INT = 1;
SET NOCOUNT ON;
       While @vCounter <= 1000
BEGIN
       INSERT into dwInventoryDates
       values (@vStartDate, DATENAME(weekday,@vStartDate), DATENAME(MONTH, @vStartDate), CONCAT(DATEPART(QUARTER, @vStartDate), 'Q'), DATEPART(Year,@vStartDate));
       Set @vCounter = @vCounter+1;
       Set @vStartDate = DATEADD(day,1,@vStartDate);
END;

Exec dwMakeDates @vStartDate = '11/2/2015';

select *
from dwInventoryDates

------------------Get DWSupplier FACTS------------------------------


create proc dwGetSupFacts
as
begin
       insert into dwSupplier
              select s.SupplierID, s.SName, CONCAT(s.SContactFName, ' ', s.SContactLName), s.SContactPhone, i.ItemName
              from Supplier s join SupplierItem si on s.SupplierID = si.SupplierID
              join item i on si.ItemSN = i.ItemSN
end


------------------Get DWITem FACTS----------------------------------


create proc dwGetItemFacts
as
begin
       insert into dwItems
              select i.ItemSN, i.ItemName, i.ItemPrice, i.ItemWeight, inv.Quantity, DATEDIFF(dd,'11/1/2015',inv.DateIN), s.SupplierID, inv.InvtID
              from item I join Inventory inv on I.ItemSN = inv.ItemSN
              join SupplierItem si on si.ItemSN = i.ItemSN
              join Supplier s on si.SupplierID = s.SupplierID
end

exec dwGetItemFacts;

--****************Total Quantity in Inventory per QUARTER***********

select dwd.Quarter_of_Year, SUM(dwi.QUANTITY) as 'Total Quantity in Inventory'
from dwItems dwi join dwInventoryDates dwd on dwi.DateID = dwd.DateID
group by dwd.Quarter_of_Year
order by 2 desc;



------------------Get ITEM name from SN-----------------------------


create function GetItemName
       (@vItemSN VARCHAR(25))
       RETURNS VARCHAR(35)
as
begin
declare
       @vItemName VARCHAR(35);
       select @vItemName = ItemName
       from Item
       where ItemSN = @vItemSN;
       return @vItemName;
end

------------------Get Inventory going out on Given month/year-------


create proc InvOUTupdate
       (@vMonthNum INT,
        @vYear              INT)
      
as
declare
       @vInvtID      INT,
       @vIName              VARCHAR(35),
       @vQuantity    INT,
       @vDateOUT     DATETIME;

       Delete from dwInvOUT
       where datepart(Year,DateOUT) = @vYear and datepart(Month,DateOUT) = @vMonthNum;

declare MonthsInventory CURSOR for
       select Distinct InvtID
       from Inventory
       where DATEPART(Month,DateOUT) = @vMonthNum and datepart(Year,DateOUT) = @vYear;
OPEN MonthsInventory
FETCH NEXT FROM MonthsInventory into @vInvtID
While @@FETCH_STATUS = 0
begin
       select @vIName = dbo.GetItemName(ItemSN), @vQuantity = Quantity, @vDateOUT = DateOUT
       from Inventory
       where InvtID = @vInvtID;

       Insert into dwInvOUT
              values
                     (@vInvtID, @vIName, @vQuantity, @vDateOUT);
FETCH NEXT FROM MonthsInventory INTO @vInvtID
end
SET NOCOUNT OFF;
CLOSE MonthsInventory;
DEALLOCATE MonthsInventory;



Exec InvOUTupdate @vMonthNum = 2, @vYear = 2017;

select *
from dwInvOUT
order by 4;


------------------Trigger to prevent Inventory delete and update to Item table to re-order Item


create trigger Qupdate on Inventory
instead of delete
as
begin
       declare @vIntID int;
       select @vIntID = deleted.InvtID
       from deleted;
       update Inventory
       set Quantity = 0
       where InvtID = @vIntID;
       update Item
       set ItemDesc = 'Need to RE-Order'
       where ItemSN like
              (select ItemSN from Inventory where InvtID = @vIntID)
end;

delete from Inventory
where InvtID = 1049;

select InvtID, Quantity, it.ItemName, it.ItemDesc
from Inventory i join item it on i.ItemSN = it.ItemSN
where i.InvtID = 1049;

select *
from Item


-----------------------------------------------





select *
from Airports

drop table LongHauls

create table LongHauls (
       Flight_ID                  INT Primary key,
       City_Origin                VARCHAR(25),
       City_Destination     VARCHAR(25),
       Flight_Time_Minutes  INT

);

---------Get airport Origin Name from Airport Code

create function GetOrigin
       (@vCOrigin VARCHAR(3))
       RETURNS VARCHAR(25)
as
begin
Declare
       @vAirLoca VARCHAR(25);
       select @vAirLoca = Airport_Location
       from Airports
       where Airport_Code = @vCOrigin;
       Return @vAirLoca;
End

---------Get airport Destination Name from Airport Code

create function GetDest
       (@vCDest VARCHAR(3))
       RETURNS VARCHAR(25)
as
begin
Declare
       @vAirLoca VARCHAR(25);
       select @vAirLoca = Airport_Location
       from Airports
       where Airport_Code = @vCDest;
       Return @vAirLoca;
End

----------------CURSOR-----
select DATEDIFF(MINUTE, Departure_DateTime, Arrival_DateTime) as TotalMin
from Flights
order by 1 desc

drop proc LongFlights

create proc LongFlights
as
declare
       @vFlightID           INT,
       @vCityOri            VARCHAR(25),
       @vCityDes            VARCHAR(25),
       @vTotalMin           INT;
declare LongHualFlights CURSOR for
       Select Flight_ID
       from Flights
       where DATEDIFF(MINUTE, Departure_DateTime, Arrival_DateTime) >= 200;
OPEN LongHualFlights
FETCH NEXT FROM LongHualFlights INTO @vFlightID
WHILE @@FETCH_STATUS = 0
begin
       select @vCityOri = dbo.GetOrigin(Airport_Code_Origin), @vCityDes = dbo.GetDest(Airport_Code_Destination), @vTotalMin = DATEDIFF(MINUTE, Departure_DateTime, Arrival_DateTime)
       from Flights
       where Flight_ID = @vFlightID;

       Insert into LongHauls
              values
                     (@vFlightID, @vCityOri, @vCityDes, @vTotalMin);
FETCH NEXT FROM LongHualFlights INTO @vFLightID
END
SET NOCOUNT OFF;
CLOSE LongHualFlights;
DEAllOCATE LongHualFlights;

exec LongFlights;

------------------------------------------

select *
from LongHauls







create table dwBookFacts (
       ISBN                       VARCHAR(14),
       SellerID                   INT,
       DateID                     INT,
       TotalSales                 Decimal(8,2),
       TotalVolumessold     INT
);


select *
from BookSales

CREATE PROC dwGetBookSales
       @vSaleDate DATE
AS
      
BEGIN
       insert into dwBookFacts
              select ISBN, SellerID, DATEDIFF(dd,'9/30/2016',SaleDate), SUM   (SalePrice),                           COUNT(ISBN)
              from BookSales
              where SaleDate = @vSaleDate
              group by ISBN, SellerID, DATEDIFF(dd,'9/30/2016',SaleDate);

END

Exec dwGetBookSales @vSaleDate = '11/1/2016';

select *
from dwBookFacts;

insert into BookSales
values
('978-0439064873', 200, '11/1/2016', 9.27),
('978-0439064873', 200, '11/1/2016', 9.27),
('978-0439064873', 100, '11/1/2016', 9.27);

drop table dwSalesDates

CREATE TABLE dwSalesDates (
       DateID               INT           IDENTITY(1,1) PRIMARY KEY,
       RawDate                    DATE,
       Day_of_Week          VARCHAR(9),
       Month_of_Year VARCHAR(9),
       Quarter_of_Year      VARCHAR(2),
       Sales_Year           INT
);

Drop proc dwMakeDates

create proc dwMakeDates
       @vStartDate DATE
as
declare @vCounter INT = 1;
SET NOCOUNT ON;
       While @vCounter <= 1000
BEGIN
       INSERT into dwSalesDates
       values (@vStartDate, DATENAME(weekday,@vStartDate), DATENAME(MONTH, @vStartDate), CONCAT(DATEPART(QUARTER, @vStartDate), 'Q'), DATEPART(Year,@vStartDate));
       Set @vCounter = @vCounter+1;
       Set @vStartDate = DATEADD(day,1,@vStartDate);
END;

Exec dwMakeDates @vStartDate = '10/1/2016';

select *
from dwSalesDates;

select d.Quarter_of_Year, Sum(f.TotalSales) as 'Total Sales'
from dwBookFacts f join dwSalesDates d on f.DateID = d.DateID
group by d.Quarter_of_Year
order by 2 desc;



create function getAuthors
       (@vISBN VARCHAR(14))
       RETURNS VARCHAR(420)
AS
BEGIN
Declare
       @vCurAuthor VARCHAR(41) = '',
       @vAuthorList VARCHAR(420) = '',
       @vAuthorCount INT,
       @vCounter INT = 1;
       Select @vAuthorCount = COUNT(AID)
       from BookAuthors
       where ISBN = @vISBN;
       if @vAuthorCount = 0
              set @vAuthorList = 'No Author found for this title'
       else if @vAuthorCount = 1
              Select @vAuthorList = CONCAT(a.AFName, ' ', a.LFName)
              from Author a join BookAuthors ba on a.AID = ba.AID
              where ba.ISBN = @vISBN;
       else
              While @vCounter <= @vAuthorCount
              begin
                     Select @vCurAuthor = MIN(CONCAT(a.AFName, ' ', a.LFName))
                     from Author a join BookAuthors ba on a.AID = ba.AID
                     where ba.ISBN = @vISBN and CONCAT(a.AFName, ' ', a.LFName) > @vCurAuthor;
                     IF @vCounter = 1
                           Set @vAuthorList = @vCurAuthor;
                     Else
                           Set @vAuthorList = @vAuthorList+ ', ' + @vCurAuthor;
                     Set @vCounter = @vCounter+1;
              END
       RETURN @vAuthorList;
END;

Select *
from Books



select BookTitle, dbo.getAuthors(ISBN) from Books
where ISBN = '978-0470135921';

select *, dbo.getAuthors(ISBN) from dwBookFacts;


create table dwAuthorList (
       ISBN VARCHAR(14) Primary key,
       AuthorList VARCHAR(420),
       BookTitle  VARCHAR(60)
);

drop proc popAuthors


create proc popAuthors
as
Begin
       delete from dwAuthorList;
       insert into dwAuthorList
       select ISBN, dbo.getAuthors(ISBN), BookTitle
       from Books;
End

exec popAuthors;

select *
from dwAuthorList;

--------Learning how to make Cursors *#@$% ----------------

create table dwSalesVolumes (
       ISBN                 VARCHAR(14) primary key,
       BookTitle            VARCHAR(60),
       SalesTot             DECIMAL(12,2),
       VolumeTot            INT
);



create function GetTitle
       (@vISBN VARCHAR(14))
       RETURNS VARCHAR(60)
As
Begin
Declare
       @vTitle VARCHAR(60);
       select @vTitle = BookTitle
       from Books
       where ISBN = @vISBN;
       Return @vTitle;
END



create proc UpdateSalesData
       (@vSalesDate  DATE)
as
--SET NOCOUNT ON;
Declare
       @vISBN               VARCHAR(14),
       @vBookTitle          VARCHAR(60),
       @vSalesTot           DECIMAL(12,2),
       @vVolumeTot          INT;
Declare curSalesVol CURSOR FOR
       Select Distinct ISBN
       from BookSales
       where SaleDate = @vSalesDate;
OPEN curSalesVol
FETCH NEXT FROM curSalesVol INTO @vISBN
WHILE @@FETCH_STATUS = 0
BEGIN
       Select @vBookTitle = dbo.GetTitle(ISBN), @vSalesTot = SUM(SalePrice), @vVolumeTot = Count(SaleID)
       from BookSales
       where ISBN = @vISBN
       group by dbo.GetTitle(ISBN);

       Delete from dwSalesVolumes where ISBN = @vISBN;
       INSERT INTO dwSalesVolumes
       values (@vISBN, @vBookTitle, @vSalesTot, @vVolumeTot);
FETCH NEXT FROM curSalesVol INTO @vISBN
END
SET NOCOUNT OFF;
CLOSE curSalesVol;
DEALLOCATE curSalesVol;

exec UpdateSalesData @vSalesDate = '10/12/2016';

select * from dwSalesVolumes;








Posted by Kevin at 9:36 AM 0 comments
Subscribe to: Posts (Atom)