WITH MathConst(PI, Avogadro)
AS
(SELECT 3.14159, 6.022e23) 
SELECT * FROM MathConst

select * from Testdata

SELECT * FROM
(SELECT 3.14159 AS PI, 6.022e23 AS Avogadro)
AS MathConst





WITH
TreData (number)
AS
(
SELECT data FROM TestData where data % 3 = 0
),
OddData
AS
(
SELECT id, data FROM TestData
WHERE data % 2 <> 0
)
SELECT id, data FROM OddData 
WHERE data in (select number from TreData)

INSERT INTO TestData VALUES(21)


SELECT id, data FROM
(SELECT id, data FROM TestData
WHERE data % 2 <> 0) as OddData
WHERE data IN
(SELECT data FROM TestData WHERE data % 3 = 0)



select count(*) from adventureworks.sales.salesperson




SELECT DISTINCT SH.SalesPersonId FROM 
Sales.SalesOrderHeader SH 
JOIN Sales.SalesOrderDetail SD
ON SH.SalesOrderId = SD.SalesOrderId
WHERE SD.ProductID = 866
AND SH.SalesPersonId IS NOT NULL

SELECT SalesPersonID, 
CASE 
WHEN SalesPersonID IN 
(
SELECT SH.SalesPersonId FROM 
Sales.SalesOrderHeader SH 
JOIN Sales.SalesOrderDetail SD
ON SH.SalesOrderId = SD.SalesOrderId
WHERE SD.ProductID = 866
AND SH.SalesPersonId IS NOT NULL
)
THEN 'Make Call'
ELSE 'Relax'
END
FROM Sales.SalesPerson;




WITH SalesPeopleWithOrder866
AS
(
SELECT SH.SalesPersonId FROM 
Sales.SalesOrderHeader SH 
JOIN Sales.SalesOrderDetail SD
ON SH.SalesOrderId = SD.SalesOrderId
WHERE SD.ProductID = 866
AND SH.SalesPersonId IS NOT NULL
)
SELECT SalesPersonID, 
CASE 
WHEN SalesPersonID IN 
(SELECT * FROM SalesPeopleWithOrder866)
THEN 'Make Call'
ELSE 'Relax'
END
FROM Sales.SalesPerson



select * from sales.salesorderdetail



SELECT DISTINCT SH.SalesPersonId FROM 
Sales.SalesOrderHeader SH 
JOIN Sales.SalesOrderDetail SD
ON SH.SalesOrderId = SD.SalesOrderId
WHERE SD.ProductID = 866
AND SH.SalesPersonId IS NOT NULL
AND (SELECT SUM(OrderQty * UnitPrice) FROM Sales.SalesOrderDetail WHERE SalesOrderID = SH.SalesOrderID)
> (SELECT AVG(V) FROM
(SELECT SUM(OrderQty * UnitPrice) AS V FROM Sales.SalesOrderDetail
WHERE Sales.SalesOrderDetail WHERE SalesOrderID = SH.SalesOrderID) d)




WITH OrderValue
AS
( Select SalesOrderID, SUM(UnitPrice * OrderQty) AS Total 
FROM Sales.SalesOrderDetail 
GROUP BY SalesOrderID),
AvgValue
AS
(
SELECT AVG(Total) AvgTotal FROM OrderValue
),
SalesPeopleWithOrder866
AS
(
SELECT SH.SalesPersonId FROM 
Sales.SalesOrderHeader SH 
JOIN Sales.SalesOrderDetail SD
ON SH.SalesOrderId = SD.SalesOrderId
WHERE SD.ProductID = 866
AND SH.SalesPersonId IS NOT NULL
)
SELECT SalesPersonID, 
CASE 
WHEN SalesPersonID IN 
(SELECT * FROM SalesPeopleWithOrder866)
THEN 'Make Call'
ELSE 'Relax'
END
FROM Sales.SalesPerson



