SCD Type2 with sql server Merge statement or SSIS package

Hi folks, Few days back i was working on SCD Type2. SCD is slowly changing dimensions & Type2 is used for managing history. I have implemented it through SSIS package however i am also describing sql merge statement to implement it.
I have few execution segments. I am explaining the order of execution of these segments. It is necessary because i am using some segment code in SSIS as well-

/*Segment1 */

Create table StagingEmployee(EmployeeCode int, EmployeeName varchar(100), Address varchar(100), 
 ChecksumData int Default -1);

Insert into StagingEmployee(EmployeeCode,EmployeeName,Address) Values(1001,'Vijay','Noida');
Insert into StagingEmployee(EmployeeCode,EmployeeName,Address) Values(1002,'Manoj','Gurugram');
Insert into StagingEmployee(EmployeeCode,EmployeeName,Address) Values(1003,'Jai','Delhi');

Update StagingEmployee Set ChecksumData=BINARY_CHECKSUM(EmployeeCode,EmployeeName,Address);

Create table dimEmployee(Employeeid int identity(1,1),Employeecode int,EmployeeName varchar(100),
  Address varchar(100),CreatedDate datetime null Default Getdate(),ExpirationDate datetime null,
  CurrentRecord char(1) Default 'Y',DimChecksum int Default -1);

Select * from StagingEmployee
Select * from dimEmployee


/*Segment2 */

Insert into dimEmployee(EmployeeCode,EmployeeName,Address,CreatedDate,ExpirationDate,DimChecksum)
Select EmployeeCode,EmployeeName,Address,CreatedDate,ExpirationDate,DimChecksum
From
(
 Merge into dimEmployee as T
 Using StagingEmployee as S
  ON T.EmployeeCode=S.EmployeeCode
 When Matched and T.DimChecksum<>S.ChecksumData and T.CurrentRecord='Y'
 Then
  Update Set
  ExpirationDate=GetDate()-1,
  CurrentRecord= 'N'
 When Not Matched
 Then 
  Insert(EmployeeCode,EmployeeName,Address,DimChecksum)
  Values(S.EmployeeCode,S.EmployeeName,S.Address,S.ChecksumData)
 Output
  $action,S.EmployeeCode,S.EmployeeName,S.Address,GetDate(),Null,S.ChecksumData
) as Tab 
  (action,EmployeeCode,EmployeeName,Address,CreatedDate,ExpirationDate,DimChecksum)
where action='UPDATE';

Select * from StagingEmployee
Select * from dimEmployee


/*Segment3 */

Truncate table StagingEmployee;
Insert into StagingEmployee(EmployeeCode,EmployeeName,Address) Values(1001,'Vijay','Pune');
Insert into StagingEmployee(EmployeeCode,EmployeeName,Address) Values(1005,'Varun','Mumbai');
Update StagingEmployee Set ChecksumData=BINARY_CHECKSUM(EmployeeCode,EmployeeName,Address);

/*Segment4 */ -- <----Execute2 code

Select * from StagingEmployee
Select * from dimEmployee
/*
Truncate table StagingEmployee;
Truncate table dimEmployee;
Drop table StagingEmployee;
Drop table dimEmployee;
*/

SQL SERVER :

Execute Segment1 ---> Segment2 --->Segment3 --->Segment2 --->Segment4
Check result after each segment.

Now execute Segment4

SSIS :

Execute Segment1 from above code.
Open BIDS or SSDT ---> Open/Create SSIS project --->Create new project
Create a connection string and drag data flow task and rename it as below :

Click on SCD Type2 (Control flow task) --> drag a source assistant and connect with table StagingEmplohyee and name it StagingEmployee

 

Now drag a slowly changing dimension and connect it from stagingEmployee(Source)
Click on slowly changing dimension and one wizard will open then follow like below :

 

 

Now it will look like this :

Now run the package and try the result in sql server management studio.

 





If you will check the result then DimChecksum value not correct so here either we can remove the column as we don't need it or we can correct it with the help of some package correction.
Also you need to correct some other components as below :

Correction on OLEDB Command as below (in sql query section and no change in mapping)

