forked from aravindsuri/ADFTutorial
-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathusp_LoadStageSales.sql
More file actions
89 lines (77 loc) · 5.75 KB
/
usp_LoadStageSales.sql
File metadata and controls
89 lines (77 loc) · 5.75 KB
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
84
85
86
87
88
89
USE [WineStoreDW]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[usp_LoadStageSales]
AS
BEGIN
--Insert stage.Sales
TRUNCATE TABLE stage.Sales
--Insert stage.Arancione_Sales into stage.Sales
--Set ProductNo as -1 and SalesTerritory as EU and SalesCurrency as EUR
INSERT INTO stage.Sales
(
[ProductNo],[OnlineRetailer],[SalesMonth],[SalesTerritory],[SalesCurrency],[Title],[Vintage],[Variety],[Score],[ListPrice],[Quantity]
)
SELECT -1 ,
sales.[OnlineRetailer],
sales.[SalesMonth],
'EU',
'EUR',
sales.[Title],
sales.[Vintage],
sales.[Variety],
sales.[Score],
sales.[ListPrice],
sales.[Quantity]
FROM [stage].[Arancione_Sales] sales
--Insert stage.Verde_Sales into stage.Sales
--Set ProductNo as -1 and SalesTerritory as EU and SalesCurrency as EUR
INSERT INTO stage.Sales
(
[ProductNo],[OnlineRetailer],[SalesMonth],[SalesTerritory],[SalesCurrency],[Title],[Vintage],[Variety],[Score],[ListPrice],[Quantity]
)
SELECT -1 ,
sales.[OnlineRetailer],
sales.[SalesMonth],
'EU',
'EUR',
sales.[Title],
sales.[Vintage],
sales.[Variety],
sales.[Score],
sales.[ListPrice],
sales.[Quantity]
FROM [stage].[Verde_Sales] sales
--Insert stage.Celeste_Sales into stage.Sales and set the Vintage to 9999 if it is NULL
--Set ProductNo as -1
INSERT INTO stage.Sales
(
[ProductNo],[OnlineRetailer],[SalesMonth],[SalesTerritory],[SalesCurrency],[Title],[Vintage],[Variety],[Score],[ListPrice],[Quantity]
)
SELECT -1 ,
sales.[OnlineRetailer],
sales.[SalesMonth],
sales.[SalesRegion] ,
sales.[SalesCurrency],
sales.[Title],
ISNULL(sales.[Vintage], 9999),
sales.[Variety],
sales.[Score],
sales.[ListPrice],
sales.[Quantity]
FROM [stage].[Celeste_Sales] sales
--Update the ProductNo from the stage.Product Table by joining on Title, Vintage and Score
UPDATE stage.[Sales]
SET stage.[Sales].[ProductNo] = b.[ProductNo]
FROM stage.Sales a
INNER JOIN [stage].[Product] b
ON a.[Title] = b.[Title]
AND a.[Vintage] = b.[Vintage]
AND a.[Score] = b.[Score]
DELETE Stage.Sales WHERE ProductNo = -1
DELETE Stage.Sales WHERE Quantity = 0
END
GO