SELECT DISTINCT SH.SalesPersonId FROM 
Sales.SalesOrderHeader SH 
JOIN Sales.SalesOrderDetail SD
ON SH.SalesOrderId = SD.SalesOrderId
AND SH.SalesPersonId IS NOT NULL
AND SD.ProductID = 866
JOIN OrderValue ON SH.SalesOrderID = OrderValue.SalesOrderId
JOIN AvgValue ON AvgValue.AvgTotal < OrderValue.Total

SELECT * FROM Ordervalue







SELECT SD.ProductID, SH.SalesOrderId, SH.SalesPersonId FROM 
Sales.SalesOrderHeader SH 
JOIN Sales.SalesOrderDetail SD
ON SH.SalesOrderId = SD.SalesOrderId

SELECT SD.ProductID, SH.SalesPersonID,
RANK() OVER ( ORDER BY SD.ProductID )
 FROM
Sales.SalesOrderHeader SH 
JOIN Sales.SalesOrderDetail SD
ON SH.SalesOrderId = SD.SalesOrderId
WHERE SH.SalesPersonID is not null






select * from sales.salesorderdetail
select * from sales.salesorderheader where salespersonid is null

DECLARE @results TABLE (pid int, c int)
DECLARE @prod INT
DECLARE prodCursor CURSOR
FOR
select ProductID from Production.Product
OPEN prodCursor
loop:
FETCH NEXT FROM prodCursor INTO @prod
IF @@FETCH_STATUS <> 0 GOTO done
DECLARE @c INT
SELECT DISTINCT @c = COUNT(*) from sales.salesorderheader AS H
JOIN sales.salesorderdetail AS D
ON H.SalesOrderId = D.SalesOrderId
WHERE D.ProductID = @prod
INSERT INTO @results VALUES(@prod, @c)
GOTO loop
done:
CLOSE prodCursor
DEALLOCATE prodCursor
SELECT * from @results


SELECT DISTINCT SH.SalesPersonId, SH.SalesOrderId, SD.ProductID FROM 
Sales.SalesOrderHeader SH 
JOIN Sales.SalesOrderDetail SD
ON SH.SalesOrderId = SD.SalesOrderId
WHERE SD.ProductID = 744
AND SH.SalesPersonId IS NOT NULL

SELECT DISTINCT SH.SalesPersonID FROM 
Sales.SalesOrderHeader SH 
JOIN Sales.SalesOrderDetail SD
ON SH.SalesOrderId = SD.SalesOrderId
WHERE SD.ProductID = 744
AND SH.SalesPersonId IS NOT NULL




SELECT Count(*) from Sales.SalesPerson



DECLARE @results TABLE (pid int, c int)
DECLARE @prod INT
DECLARE prodCursor CURSOR
FOR
select ProductID from Production.Product
OPEN prodCursor
loop:
FETCH NEXT FROM prodCursor INTO @prod
IF @@FETCH_STATUS <> 0 GOTO done
DECLARE @c INT
SELECT @c = COUNT(*) from (
SELECT Distinct SH.SalesPersonId FROM 
Sales.SalesOrderHeader SH 
JOIN Sales.SalesOrderDetail SD
ON SH.SalesOrderId = SD.SalesOrderId
WHERE SD.ProductID = @prod) s

INSERT INTO @results VALUES(@prod, @c)
GOTO loop
done:
CLOSE prodCursor
DEALLOCATE prodCursor
SELECT * from @results

select * FROM Sales.SalesOrderHeader


SELECT * from dbo.MissingProduct(866)

--DROP FUNCTION MissingProcuct

CREATE FUNCTION MissingProduct(@prod INT)
RETURNS Table
RETURN
WITH 
DetailsFor866
AS
(
SELECT SalesOrderID FROM Sales.SalesOrderDetail
WHERE ProductID=@prod
),
MakeCall
AS
(
SELECT SalesPersonID FROM Sales.SalesOrderHeader
WHERE SalesOrderID in (SELECT SalesOrderID FROM DetailsFor866)
)
SELECT SalesPersonID,
CASE
WHEN SalesPersonID in (SELECT SalesPersonID FROM MakeCall) 
THEN 'Make Call!'
ELSE 'Relax'
END AS Message
FROM Sales.SalesPerson;


