All these examples work in AdventureWorks Database
For SQL Server 2012 and Higher.
Use
AdventureWorksDW2014
GO
/*************************************************************
ONE
Running Total Calculation
Give Daily, Week to Date & Year to Date
Sales
*****************************************************************/
SELECT
d.CalendarYear YEAR,
d.WeekNumberOfYear WEEK,
d.EnglishMonthName MONTH,
d.FullDateAlternateKey DATE,
SUM(f.SalesAmount) DailySales,
SUM(SUM(f.SalesAmount)) OVER ( PARTITION BY d.CalendarYear,
WeekNumberOfYear ORDER BY
FullDateAlternateKey ) WTDSales,
SUM(SUM(f.SalesAmount)) OVER ( PARTITION BY d.CalendarYear,
EnglishMonthName ORDER BY
FullDateAlternateKey ) MTDSales,
SUM(SUM(f.SalesAmount)) OVER ( PARTITION BY d.CalendarYear ORDER BY FullDateAlternateKey )
YTDSales
FROM [dbo].[FactInternetSales] f
JOIN [dbo].[DimDate] d
ON f.OrderDateKey = d.DateKey
--where d.CalendarYear = 2013
GROUP BY d.CalendarYear,
d.WeekNumberOfYear,
d.EnglishMonthName,
d.FullDateAlternateKey
ORDER BY
d.FullDateAlternateKey
|
Year
|
Week
|
Month
|
Date
|
DailySales
|
WTDSales
|
MTDSales
|
YTDSales
|
|
2010
|
53
|
December
|
12/29/10
|
14477.34
|
14477.34
|
14477.34
|
14477.34
|
|
2010
|
53
|
December
|
12/30/10
|
13931.52
|
28408.86
|
28408.86
|
28408.86
|
|
2010
|
53
|
December
|
12/31/10
|
15012.18
|
43421.04
|
43421.04
|
43421.04
|
|
2011
|
1
|
January
|
1/1/11
|
7156.54
|
7156.54
|
7156.54
|
7156.54
|
|
2011
|
2
|
January
|
1/2/11
|
15012.18
|
15012.18
|
22168.72
|
22168.72
|
|
2011
|
2
|
January
|
1/3/11
|
14313.08
|
29325.26
|
36481.8
|
36481.8
|
|
2011
|
2
|
January
|
1/4/11
|
7855.638
|
37180.9
|
44337.44
|
44337.44
|
/*************************************************************
TWO
Percent of Total.
% of Sales of This Product to Over All Sales.
*****************************************************************/
SELECT
p.EnglishProductName
Product,
SUM(SalesAmount) AS SalesAmount,
CAST(100.00* SUM(SalesAmount) / SUM(SUM(SalesAmount)) OVER() AS DECIMAL(6,2)) AS [% OF Total]
FROM dbo.FactResellerSales f
JOIN dbo.DimReseller r
ON f.ResellerKey = r.ResellerKey
JOIN dbo.DimProduct p
ON f.ProductKey = p.ProductKey
GROUP BY EnglishProductName
ORDER BY [% OF Total] DESC
|
Product
|
SalesAmount
|
% of Total
|
|
Mountain-200 Black, 38
|
3105726.659
|
3.86
|
|
Mountain-200 Black, 42
|
2646352.669
|
3.29
|
|
Mountain-200 Silver, 38
|
2354215.235
|
2.93
|
|
Mountain-200 Silver, 42
|
2181044.288
|
2.71
|
|
Mountain-200 Silver, 46
|
2133156.844
|
2.65
|
/*************************************************************
THREE
Gaps in Sequence
Find the date ranges when Orders for a
Product were not Received
*****************************************************************/
;WITH cte_orders
AS
(
SELECT
p.EnglishProductName
AS
Product,
f.OrderDate ,
ROW_NUMBER() OVER ( PARTITION BY EnglishProductName ORDER
BY OrderDate ) AS rownum
FROM dbo.FactInternetSales f
JOIN dbo.DimProduct p
ON f.ProductKey = p.ProductKey
WHERE f.OrderDateKey BETWEEN
20130101 AND 20131231 --
For Year 2013
AND p.EnglishProductName =
'Short-Sleeve Classic Jersey, S'
GROUP BY EnglishProductName,f.OrderDate
)
SELECT cur.Product, cur.OrderDate + 1 [NO
ORDER BEGIN] , nxt.OrderDate
- 1 [NO ORDER END]
FROM cte_orders
cur
JOIN cte_orders
nxt
ON cur.Product = nxt.Product AND nxt.rownum = cur.rownum + 1
WHERE nxt.OrderDate > cur.OrderDate + 1
ORDER BY cur.Product, cur.OrderDate
|
Product
|
No Order Begin
|
No Order End
|
|
Short-Sleeve Classic
Jersey, S
|
1/2/2013
|
1/4/2013
|
|
Short-Sleeve Classic
Jersey, S
|
1/6/2013
|
1/6/2013
|
|
Short-Sleeve Classic
Jersey, S
|
1/8/2013
|
1/10/2013
|
|
Short-Sleeve Classic
Jersey, S
|
1/12/2013
|
1/12/2013
|
|
Short-Sleeve Classic
Jersey, S
|
1/14/2013
|
1/15/2013
|
|
Short-Sleeve Classic
Jersey, S
|
1/17/2013
|
1/17/2013
|
|
Short-Sleeve Classic
Jersey, S
|
1/19/2013
|
1/22/2013
|
|
Short-Sleeve Classic
Jersey, S
|
1/25/2013
|
1/27/2013
|
|
Short-Sleeve Classic
Jersey, S
|
1/29/2013
|
1/30/2013
|
*****************************************************************/
FOUR
/* Islands in Sequence */
/* Countinuous Range of Dates when Orders for a
Product were received */
*****************************************************************/
;WITH cte_orders
AS
(
SELECT
EnglishProductName AS
Product ,
f.OrderDate
FROM dbo.FactInternetSales f
JOIN dbo.DimProduct p
ON f.ProductKey = p.ProductKey
WHERE f.OrderDateKey BETWEEN
20130101 AND 20131231 --
For Year 2013
AND p.EnglishProductName =
'Short-Sleeve Classic Jersey, S'
GROUP BY EnglishProductName,f.OrderDate
) ,
start_dates
AS
(
SELECT
Product, OrderDate,
ROW_NUMBER()
OVER ( PARTITION BY Product ORDER BY OrderDate ) AS rownum
FROM
cte_orders o1
WHERE NOT EXISTS (
SELECT * FROM cte_orders o2 WHERE o1.Product = o2.Product AND o2.OrderDate = o1.OrderDate - 1
)
) ,
end_dates AS
(
SELECT
Product, OrderDate,
ROW_NUMBER()
OVER ( PARTITION BY Product ORDER BY OrderDate ) AS rownum
FROM
cte_orders o1
WHERE NOT EXISTS (
SELECT * FROM cte_orders o2 WHERE o1.Product = o2.Product AND o2.OrderDate = o1.OrderDate + 1
)
)
SELECT s.Product, s.OrderDate AS [ORDER
START], e.OrderDate
AS [ORDER END] FROM
start_dates
s
JOIN end_dates
e
ON s.Product = e.Product AND s.rownum = e.rownum
ORDER BY s.Product, s.OrderDate
|
Product
|
Order Start
|
Order End
|
|
Short-Sleeve Classic
Jersey, S
|
1/1/2013
|
1/1/2013
|
|
Short-Sleeve Classic
Jersey, S
|
1/5/2013
|
1/5/2013
|
|
Short-Sleeve Classic
Jersey, S
|
1/7/2013
|
1/7/2013
|
|
Short-Sleeve Classic
Jersey, S
|
1/11/2013
|
1/11/2013
|
|
Short-Sleeve Classic
Jersey, S
|
1/13/2013
|
1/13/2013
|
|
Short-Sleeve Classic
Jersey, S
|
1/16/2013
|
1/16/2013
|
|
Short-Sleeve Classic
Jersey, S
|
1/18/2013
|
1/18/2013
|
|
Short-Sleeve Classic
Jersey, S
|
1/23/2013
|
1/24/2013
|
|
Short-Sleeve Classic
Jersey, S
|
1/28/2013
|
1/28/2013
|
*****************************************************************/
Five
first_value and last_value functions
Give Salary for Each Employee as well as Min
and Max Sal For his Department
*****************************************************************/
SELECT FirstName, LastName, BaseRate*PayFrequency AS sal, DepartmentName,
first_value(BaseRate*PayFrequency) OVER ( PARTITION BY DepartmentName ORDER
BY BaseRate*PayFrequency
) AS min_sal,
last_value(BaseRate*PayFrequency) OVER ( PARTITION BY DepartmentName ORDER
BY BaseRate*PayFrequency
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING )
AS max_sal
FROM [dbo].[DimEmployee]
ORDER BY DepartmentName
|
FirstName
|
LastName
|
sal
|
DepartmentName
|
min_sal
|
max_sal
|
|
Zainal
|
Arifin
|
35.577
|
Document Control
|
20.5
|
35.577
|
|
Chris
|
Norred
|
33.6538
|
Document Control
|
20.5
|
35.577
|
|
Tengiz
|
Kharatishvili
|
33.6538
|
Document Control
|
20.5
|
35.577
|
|
Sean
|
Chai
|
20.5
|
Document Control
|
20.5
|
35.577
|
|
Karen
|
Berge
|
20.5
|
Document Control
|
20.5
|
35.577
|
*****************************************************************/
Six
-- Median Calcuation
-- Give Median Sales.
*****************************************************************/
-- Median Sales Amount. Method 1.
;WITH cte_x
AS
(
SELECT SalesAmount,
ROW_NUMBER() OVER (ORDER BY SalesAmount) AS RowNo,
COUNT(*) OVER () AS Count1
FROM dbo.[FactInternetSales]
)
SELECT SalesAmount AS
MedianSalesAmt
FROM cte_x
WHERE CEILING(0.5 * Count1) = RowNo;
-- Median Sales Amount. Method 2.
SELECT TOP 1 PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY SalesAmount) OVER () AS MedianSalesAmount
FROM dbo.[FactInternetSales]
*****************************************************************/
Seven
Give Event Duration from Event Logs Table
*****************************************************************/
IF OBJECT_ID( 'tempdb.dbo.#event_logs' )
IS NOT NULL
DROP TABLE #event_logs
CREATE TABLE #event_logs ( person VARCHAR(20), event_type VARCHAR(20), event_time DATETIME
)
INSERT INTO #event_logs VALUES ( 'Tom' , 'Login', '05-01-2015 08:00 AM'
),
( 'Tom' , 'Away', '05-01-2015 12:00 PM'
),
( 'Tom' , 'Available', '05-01-2015 1:00 PM'
),
( 'Tom' , 'Logout', '05-01-2015 5:00 PM'
),
( 'Jim' , 'Login', '05-02-2015 08:10 AM'
),
( 'Jim' , 'Away', '05-02-2015 12:01 PM'
),
( 'Jim' , 'Available', '05-02-2015 1:01 PM'
),
( 'Jim' , 'Logout', '05-02-2015 5:10 PM' )
;WITH cte_x AS
(
SELECT
* , ROW_NUMBER() OVER ( PARTITION BY person, CAST(event_time AS DATE) ORDER BY event_time ) rowno
FROM
#event_logs
)
SELECT
start.person, start.event_type, start.event_time AS
start_time, end1.event_time
AS end_time
FROM cte_x
START
LEFT JOIN cte_x AS end1
ON start.person = end1.person
AND CAST(start.event_time AS DATE) = CAST(end1.event_time AS DATE) /* Limit to same date */
AND start.rowno = end1.rowno - 1
|
person
|
event_type
|
start_time
|
end_time
|
|
Jim
|
Login
|
5/2/15 8:10 AM
|
5/2/15 12:01 PM
|
|
Jim
|
Away
|
5/2/15 12:01 PM
|
5/2/15 1:01 PM
|
|
Jim
|
Available
|
5/2/15 1:01 PM
|
5/2/15 5:10 PM
|
|
Jim
|
Logout
|
5/2/15 5:10 PM
|
NULL
|
|
Tom
|
Login
|
5/1/15 8:00 AM
|
5/1/15 12:00 PM
|
|
Tom
|
Away
|
5/1/15 12:00 PM
|
5/1/15 1:00 PM
|
|
Tom
|
Available
|
5/1/15 1:00 PM
|
5/1/15 5:00 PM
|
|
Tom
|
Logout
|
5/1/15 5:00 PM
|
NULL
|
*****************************************************************/
Eight
Find & Delete Duplicates from Table
*****************************************************************/
IF OBJECT_ID( 'tempdb.dbo.#t' ) IS NOT NULL
DROP TABLE #t
CREATE TABLE #t ( ID INT )
INSERT INTO #t
VALUES
(1), (1), (2), (3), (4), (5)
/* Find Duplicates */
;WITH cte_x AS (
SELECT ID,
ROW_NUMBER() OVER ( PARTITION BY ID ORDER BY (SELECT NULL) ) row_no
FROM #t
)
SELECT ID FROM cte_x WHERE
row_no > 1
/* Delete Duplicates */
;WITH cte_x AS (
SELECT ID, ROW_NUMBER() OVER ( PARTITION BY ID ORDER BY (SELECT NULL) ) row_no
FROM #t
)
DELETE FROM cte_x WHERE
row_no > 1
*****************************************************************/
NINE
NTile Functions.
Divide Products into 4 Quartiles.
Top 25 % are in First Quartile and Bottom 25 %
are in Last Quartile.
*****************************************************************/
SELECT
s.EnglishProductSubcategoryName
[Prod Category],
SUM(SalesAmount) AS SalesAmount,
NTILE(4) OVER ( ORDER BY SUM(SalesAmount) DESC ) Quartile
FROM dbo.FactResellerSales f
JOIN dbo.DimProduct p
ON f.ProductKey = p.ProductKey
JOIN [dbo].[DimProductSubcategory] s
ON s.ProductSubcategoryKey =
p.ProductSubcategoryKey
JOIN dbo.DimProductCategory c
ON c.ProductCategoryKey =
s.ProductCategoryKey
WHERE
EnglishProductCategoryName = 'Clothing'
GROUP BY c.EnglishProductCategoryName,EnglishProductSubcategoryName
ORDER BY SalesAmount DESC
|
Prod Category
|
SalesAmount
|
Quartile
|
|
Jerseys
|
579308.7084
|
1
|
|
Shorts
|
342202.717
|
1
|
|
Vests
|
223801.3707
|
2
|
|
Gloves
|
207775.1742
|
2
|
|
Tights
|
201833.006
|
3
|
|
Bib-Shorts
|
166739.7086
|
3
|
|
Caps
|
31541.3461
|
4
|
|
Socks
|
24638.8081
|
4
|
*****************************************************************/
TEN
MOVING Average Sales for Last Three Months.
*****************************************************************/
SELECT
CalendarYear YEAR,
d.EnglishMonthName MONTH,
SUM(f.SalesAmount) MonthlySales,
(SUM(f.SalesAmount) +
lag(SUM(f.SalesAmount)) OVER( ORDER BY CalendarYear
* 100 +
MonthNumberOfYear) +
lag(SUM(f.SalesAmount),2) OVER( ORDER BY CalendarYear * 100
+ MonthNumberOfYear)
) / 3 AS
ThreeMosMovingAVG
FROM [dbo].[FactInternetSales] f
JOIN [dbo].[DimDate] d
ON f.OrderDateKey = d.DateKey
--where d.CalendarYear = 2013
GROUP BY d.CalendarYear,
d.MonthNumberOfYear,
d.EnglishMonthName
ORDER BY
d.CalendarYear
DESC,
d.MonthNumberOfYear DESC
|
Year
|
Month
|
MonthlySales
|
ThreeMosMovingAVG
|
|
2014
|
January
|
45694.72
|
1233658.357
|
|
2013
|
December
|
1874360.29
|
1776191.253
|
|
2013
|
November
|
1780920.06
|
1633903.053
|
|
2013
|
October
|
1673293.41
|
1557284.887
|
|
2013
|
September
|
1447495.69
|
1456745.687
|
|
2013
|
August
|
1551065.56
|
1521973.05
|
|
2013
|
July
|
1371675.81
|
1433148.84
|
