DBI310

“Calculate how many days since the previous analyze the distribution of sales”

Set definition:

By a "set" we mean any collection M into a whole of definite, distinct objects m (which are called the "elements" of M) of our perception or of our thought.

-- Georg Cantor

-- percent of total and diff from average WITH CustAggregates AS ( SELECT custid, SUM(val) AS sumval, AVG(val) AS avgval FROM Sales.OrderValues

GROUP BY custid ), GrandAggregates AS ( SELECT SUM(val) AS sumval, AVG(val) AS avgval FROM Sales.OrderValues

) SELECT O.orderid, O.custid, O.val, O.val / CA.sumval AS pctcust, O.val - CA.avgval AS diffcust, O.val / GA.sumval AS pctall, O.val - GA.avgval AS diffall FROM Sales.OrderValues AS O JOIN CustAggregates AS CA ON O.custid = CA.custid

CROSS JOIN GrandAggregates AS GA;

-- percent of total and diff from average with filter SELECT orderid, custid, val, val / (SELECT SUM(O2.val) FROM Sales.OrderValues AS O2 WHERE O2.custid = O1.custid

AND orderdate >= '20070101' AND orderdate < '20080101') AS pctcust, val - (SELECT AVG(O2.val) FROM Sales.OrderValues AS O2 WHERE O2.custid = O1.custid

AND orderdate >= '20070101' AND orderdate < '20080101') AS diffcust, val / (SELECT SUM(O2.val) FROM Sales.OrderValues AS O2 WHERE orderdate >= '20070101' AND orderdate < '20080101') AS pctall, val - (SELECT AVG(O2.val) FROM Sales.OrderValues AS O2 WHERE orderdate >= '20070101' AND orderdate < '20080101') AS diffall FROM Sales.OrderValues AS O1 WHERE orderdate >= '20070101' AND orderdate < '20080101';

-- islands (e.g., consecutive periods of activity) SELECT MIN(col1) AS start_range, MAX(col1) AS end_range FROM (SELECT col1, (SELECT MIN(B.col1) FROM dbo.T1 AS B WHERE B.col1 >= A.col1

AND NOT EXISTS (SELECT * FROM dbo.T1 AS C WHERE C.col1 = B.col1 + 1)) AS grp FROM dbo.T1 AS A) AS D GROUP BY grp;

-- account balances SELECT S1.actid, S1.tranid, S1.val, SUM(S2.val) AS balance FROM dbo.Accounts AS S1 JOIN dbo.Accounts AS S2 ON S2.actid = S1.actid

AND S2.tranid <= S1.tranid

GROUP BY S1.actid, S1.tranid, S1.val;

