USE DataMiningDW
GO

SELECT *
FROM [dbo].[Shoe]
WHERE MilesWeek IS NOT NULL;
GO

ALTER TABLE dbo.Shoe
ADD MilesWeekIsMissing bit;
GO


UPDATE dbo.Shoe
SET MilesWeekIsMissing = 1-MissingMilesWeek
FROM dbo.Shoe AS S JOIN dbo.ShoeMVP as MVP
ON s.id=mvp.ID;
GO

EXEC sp_addlinkedserver 
@server=DMS, -- Nazwa tworzonego serwera powiązanego
@srvproduct='', 
@provider='MSOLAP',
@datasrc='.', -- Nazwa docelowego serwera Analysis Services
@catalog='DataMiningSolutions' -- Nazwa bazy analitycznej w której znajdują się odczytywane modele ekslporacji danych
GO

;WITH CTE AS (
SELECT * FROM OPENQUERY
 (DMS, '
SELECT
  Predict([MilesWeekLR].[Miles Week] ) as [PredictedMilesWeek],
  t.[id]
FROM
  [MilesWeekLR]
PREDICTION JOIN
  OPENQUERY([Data Mining],
    ''SELECT
    [Gender], [City], [State], [Source], [Age], [YearsRunning],
      [MilesWeek], [RacesYear], [Triathlete], [Style], [ShoeCode], id
    FROM
      [dbo].[Shoe]
    '') AS t
ON
  [MilesWeekLR].[Gender] = t.[Gender] AND
  [MilesWeekLR].[City] = t.[City] AND
  [MilesWeekLR].[State] = t.[State] AND
  [MilesWeekLR].[Source] = t.[Source] AND
  [MilesWeekLR].[Age] = t.[Age] AND
  [MilesWeekLR].[Years Running] = t.[YearsRunning] AND
  [MilesWeekLR].[Miles Week] = t.[MilesWeek] AND
  [MilesWeekLR].[Races Year] = t.[RacesYear] AND
  [MilesWeekLR].[Triathlete] = t.[Triathlete] AND
  [MilesWeekLR].[Style] = t.[Style] AND
  [MilesWeekLR].[Shoe Code] = t.[ShoeCode]'))
 UPDATE dbo.Shoe
 SET MilesWeek = PredictedMilesWeek
 FROM dbo.Shoe JOIN CTE
 ON dbo.Shoe.id=cte.id
 WHERE MilesWeek IS NULL;
 GO

SELECT ID,COALESCE(StoreCd,'') + COALESCE(Style,'') + COALESCE(Triathlete,'') 
AS StoreStyleTriathlete
FROM dbo.Shoe;
GO

USE AdventureWorksDW
GO

SELECT DATEDIFF(YEAR,[BirthDate],GETDATE()) AS Age
FROM dbo.CustomersHistory;
GO

SELECT  DATEDIFF(MONTH,[DateFirstPurchase],GETDATE()) AS MonthsAsCustomer
FROM dbo.CustomersHistory
GO


ALTER VIEW [dbo].[CustomersHistory] AS
WITH Cust AS 
	(SELECT
	pc.[EnglishProductCategoryName] AS ProductCategoryName
	,c.[CustomerKey]
	,f.OrderQuantity AS Quantity
	,f.ExtendedAmount AS Amount
	FROM
	[dbo].[FactInternetSales] f
	INNER JOIN [dbo].[DimProduct] p
	ON f.[ProductKey] = p.[ProductKey]
	INNER JOIN [dbo].[DimProductSubcategory] psc
	ON p.[ProductSubcategoryKey] = psc.[ProductSubcategoryKey]
	INNER JOIN [dbo].[DimProductCategory] pc
	ON psc.[ProductCategoryKey] = pc.[ProductCategoryKey]
	INNER JOIN [dbo].[DimCustomer] c
	ON f.[CustomerKey] = c.[CustomerKey])
SELECT
ROW_NUMBER() OVER(ORDER BY c.[FirstName]) AS ID
,c.[FirstName],c.[LastName],c.Title,DATEDIFF(YEAR,[BirthDate],GETDATE()) AS Age,c.[MaritalStatus],c.[Gender]
,c.[TotalChildren],c.[NumberChildrenAtHome],c.[EnglishEducation] AS Education
,c.[EnglishOccupation] AS Occupation,c.[YearlyIncome],c.[HouseOwnerFlag]
,c.[NumberCarsOwned],c.[DateFirstPurchase],DATEDIFF(MONTH,[DateFirstPurchase],GETDATE()) AS MonthsAsCustomer,
c.EmailAddress,x.Amount AS TotalAmount
,x.Quantity AS TotalQuantity,x.BikesQuantity,x.BikesAmount
,x.ClothingQuantity,x.ClothingAmount,x.AccessoriesQuantity,x.AccessoriesAmount
FROM
[dbo].[DimCustomer] c INNER JOIN (
	SELECT
	[CustomerKey]
	,SUM(CASE ProductCategoryName
		WHEN 'Bikes' THEN  Quantity
		ELSE 0 
		END) AS [BikesQuantity]
	,SUM(CASE ProductCategoryName
		WHEN 'Bikes' THEN  Amount
		ELSE 0 
		END) AS [BikesAmount]
	,SUM(CASE ProductCategoryName
		WHEN 'Clothing' THEN  Quantity
		ELSE 0 
		END) AS [ClothingQuantity]
	,SUM(CASE ProductCategoryName
		WHEN 'Clothing' THEN  Amount
		ELSE 0 
		END) AS [ClothingAmount]
	,SUM(CASE ProductCategoryName
		WHEN 'Accessories' THEN  Quantity
		ELSE 0 
		END) AS [AccessoriesQuantity]
	,SUM(CASE ProductCategoryName
		WHEN 'Accessories' THEN  Amount
		ELSE 0 
		END) AS [AccessoriesAmount]
	,SUM(Quantity) AS Quantity,SUM(Amount) AS Amount
	FROM Cust 
	GROUP BY
	[CustomerKey]
	) AS [x]
ON c.[CustomerKey] = x.[CustomerKey];
GO






SELECT CustomerKey, AVG(SalesAmount) AS AvgSalesAmount, COUNT(SalesOrderNumber) AS OrdersCount, 
GROUPING (DATEPART(quarter ,OrderDate)) AS GroupedByQuarter,
DATEPART(quarter ,OrderDate) AS Quarter
FROM dbo.FactInternetSales
WHERE OrderDate BETWEEN '2004-01-01' AND '2004-12-31'
GROUP BY GROUPING SETS ((CustomerKey),(DATEPART(quarter ,OrderDate),CustomerKey));
GO

USE DataMiningDW
GO

SELECT Returned, COUNT(*)*1.00/(SELECT COUNT(*) FROM dbo.Shipments)*100 AS Pct
FROM dbo.Shipments
GROUP BY Returned;
GO

SELECT *
INTO dbo.ShipmentsSelected
FROM dbo.Shipments
WHERE Returned <>'NULL';
PRINT @@ROWCOUNT;
GO

INSERT INTO dbo.ShipmentsSelected (Name,StandardCost,ListPrice,Location,SendDate,ArrivalDate,ResourceHrs,Returned)
SELECT TOP (105) Name,StandardCost,ListPrice,Location,SendDate,ArrivalDate,ResourceHrs,Returned
FROM dbo.Shipments
WHERE Returned IS NOT NULL
ORDER BY NEWID();
GO

/*
UPDATE dbo.NewShipments
SET Days = Days/2;
*/

SELECT Prev.Id,Prev.Expression, After.Expression
FROM dbo.ShipmentsCategorized AS Prev 
JOIN dbo.ShipmentsCategorizedv2 AS After 
ON Prev.id = After.Id;
GO

USE AdventureWorksDW
GO

/*
SELECT *
INTO FactFinanceTrain
FROM dbo.FactFinance
TABLESAMPLE (60 PERCENT);
*/

SELECT TOP 60 PERCENT *
INTO dbo.CustomersHistoryTrain
FROM dbo.CustomersHistory
ORDER BY NEWID();
GO

SELECT TOP 65 PERCENT *
INTO dbo.CustomersHistoryTest
FROM dbo.CustomersHistory AS CH
WHERE CH.ID NOT IN (SELECT ID FROM dbo.CustomersHistoryTrain)
ORDER BY NEWID();
GO

SELECT *
INTO dbo.CustomersHistoryEvaluation
FROM dbo.CustomersHistory AS CH
WHERE CH.ID NOT IN (SELECT ID FROM dbo.CustomersHistoryTrain)
AND CH.ID NOT IN (SELECT ID FROM dbo.CustomersHistoryTest);
GO