text: /******************************************************************************
  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
  
  
  