Correction on Insert destination > Mapping the checksumdata and then execute Segment4

Now Execute Segment1 ---> Run the package see the results

Now Execute Segment3 ---> run the package and check the results and then Execugte Segment4

Powershell to execute sql script on all server

Hi folks, I was working on some maintenance activity where i have to execute one sql script on all sql server in domain and collect the result on csv file.
So I created one txt file in which i kept all server list. I also put sql script on same folder. then i wrote below script on powershell. I hope it will help you too.

Add-PSSnapin SqlServerCmdletSnapin100
Add-PSSnapin SqlServerProviderSnapin100

$Serverlist= Get-Content "D:\Lab\PowerShellTest\AllServerScript\ServerList.txt"
$SqlScript= "D:\Lab\PowerShellTest\AllServerScript\Script.sql"
$Outputfile= "D:\Lab\PowerShellTest\AllServerScript\Report_$((Get-Date).ToString('MM-dd-yyyy_hh-mm-ss')).csv"
$Logfile= "D:\Lab\PowerShellTest\AllServerScript\Log_$((Get-Date).ToString('MM-dd-yyyy_hh-mm-ss')).txt"

#=====================
#Sql script to execute
#=====================
@(
ForEach($ServerName in $Serverlist)
{
try {
write-host $ServerName
Invoke-Sqlcmd -InputFile $SqlScript -serverinstance $ServerName -Database master}
Catch {

$ServerName | Out-File $Logfile -Append
$_ | Out-File $Logfile -Append
}}
) | export-csv -NoTypeInformation -Path $Outputfile


If you want to learn PowerShell, please follow below link-

Learn PowerShell with Vimal Lohani

Interview Question : Adding Each character of a number in a table

CREATE TABLE addnum ( id INT identity(1, 1) ,
                             val INT )
INSERT INTO addnum
SELECT ABS(CAST(NEWID() AS BINARY (6)) % 1000) + 1 randomNumber GO 1000
SELECT *
FROM addnum

;

WITH cte AS
  (SELECT id,
          cast(val AS varchar(10)) val,
          len(val) leng
   FROM addnum),
     cte1 AS
  (SELECT substring(val,1,1) x,
          id,
          1 num
   FROM cte
   UNION ALL SELECT Isnull(substring(cte.val,num+1,1),'0'),
                    cte.id,
                    num+1
   FROM cte1
   JOIN cte ON cte1.id=cte.id
   AND cte1.num<cte.leng)
SELECT cte.val,
       cte.id,
       sum(cast(x AS int)) addition
FROM cte1
JOIN cte ON cte.id=cte1.id
GROUP BY cte.val,
         cte.id
ORDER BY id

Dynamic Restore Script

Hi Folks! Few days back i was working on a project, I was trying to automate few things, I will share most of my work in next blogs, so i have also automate restore process for database having multiple files. It will work well with any number of files. You need to provide database name and backup location.

This script is for Sql Server. 

SET NOCOUNT ON

DECLARE @restoreTable TABLE (
 [LogicalName] NVARCHAR(128)
 ,[PhysicalName] NVARCHAR(260)
 ,[Type] CHAR(1)
 ,[FileGroupName] NVARCHAR(128)
 ,[Size] NUMERIC(20, 0)
 ,[MaxSize] NUMERIC(20, 0)
 ,[FileID] BIGINT
 ,[CreateLSN] NUMERIC(25, 0)
 ,[DropLSN] NUMERIC(25, 0)
 ,[UniqueID] UNIQUEIDENTIFIER
 ,[ReadOnlyLSN] NUMERIC(25, 0)
 ,[ReadWriteLSN] NUMERIC(25, 0)
 ,[BackupSizeInBytes] BIGINT
 ,[SourceBlockSize] INT
 ,[FileGroupID] INT
 ,[LogGroupGUID] UNIQUEIDENTIFIER
 ,[DifferentialBaseLSN] NUMERIC(25, 0)
 ,[DifferentialBaseGUID] UNIQUEIDENTIFIER
 ,[IsReadOnly] BIT
 ,[IsPresent] BIT
 ,[TDEThumbprint] VARBINARY(32) -- remove this column if using SQL 2005
 )