SELECT DISTINCT SH.SalesPersonID FROM 
Sales.SalesOrderHeader SH 
JOIN Sales.SalesOrderDetail SD
ON SH.SalesOrderId = SD.SalesOrderId
WHERE SD.ProductID = 744
AND SH.SalesPersonId IS NOT NULL;


-- salepeople for product 744 (Subquery)
SELECT SalesPersonID ,
CASE 
WHEN SalesPersonID in
(
	SELECT SH.SalesPersonID FROM  Sales.SalesOrderHeader SH 
	WHERE SalesOrderID in
	(
		SELECT SalesOrderID FROM Sales.SalesOrderDetail
		WHERE ProductID = 744
)
)
THEN 'Make Call!'
ELSE 'Relax'
END AS Message
FROM Sales.SalesPerson;



-- salepeople for product 744 (CTE)
WITH 
DetailsFor744
AS
(
SELECT SalesOrderID FROM Sales.SalesOrderDetail
WHERE ProductID=744
),
MakeCall
AS
(
SELECT SalesPersonID FROM Sales.SalesOrderHeader
WHERE SalesOrderID in (SELECT SalesOrderID FROM DetailsFor744)
)
SELECT SalesPersonID,
CASE
WHEN SalesPersonID in (SELECT SalesPersonID FROM MakeCall) 
THEN 'Make Call!'
ELSE 'Relax'
END AS Message
FROM Sales.SalesPerson;


-- salepeople for product 744 (sub query)
SELECT SalesPersonID,
CASE
WHEN SalesPersonID in
(
SELECT SalesPersonID FROM Sales.SalesOrderHeader
WHERE SalesOrderID in
(SELECT SalesOrderID FROM Sales.SalesOrderDetail
WHERE ProductID = 744)
) 
THEN 'Make Call!'
ELSE 'Relax'
END
FROM Sales.SalesPerson





CREATE FUNCTION MissingProdExp(@prod INT)
RETURNS TABLE
RETURN
WITH 
OrdersFor866
AS
(
SELECT SalesOrderID FROM Sales.SalesOrderHeader
WHERE SalesOrderID in (SELECT SalesOrderID FROM Sales.SalesOrderDetail
WHERE ProductID=866)
),
OrderTotalsFor866
AS
(
SELECT SalesOrderID, SUM(UnitPrice * OrderQty) AS Total From Sales.SalesOrderDetail
WHERE SalesOrderID in (SELECT SalesOrderID FROM OrdersFor866)
GROUP BY SalesOrderID
),
AvgOrdersFor866
AS
(
SELECT Avg(Total) AS Average FROM OrderTotalsFor866
),

AboveAvgOrdersFor866
AS
(
SELECT OT.SalesOrderID FROM OrderTotalsFor866 OT
JOIN AvgOrdersFor866 AS A ON 1 = 1
WHERE A.Average < OT.Total 
)
,
MakeCall
AS
(
SELECT SalesPersonID FROM Sales.SalesOrderHeader
WHERE SalesOrderID in (SELECT SalesOrderID FROM AboveAvgOrdersFor866)
)
--SELECT SalesPersonID FROM MakeCall
SELECT SalesPersonID,
CASE
WHEN SalesPersonID in (SELECT SalesPersonID FROM MakeCall) 
THEN 'Make Call!'
ELSE 'Relax'
END AS Message
FROM Sales.SalesPerson;


SELECT Count(*) from dbo.MissingProdExp(744)
WHERE Message = 'Make Call!'
UNION
SELECT Count(*) from dbo.MissingProduct(744)
WHERE Message = 'Make Call!'



