Repository navigation
Expand file tree
/
Copy pathNewScdScript.sql
More file actions
96 lines (82 loc) · 5.76 KB
/
Copy pathNewScdScript.sql
File metadata and controls
96 lines (82 loc) · 5.76 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
90
91
92
93
94
/******************************************************************************
This Script Goal Is to Show How we can create a new Scd table for a specific Field
From an Scd table.
In This Example we create a customer table which is SCD Table.
A customer can have several Status:
2 - Active customer
3 - Cancel Customer
4 - Waiting Customer (a Customer that is being wait to become Active)
Then we want to add 2 new Field to this table:
A) Customer_Status_From_Date
B) Customer_Status_To_Date
In order to calculate these to fields we need to create kind of a new SCD only for the "Status" field.
******************************************************************************/
/*******************************************
Create A new Customer Table
*******************************************/
CREATE TABLE [dbo].[Dim_Customers](
[PK_Customer] [bigint] NOT NULL,
[From_Date] [date] NULL,
[To_Date] [date] NULL,
[Current_Ind] [int] NOT NULL,
[Customer_Full_ID] [varchar](11) NOT NULL,
[First_Name] [varchar](30) NULL,
[Family_Name] [varchar](30) NULL,
[Status_Code] [smallint] NULL,
[Status_Desc] [varchar](14) NULL,
[Family_Head_Full_ID] [varchar](11) NULL,
[Family_Relative_Type] [smallint] NULL,
[Birth_Date] [date] NULL,
[Gender_Code] [smallint] NULL,
[Gender_Desc] [varchar](7) NULL,
[City_Code] [numeric](5, 0) NULL,
[City_Desc] [varchar](50) NULL,
[Street] [nvarchar](50) NULL,
[Home_Num] [varchar](30) NULL,
[Zip_Code] [int] NULL,
[Home_Phone_No] [varchar](11) NULL,
[Work_Phone_No] [varchar](11) NULL,
[Online_Mail] [nvarchar](100) NULL,
[Online_Start_Date] [date] NULL,
[Online_Ind] [smallint] NULL,
[On_Line_Ind_Desc] [varchar](50) NULL,
[Insurance_Date] [date] NULL,
)
/*************************************
Insert values to a specific customer
*************************************/
insert into [dbo].[Dim_Customers]
values (13572558920100301,'2010-03-01','2010-10-31',0,'1-35725589','Benny','Cohen',2,'Active','1-35728461',1,'1986-08-03',1,'Male',3000,'Hod Hasharon','Sokolov','',12568,'077-5425698','00-0',null,null,0,'Not registrate to Online','2010-01-14'),
(13572558920101101,'2010-11-01','2010-12-31',0,'1-35725589','Benny','Cohen',2,'Active','1-35728461',1,'1986-08-03',1,'Male',3000,'Hod Hasharon','Habanim','',12568,'052-5486132','00-0',null,null,0,'Not registrate to Online','2010-01-14'),
(13572558920110101,'2011-01-01','2012-09-30',0,'1-35725589','Benny','Cohen',2,'Active','1-35728461',1,'1986-08-03',1,'Male',3000,'Hod Hasharon','Habanim','',12568,'052-5486132','03-5478563',null,null,0,'Not registrate to Online','2010-01-14'),
(13572558920121001,'2012-10-01','2013-07-31',0,'1-35725589','Benny','Cohen',2,'Active','1-35728461',1,'1986-08-03',1,'Male',3000,'Hod Hasharon','Habanim','',12568,'052-5486132','03-5478563',null,null,0,'Not registrate to Online','2013-03-03'),
(13572558920130801,'2013-08-01','2013-08-31',0,'1-35725589','Benny','Cohen',3,'Cancel','1-35728461',1,'1986-08-03',1,'Male',3000,'Hod Hasharon','Habanim','',12568,'052-5486132','03-5478563',null,null,0,'Not registrate to Online','2013-03-03'),
(13572558920130901,'2013-09-01','2013-09-12',0,'1-35725589','Benny','Cohen',4,'Waiting','1-35728461',1,'1986-08-03',1,'Male',3000,'Hod Hasharon','Habanim','',12568,'052-5486132','03-5478563',null,null,0,'Not registrate to Online','2013-03-03'),
(13572558920130913,'2013-09-13','2013-09-30',0,'1-35725589','Benny','Cohen',4,'Waiting','1-35728461',1,'1986-08-03',1,'Male',3000,'Hod Hasharon','Habanim','10',12568,'052-5486132','03-5478563',null,null,0,'Not registrate to Online','2013-03-03'),
(13572558920131001,'2013-10-01','2013-10-15',0,'1-35725589','Benny','Cohen',4,'Waiting','1-35728461',1,'1986-08-03',1,'Male',3000,'Hod Hasharon','Habanim','10',12568,'052-5486132','03-5478563',null,null,0,'Not registrate to Online','2013-03-03'),
(13572558920131016,'2013-10-16','2013-10-31',0,'1-35725589','Benny','Cohen',4,'Waiting','1-35728461',1,'1986-08-03',1,'Male',3000,'Hod Hasharon','Habanim','10',12568,'052-5486132','03-5478563',null,null,0,'Not registrate to Online','2013-10-10'),
(13572558920131101,'2013-11-01','2013-11-30',0,'1-35725589','Benny','Cohen',2,'Active','1-35728461',1,'1986-08-03',1,'Male',3000,'Hod Hasharon','Habanim','10',12568,'052-5486132','03-5478563',null,null,0,'Not registrate to Online','2013-10-10'),
(13572558920131201,'2013-12-01','2999-12-31',1,'1-35725589','Benny','Cohen',2,'Active','1-35728461',1,'1986-08-03',1,'Male',3000,'Hod Hasharon','Habanim','10',12568,'052-5486132','03-5478563','w.m@gmail.com','2011-09-08',1,'registrate to Online','2013-10-10')
/******************************
Add 2 new fields To Dim_Customer Table:
Customer_Status_From_Date
Customer_Status_To_Date
******************************/
Alter table [dbo].[Dim_Customers]
add Customer_Status_From_Date [date] null,
Customer_Status_To_Date [date] null
/**********************************
Calculate The Value of the new 2 fields -
**********************************/
SELECT Customer_Full_ID,status_code,MIN(nf.from_date) AS FROM_date, max(nf.to_date) as to_date
From
(
select
RANK() over( PARTITION BY Customer_Full_Id order by from_date) rnk_date , -->Rank Partition by Customer order by From Value
RANK () OVER (PARTITION BY Customer_Full_Id, status_code ORDER BY from_date ) rnk_date_and_status, --> Rank Partition by Customer And Status Order by From Value
RANK() over( PARTITION BY Customer_Full_Id order by from_date) - RANK () OVER (PARTITION BY Customer_Full_Id, status_code ORDER BY from_date ) AS Sub_RANK, --> Rank According to First Rank - Second Rank
*
from [dbo].[Dim_Customers]
)nf
group by Customer_Full_ID,status_code,Sub_RANK
order by 1,3