-- account balances SELECT actid, tranid, val, SUM(val) OVER(PARTITION BY actid ORDER BY tranid ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS balance FROM dbo.Accounts;

SELECT orderid, orderdate, val, RANK() OVER(ORDER BY val DESC) AS rnk FROM Sales.OrderValues; orderid orderdate val rnk ----------- ----------------------- --------- --- 10865 2008-02-02 00:00:00.000 16387.50 1 10981 2008-03-27 00:00:00.000 15810.00 2 11030 2008-04-17 00:00:00.000 12615.05 3 10889 2008-02-16 00:00:00.000 11380.00 4 10417 2007-01-16 00:00:00.000 11188.40 5 ...

SELECT orderid, orderdate, val, RANK() OVER(ORDER BY val DESC) AS rnk FROM Sales.OrderValues; orderid orderdate val rnk ----------- ----------------------- --------- --- 10865 2008-02-02 00:00:00.000 16387.50 10981 2008-03-27 00:00:00.000 15810.00 1 11030 2008-04-17 00:00:00.000 12615.05 10889 2008-02-16 00:00:00.000 11380.00 10417 2007-01-16 00:00:00.000 11188.40 ...

2 3 4 5

-- percent of total and diff from average SELECT orderid, custid, val, val / SUM(val) OVER(PARTITION BY custid) AS pctcust, val - AVG(val) OVER(PARTITION BY custid) AS diffcust, val / SUM(val) OVER() AS pctall, val - AVG(val) OVER() AS diffall FROM Sales.OrderValues

WHERE orderdate >= '20070101' AND orderdate < '20080101';

-- traditional SELECT orderid, custid, val, val / (SELECT SUM(O2.val) FROM Sales.OrderValues AS O2 WHERE O2.custid = O1.custid

AND orderdate >= '20070101' AND orderdate < '20080101') AS pctcust, val - (SELECT AVG(O2.val) FROM Sales.OrderValues AS O2 WHERE O2.custid = O1.custid

AND orderdate >= '20070101' AND orderdate < '20080101') AS diffcust, val / (SELECT SUM(O2.val) FROM Sales.OrderValues AS O2 WHERE orderdate >= '20070101' AND orderdate < '20080101') AS pctall, val - (SELECT AVG(O2.val) FROM Sales.OrderValues AS O2 WHERE orderdate >= '20070101' AND orderdate < '20080101') AS diffall FROM Sales.OrderValues AS O1 WHERE orderdate >= '20070101' AND orderdate < '20080101'; -- window functions SELECT orderid, custid, val, val / SUM(val) OVER(PARTITION BY custid) AS pctcust, val - AVG(val) OVER(PARTITION BY custid) AS diffcust, val / SUM(val) OVER() AS pctall, val - AVG(val) OVER() AS diffall FROM Sales.OrderValues

WHERE orderdate >= '20070101' AND orderdate < '20080101';

-- account balances SELECT actid, tranid, val, SUM(val) OVER(PARTITION BY actid ORDER BY tranid ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS balance FROM dbo.Accounts;

Running Totals

-- traditional SELECT S1.actid, S1.tranid, S1.val, SUM(S2.val) AS balance FROM dbo.Accounts AS S1 JOIN dbo.Accounts AS S2 ON S2.actid = S1.actid

AND S2.tranid <= S1.tranid

GROUP BY S1.actid, S1.tranid, S1.val; -- window functions SELECT actid, tranid, val, SUM(val) OVER(PARTITION BY actid ORDER BY tranid) AS balance FROM dbo.Accounts;

Recency

-- traditional SELECT custid, orderdate, orderid, DATEDIFF(day, (SELECT TOP (1) I.orderdate

FROM Sales.Orders AS I WHERE I.custid = O.custid

AND I.orderdate <= O.orderdate

AND (I.orderdate < O.orderdate

OR I.orderid < O.orderid) ORDER BY orderdate DESC, orderid DESC), orderdate) AS diff FROM Sales.Orders AS O; -- window functions SELECT custid, orderdate, orderid, DATEDIFF(day, LAG(orderdate) OVER(PARTITION BY custid ORDER BY orderdate, orderid), orderdate) AS diff FROM Sales.Orders;

Recency

Islands

-- traditional SELECT MIN(col1) AS start_range, MAX(col1) AS end_range FROM (SELECT col1, (SELECT MIN(B.col1) FROM dbo.T1 AS B WHERE B.col1 >= A.col1

AND NOT EXISTS (SELECT * FROM dbo.T1 AS C GROUP BY grp; WHERE C.col1 = B.col1 + 1)) AS grp FROM dbo.T1 AS A) AS D -- window functions SELECT MIN(col1) AS start_range, MAX(col1) AS end_range FROM (SELECT col1, col1 - ROW_NUMBER() OVER(ORDER BY col1) AS grp FROM dbo.T1) AS D GROUP BY grp;

Islands

Questions?

Thank You!

Visit the updated website for SQL Server ® Code Name “Denali” on www.microsoft.com/sqlserver and sign to be notified when the next CTP is available Follow the @SQLServer Twitter account to watch for updates Try the new SQL Server Mission Critical BareMetal Hand’s on-Labs Visit the SQL Server Product Demo Stations in the DBI Track section of the . Bring your questions, ideas and conversations!

• Microsoft® SQL Server® Security & Management • Microsoft® SQL Server® Programmability • Microsoft® SQL Server® Mission Critical • Microsoft® SQL Server® Optimization and Scalability • Microsoft® SQL Server® Data Warehousing • Microsoft® SQL Server® Data Integration

http://northamerica.msteched.com

www.microsoft.com/teched http://microsoft.com/technet www.microsoft.com/learning http://microsoft.com/msdn