DECLARE @results TABLE (prod int, reg int, expensive int)
DECLARE @prod INT
DECLARE prodCursor CURSOR
FOR
select ProductID from Production.Product
OPEN prodCursor
loop:
FETCH NEXT FROM prodCursor INTO @prod
IF @@FETCH_STATUS <> 0 GOTO done
DECLARE @reg INT
DECLARE @expensive INT
SELECT @reg = Count(*) from dbo.MissingProduct(@prod) WHERE Message = 'Make Call!'
SELECT @expensive = Count(*) from dbo.MissingProdExp(@prod)  WHERE Message = 'Make Call!'
INSERT INTO @results VALUES(@prod, @reg, @expensive)
GOTO loop
done:
CLOSE prodCursor
DEALLOCATE prodCursor
SELECT * from @results


--DROP INDEX [_dta_index_SalesOrderDetail_10_610101214__K1_4_7] ON [Sales].[SalesOrderDetail] 

CREATE NONCLUSTERED INDEX [_dta_index_SalesOrderDetail_10_610101214__K1_4_7] ON [Sales].[SalesOrderDetail] 
(
	[SalesOrderID] ASC
)
INCLUDE ( [OrderQty],
[UnitPrice]) WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]

--DROP INDEX  [_dta_index_SalesOrderHeader_10_722101613__K1] ON [Sales].[SalesOrderHeader] 

CREATE NONCLUSTERED INDEX [_dta_index_SalesOrderHeader_10_722101613__K1] ON [Sales].[SalesOrderHeader] 
(
	[SalesOrderID] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]


-- expensive salespeople for product 744 (CTE)
WITH 
OrdersFor744
AS
(
SELECT SalesOrderID FROM Sales.SalesOrderHeader
WHERE SalesOrderID in (SELECT SalesOrderID FROM
Sales.SalesOrderDetail
WHERE ProductID=744)
),
OrderTotalsFor744
AS
(
SELECT SalesOrderID, 
SUM(UnitPrice * OrderQty) AS Total 
From Sales.SalesOrderDetail
WHERE SalesOrderID in (SELECT SalesOrderID FROM OrdersFor744)
GROUP BY SalesOrderID
),
AvgOrdersFor744
AS
(
SELECT Avg(Total) AS Average FROM OrderTotalsFor744
),
AboveAvgOrdersFor744
AS
(
SELECT OT.SalesOrderID FROM OrderTotalsFor744 OT
CROSS APPLY AvgOrdersFor744 AS A
WHERE A.Average < OT.Total 
)
,
MakeCall
AS
(
SELECT SalesPersonID FROM Sales.SalesOrderHeader
WHERE SalesOrderID in (SELECT SalesOrderID FROM AboveAvgOrdersFor744)
)
--SELECT SalesPersonID FROM MakeCall
SELECT SalesPersonID,
CASE
WHEN SalesPersonID in (SELECT SalesPersonID FROM MakeCall) 
THEN 'Make Call!'
ELSE 'Relax'
END
FROM Sales.SalesPerson;




-- expensive salespeople for product 744 (sub-query)
SELECT SalesPersonID,
CASE
WHEN SalesPersonID in
(
SELECT OH.SalesPersonID FROM Sales.SalesOrderHeader OH
WHERE OH.SalesOrderID in
	(
	SELECT SalesOrderID FROM Sales.SalesOrderDetail OD
	WHERE ProductID = 744
	AND 
		(
		SELECT SUM(UnitPrice * OrderQty) FROM Sales.SalesOrderDetail
		WHERE OH.SalesOrderID = SalesOrderID
		)
	>
		(
		SELECT AVG(Total) FROM  
			(
			SELECT SUM(UnitPrice * OrderQty) AS Total 
				FROM Sales.SalesOrderDetail 
				WHERE SalesOrderID in 
					(SELECT SalesOrderID FROM Sales.SalesOrderDetail
						WHERE ProductID = OD.ProductID)
				GROUP BY SalesOrderID
			) d
		)
	)
)
THEN 'Make Call!'
ELSE 'Relax'
END AS Message
FROM Sales.SalesPerson