INSERT INTO @restoreTable
EXEC ('RESTORE FILELISTONLY FROM DISK = ''' + @backuppath + '''')

--SELECT * FROM @fileListTable
SET @Query = 'RESTORE DATABASE [' + @Dbname + '] FROM  DISK = N''' + @backuppath + ''' 
WITH  FILE = 1,'

SELECT @Query = @Query + ' 
MOVE N''' + LogicalName + ''' TO N''' + PhysicalName + ''','
FROM @restoreTable

SET @Query = @Query + ' 
NOUNLOAD,  REPLACE,  STATS = 5'

PRINT @Query
--Exec(Query)

Interview Question : Create attendance report through sql server query

Hi Everyone! Few months back, i attended sql server developer interview, My interviewer took me to the system and asked me to create complete script for attendance management monthly report in sql server.
I am sharing this question so that this may help you too :

Lets assume we have swapping card system, so when we swap or punch our employee card, logs are generated and they are saved at ATTEN_inout table in sql server.

CREATE TABLE ATTEN_inout (
 NAME VARCHAR(50)
 ,DATE DATETIME
 ,PRESENT_STATUS VARCHAR(10)
 ,TIME TIME
 ,inout BIT --0=IN 1=OUT
 )

INSERT INTO ATTEN_inout VALUES ('Amit','2016-07-01','PRESENT','09:00',0)  
INSERT INTO ATTEN_inout VALUES ('Amit','2016-07-01','PRESENT','18:00',1)  
INSERT INTO ATTEN_inout VALUES ('Manoj','2016-07-01','PRESENT','08:47',0)  
INSERT INTO ATTEN_inout VALUES ('Manoj','2016-07-01','PRESENT','14:23',1)  
INSERT INTO ATTEN_inout VALUES ('Amit','2016-07-02','PRESENT','10:12',0)  
INSERT INTO ATTEN_inout VALUES ('Amit','2016-07-02','PRESENT','16:19',1)  
INSERT INTO ATTEN_inout VALUES ('Amit','2016-07-03','PRESENT','11:11',0)
INSERT INTO ATTEN_inout VALUES ('Amit','2016-07-03','PRESENT','17:10',1)  
INSERT INTO ATTEN_inout VALUES ('Manoj','2016-07-03','PRESENT','09:12',0)  
INSERT INTO ATTEN_inout VALUES ('Manoj','2016-07-03','PRESENT','18:12',1)  
INSERT INTO ATTEN_inout VALUES ('Vijay','2016-07-13','PRESENT','11:12',0)
INSERT INTO ATTEN_inout VALUES ('Vijay','2016-07-13','PRESENT','17:30',1)  
INSERT INTO ATTEN_inout VALUES ('Vijay','2016-07-25','PRESENT','09:32',0)  
INSERT INTO ATTEN_inout VALUES ('Vijay','2016-07-25','PRESENT','18:54',1)  
SELECT * FROM ATTEN_inout

So now we need to find insights from this table and process that data and create monthly attendance report.

declare @startdate datetime ='07/01/2016'
declare @enddate datetime ='07/31/2016'

--Generating complete month dates
;with cte
as 
(select @startdate dt
union all
select dt+1 from cte where dt<@enddate
)
select * into #table from cte
--select * from #table

Declare @col varchar(max)=''
Select @Col=@col+'['+ Cast(Convert(Date,dt) as varchar) +'],' from #table
select @col=SUBSTRING(@col,1,datalength(@col)-1)
--select @col

Declare @query varchar(max)=''
Set @query='
select *  from 
(SELECT a.name,a.present_status,t.dt,cASE WHEN INOUT=0 THEN ''IN'' ELSE ''OUT'' END INOUT,TIME
FROM ATTEN_inout a JOIN #table t on a.Date=t.dt )k
PIVOT
(MIN(TIME) FOR dt IN ('+@col+')
)x'

--print(@query)
exec(@query)

Declare @column varchar(max)=''
Select @column=@column+'[IN '+ Cast(Convert(Date,dt) as varchar) +']  time null,'+'[OUT '+ Cast(Convert(Date,dt) as varchar) +']  time null,' from #table
select @column=SUBSTRING(@column,1,datalength(@column)-1)
--print @column

Declare @newqry nvarchar(max)
set @newqry= 'Create table report(name varchar(100), '+ @column +');';
exec(@newqry)

Declare @inqry varchar(max)=''
Select @inqry=@inqry+'['+name+ '],' from sys.columns where object_id=OBJECT_ID('report') AND name like 'IN%'
set  @inqry=SUBSTRING(@inqry,1,datalength(@inqry)-1 )
--print @inqry

Declare @qin nvarchar(max)
Set @qin='Insert into report (name, '+ SUBSTRING(@inqry,1,datalength(@inqry))+')  Select name, '+ @col +' from ('+@query+ ' Where inout=''in'')y'
print (@qin)
exec (@qin)


Declare @outqry varchar(max)=''
Select @outqry=@outqry+'['+name+ ']=['+SUBSTRING(name,5,DATALENGTH(name))+'],' from sys.columns where object_id=OBJECT_ID('report') AND name like 'Out%'
set  @outqry=SUBSTRING(@outqry,1,datalength(@outqry)-1 )
print @outqry
Set @qin= 'update r set '+@outqry+' from report r join ('+@query +') q on q.name=r.name and q.inout=''out'''
exec (@qin)

SELECT * FROM REPORT
drop table #table
drop table report
Drop table ATTEN_inout

Get time for restore or backup a database

SELECT percent_complete,

       CAST((estimated_completion_time/3600000) AS varchar) + ' hour(s), ' + CAST((estimated_completion_time %3600000)/60000 AS varchar) + 'min, ' + CAST((estimated_completion_time %60000)/1000 AS varchar) + ' sec' AS est_time_to_go,

       command,

       s.text,

       start_time,

       CAST(((DATEDIFF(s,start_time,GetDate()))/3600) AS varchar) + ' hour(s), ' + CAST((DATEDIFF(s,start_time,GetDate())%3600)/60 AS varchar) + 'min, ' + CAST((DATEDIFF(s,start_time,GetDate())%60) AS varchar) + ' sec' AS running_time,

       dateadd(SECOND,estimated_completion_time/1000, getdate()) AS est_completion_time

FROM sys.dm_exec_requests r CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) s

