-
Notifications
You must be signed in to change notification settings - Fork 0
/
AdventureWorks Production Data Analysis.sql
83 lines (66 loc) · 3.13 KB
/
AdventureWorks Production Data Analysis.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
--Showing product quantity produced by year
SELECT Year(ActualEndDate) AS Production_Year
,COUNT(Year(ActualEndDate)) AS Total_Quantity
FROM AdventureWorks2014.Production.WorkOrderRouting Work
GROUP BY Year(ActualEndDate)
--Calculating total quantity in inventory across all production units
SELECT Inv.ProductID, Inv.LocationID, Loc.Name, Inv.Quantity
,SUM(Quantity) OVER (PARTITION BY Inv.LocationID) AS Total_Qty_by_Unit
FROM AdventureWorks2014.Production.ProductInventory Inv
JOIN AdventureWorks2014.Production.Location Loc
ON Inv.LocationID = Loc.LocationID
WHERE Quantity > '0'
ORDER BY Total_Qty_by_Unit DESC
--Showing products as finished goods ready for sale
WITH ProductInventory_CTE (ProductID, LocationID, Name, Quantity, Total_Qty_by_Product)
AS(
SELECT Inv.ProductID, Inv.LocationID, Loc.Name, Inv.Quantity
,SUM(Quantity) OVER (PARTITION BY Inv.ProductID) AS Total_Qty_by_Product
FROM AdventureWorks2014.Production.ProductInventory Inv
JOIN AdventureWorks2014.Production.Location Loc
ON Inv.LocationID = Loc.LocationID
)
SELECT ProductID
,LocationID
,Name AS Production_Unit
,Total_Qty_by_Product
FROM ProductInventory_CTE
WHERE LocationID = '7'
--Showing Total Days of Production and Total Days Overdue by Production Unit from 2011 to 2014
SELECT Work.LocationID
,Name AS Production_Unit
,SUM(DATEDIFF(day, ActualStartDate, ActualEndDate)) AS Total_Days_Production_by_Prod_Unit
,SUM(DATEDIFF(day, ScheduledStartDate, ActualStartDate)) AS Total_Days_Overdue_by_Prod_Unit
FROM AdventureWorks2014.Production.WorkOrderRouting Work
JOIN AdventureWorks2014.Production.Location Loc
ON Work.LocationID = Loc.LocationID
GROUP BY Work.LocationID, Name
ORDER BY Total_Days_Production_by_Prod_Unit DESC
----Showing Number of Days of Production and Number of Days Overdue for Subassembly Production Unit from 2011 to 2014
SELECT Work.LocationID
,Loc.Name AS Production_Unit
,ScheduledStartDate
,ActualStartDate
,ActualEndDate
,DATEDIFF(day, ActualStartDate, ActualEndDate) AS Number_of_Days_to_Finish_a_Work_Order
,DATEDIFF(day, ScheduledStartDate, ActualStartDate) AS Number_of_Days_Overdue
FROM AdventureWorks2014.Production.WorkOrderRouting Work
JOIN AdventureWorks2014.Production.Location Loc
ON Work.LocationID = Loc.LocationID
GROUP BY Work.LocationID, Name, ActualStartDate, ActualEndDate, ScheduledStartDate
--Calculating total orders and sales amount by product
WITH SalesOrder_CTE (LineTotal, ProductID, Name, OrderQty, Total_Sales_Amt_by_Product, Total_Orders_by_Product)
AS(
SELECT ord.ProductID, ord.LineTotal, prod.Name, ord.OrderQty
,SUM(LineTotal) OVER (PARTITION BY ord.ProductID) AS Total_Sales_Amt_by_Product
,SUM(OrderQty) OVER (PARTITION BY ord.ProductID) AS Total_Orders_by_Product
FROM AdventureWorks2014.Sales.SalesOrderDetail ord
JOIN AdventureWorks2014.Production.Product prod
ON ord.ProductID = prod.ProductID
)
SELECT Name AS Product_Name
,FORMAT (Total_Sales_Amt_by_Product,'C','en-us') AS Total_Sales_Amt_by_Product
,Total_Orders_by_Product
FROM SalesOrder_CTE
GROUP BY Name, Total_Sales_Amt_by_Product, Total_Orders_by_Product
ORDER BY Total_Sales_Amt_by_Product DESC