DECLARE @a float
SELECT @a = AVG(Total) FROM  
	(
	SELECT SUM(UnitPrice * OrderQty) AS Total 
		FROM Sales.SalesOrderDetail 
		WHERE SalesOrderID in 
			(SELECT SalesOrderID FROM Sales.SalesOrderDetail
				WHERE ProductID = 866)
		GROUP BY SalesOrderID
	) d
SELECT @a

	SELECT SUM(UnitPrice * OrderQty) AS Total 
		FROM Sales.SalesOrderDetail 
		WHERE SalesOrderID in 
			(SELECT SalesOrderID FROM Sales.SalesOrderDetail
				WHERE ProductID = 866)
		GROUP BY SalesOrderID




SELECT SalesPersonID,
CASE
WHEN SalesPersonID in
(
SELECT OH.SalesPersonID FROM Sales.SalesOrderHeader OH
WHERE OH.SalesOrderID in
	(
	SELECT SalesOrderID FROM Sales.SalesOrderDetail OD
	WHERE ProductID = 866
	AND 
		(
		SELECT SUM(UnitPrice * OrderQty) FROM Sales.SalesOrderDetail
		WHERE OH.SalesOrderID = SalesOrderID
		)
	> @a
	)
)
THEN 'Make Call!'
ELSE 'Relax'
END
FROM Sales.SalesPerson





SELECT SalesPersonID,
CASE
WHEN SalesPersonID in
(
SELECT OH.SalesPersonID FROM Sales.SalesOrderHeader OH
WHERE OH.SalesOrderID in
	(
	SELECT SalesOrderID FROM Sales.SalesOrderDetail OD
	WHERE ProductID = 744
	AND 
		(
		SELECT SUM(UnitPrice * OrderQty) FROM Sales.SalesOrderDetail
		WHERE OH.SalesOrderID = SalesOrderID
		)
	>
		(
		SELECT AVG(Total) FROM  
			(
			SELECT SUM(UnitPrice * OrderQty) AS Total 
				FROM Sales.SalesOrderDetail 
				WHERE SalesOrderID in 
					(SELECT SalesOrderID FROM Sales.SalesOrderDetail
						WHERE ProductID = OD.ProductID)
				GROUP BY SalesOrderID
			) d
		)
	)
)
THEN 'Make Call!'
ELSE 'Relax'
END
FROM Sales.SalesPerson




SELECT ProductID FROM Sales.SalesOrderDetail
WHERE UnitPrice 
> (SELECT Avg(UnitPrice) FROM Sales.SalesOrderDetail)






SELECT AVG(Total) FROM  
	(
	SELECT SalesOrderID, SUM(UnitPrice * OrderQty) AS Total 
		FROM Sales.SalesOrderDetail 
		WHERE SalesOrderID in 
			(SELECT SalesOrderID FROM Sales.SalesOrderDetail
				WHERE ProductID = 866)
		GROUP BY SalesOrderID
	) d








WITH 
DetailsFor866
AS
(
SELECT SalesOrderID, UnitPrice, OrderQty FROM Sales.SalesOrderDetail
WHERE ProductID=866
),
OrderValue
AS
( Select SalesOrderID, SUM(UnitPrice * OrderQty) AS Total 
FROM DetailsFor866
GROUP BY SalesOrderID),
OrdersAvg
AS
(
SELECT AVG(Total) AvgTotal FROM OrderValue
),
MakeCall
AS
(
SELECT SH.SalesPersonID FROM OrderValue AS OV
JOIN Sales.SalesOrderHeader AS SH  ON SH.SalesOrderID = OV.SalesOrderID
JOIN OrdersAvg ON 1=1
WHERE OV.Total > OrdersAvg.AvgTotal 
)
SELECT SalesPersonID,
CASE
WHEN SalesPersonID in (SELECT SalesPersonID FROM MakeCall) THEN 'Make Call!'
ELSE 'Relax'
END
FROM Sales.SalesPerson