WHERE r.command IN ('RESTORE DATABASE',

                    'BACKUP DATABASE',

                    'RESTORE LOG',

                    'BACKUP LOG',

                    'RESTORE HEADERON')

Question of the day

Hi folks, I got a question from my junior in my organization-

Input the name of a person born in Generation X and output the names of all of their descendants born in Generation Z, but only if these descendants are brother and sister (have the same parent)

/*

Create table Test (id int ,name varchar(100),Parent_ID int null,Generation varchar(1),Gender varchar(6))

Insert into test (id,name,Parent_ID,Generation,Gender)

select 1,'John',null,'x','male'

union

select 2,'Jill',1,'y','Female'

union

select 3,'Andy',2,'z','male'

union

select 4,'Ralph',2,'z','male'

union

select 5,'Lisa',null,'x','Female'

union

select 6,'Steve',5,'y','male'

union

select 7,'Sean',6,'y','male'

union

select 8,'Sarah',6,'y','Female'

union

select 9,'Emily',7,'z','Female'

union

select 10,'Matt',7,'z','male'

Drop table test

*/

select * from Test

If input is lisa

Then output should be:

 

-------------

Answer :

Declare @name varchar(50)='Lisa'

;with cte

as

(

Select id,name,Parent_id,generation,gender from test where name =@name and generation='x'

Union All

Select t.id, t.name,t.Parent_id,t.generation,t.gender from test t

join cte c on c. id=t.parent_id

)

,condition

as

(

select Parent_id,COUNT(*) cnt  from cte Where generation='z' Group by parent_id having COUNT(*)>1

)

,sibling

as

(

select parent_id,Count(gender) cntg from (select distinct c.parent_id,c.gender from cte c join condition co on co.parent_id=c.parent_id) a group by parent_id having count(gender)>1

)

select c.name from cte c join sibling s on c.parent_id=s.parent_id