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

Encrypt And Decrypt Data Using Certificate In SQL Server

CREATE DATABASE Dbavimal
GO

USE Dbavimal
GO

--Create MasterKey
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'DBAVimal!';
GO

-- Create Certificate
CREATE CERTIFICATE [EncryptionCert]
 WITH SUBJECT = 'DBAEncryption'
GO

-- Symmetric Key
CREATE SYMMETRIC KEY SymmetricDBAVimalKey
 WITH ALGORITHM = AES_192 ENCRYPTION BY CERTIFICATE [EncryptionCert]
GO

--Use Symmetric Key
OPEN SYMMETRIC KEY SymmetricDBAVimalKey DECRYPTION BY CERTIFICATE [EncryptionCert]
GO

-----------------------------
CREATE FUNCTION [dbo].[fn_Encrypt] (@Data VARCHAR(max))
RETURNS VARBINARY(256)
AS
BEGIN
 DECLARE @Result VARBINARY(256)

 SET @Result = EncryptByKey(Key_GUID('SymmetricDBAVimalKey'), @Data)

 RETURN @Result
END
GO

-------------------------------
CREATE FUNCTION [dbo].[fn_Decrypt] (@Data VARCHAR(max))
RETURNS VARCHAR(max)
AS
BEGIN
 DECLARE @Result VARCHAR(max)

 SET @Result = DecryptByKey(@Data)

 RETURN @Result
END
GO

-----------------------------------
--Test the result (Same way you can store your data on tables)
DECLARE @Str VARCHAR(500) = 'Hello'

PRINT '====Original Data============'
PRINT @Str
PRINT '============================='

DECLARE @EncryptedData VARCHAR(256)
DECLARE @DecryptedData VARCHAR(256)

SET @EncryptedData = [dbo].[fn_Encrypt](@Str)

PRINT '====Encrypted Data==========='
PRINT @EncryptedData
PRINT '============================='

SET @DecryptedData = [dbo].[fn_Decrypt](@EncryptedData)

PRINT '====Data After Decryption===='
PRINT @DecryptedData
PRINT '============================='

USE master
GO

DROP DATABASE Dbavimal

Important Links for Sql Server scripts

Sql Server Missing Index Script
Click Here

Sql Server Unused Index Script
Click Here

 Find Unused Indexes of Current Database
Click Here

Identify Numbers of Non Clustered Index on Tables for Entire Database
Click Here

Find a column in SQL database tables

SELECT s.NAME AS ColumnName
 ,sh.NAME + '.' + o.NAME AS ObjectName
 ,o.type_desc AS ObjectType
 ,CASE 
  WHEN t.NAME IN (
    'char'
    ,'varchar'
    )
   THEN t.NAME + '(' + CASE 
     WHEN s.max_length < 0
      THEN 'MAX'
     ELSE CONVERT(VARCHAR(10), s.max_length)
     END + ')'
  WHEN t.NAME IN (
    'nvarchar'
    ,'nchar'
    )
   THEN t.NAME + '(' + CASE 
     WHEN s.max_length < 0
      THEN 'MAX'
     ELSE CONVERT(VARCHAR(10), s.max_length / 2)
     END + ')'
  WHEN t.NAME IN ('numeric')
   THEN t.NAME + '(' + CONVERT(VARCHAR(10), s.precision) + ',' + CONVERT(VARCHAR(10), s.scale) + ')'
  ELSE t.NAME
  END AS DataType
 ,CASE 
  WHEN s.is_nullable = 1
   THEN 'NULL'
  ELSE 'NOT NULL'
  END AS Nullable
 ,CASE 
  WHEN ic.column_id IS NULL
   THEN ''
  ELSE ' identity(' + ISNULL(CONVERT(VARCHAR(10), ic.seed_value), '') + ',' + ISNULL(CONVERT(VARCHAR(10), ic.increment_value), '') + ')=' + ISNULL(CONVERT(VARCHAR(10), ic.last_value), 'null')
  END + CASE 
  WHEN sc.column_id IS NULL
   THEN ''
  ELSE ' computed(' + ISNULL(sc.DEFINITION, '') + ')'
  END + CASE 
  WHEN cc.object_id IS NULL
   THEN ''
  ELSE ' check(' + ISNULL(cc.DEFINITION, '') + ')'
  END AS MiscInfo
 ,CASE 
  WHEN t.NAME IN (
    'char'
    ,'varchar'
    )
   THEN CASE 
     WHEN s.max_length < 0
      THEN 0
     ELSE CONVERT(VARCHAR(10), s.max_length)
     END
  WHEN t.NAME IN (
    'nvarchar'
    ,'nchar'
    )
   THEN CASE 
     WHEN s.max_length < 0
      THEN 0
     ELSE CONVERT(VARCHAR(10), s.max_length / 2)
     END
  END val
INTO #temp
FROM sys.columns s
INNER JOIN sys.types t ON s.system_type_id = t.user_type_id
 AND t.is_user_defined = 0
INNER JOIN sys.objects o ON s.object_id = o.object_id
INNER JOIN sys.schemas sh ON o.schema_id = sh.schema_id
LEFT JOIN sys.identity_columns ic ON s.object_id = ic.object_id
 AND s.column_id = ic.column_id
LEFT JOIN sys.computed_columns sc ON s.object_id = sc.object_id
 AND s.column_id = sc.column_id
LEFT JOIN sys.check_constraints cc ON s.object_id = cc.parent_object_id
 AND s.column_id = cc.parent_column_id
WHERE --t.name in ('nvarchar','nchar','char','varchar')
 S.NAME LIKE 'attendance%' --<--Write your columnname here
ORDER BY sh.NAME + '.' + o.NAME
 ,s.column_id

SELECT *
FROM #temp --where val>500

DROP TABLE #temp

Find List of Year, Month,Days On The Basis Of Month Between Two Dates

 Declare @StartDate date= '03/01/2015'
 Declare @EndDate date= '06/05/2015'
 
 select  
  year(dt) [Year], month(dt) [Month],  count(*) Days
  from 
  (
   select top (datediff(d, @StartDate, @EndDate)) dateadd(d,  row_number() over (order by (select null)), @StartDate) dt
   from sys.columns
  ) q
  group by year(dt), month(dt)
  order by [Year], [Month]

Drop And Recreate all indexes on all tables of a database :

How to use :

First execute both scripts (on SSMS) on your database and save the result. It will generate drop index  and create index for all tables on database.

After doing this, please execute drop indexes result and then execute create index result.

Please verify the result.

--First Script----------

---------Drop indexes start

DECLARE @SchemaName VARCHAR(256)
DECLARE @TableName VARCHAR(256)
DECLARE @IndexName VARCHAR(256)
DECLARE @TSQLDropIndex VARCHAR(MAX)

DECLARE CursorIndexes CURSOR
FOR
SELECT schema_name(t.schema_id)
 , t.NAME
 , i.NAME
FROM sys.indexes i
INNER JOIN sys.tables t ON t.object_id = i.object_id
WHERE i.type > 0
 AND t.is_ms_shipped = 0
 AND t.NAME <> 'sysdiagrams'
 AND (
  is_primary_key = 0
  AND is_unique_constraint = 0
  )

OPEN CursorIndexes

FETCH NEXT
FROM CursorIndexes
INTO @SchemaName
 , @TableName
 , @IndexName

WHILE @@fetch_status = 0
BEGIN
 SET @TSQLDropIndex = 'DROP INDEX ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) + '.' + QUOTENAME(@IndexName)

 PRINT @TSQLDropIndex

 FETCH NEXT
 FROM CursorIndexes
 INTO @SchemaName
  , @TableName
  , @IndexName
END

CLOSE CursorIndexes

DEALLOCATE CursorIndexes
--------------------Drop indexes end--------------

PRINT '  '
PRINT '-------- DROP AND CREATE INDEXES BY VIMAL------------------'
PRINT '  '
GO

--Second Script----------

--------------------Create indexes start-----------
DECLARE @SchemaName VARCHAR(100)
DECLARE @TableName VARCHAR(256)
DECLARE @IndexName VARCHAR(256)
DECLARE @ColumnName VARCHAR(100)
DECLARE @is_unique VARCHAR(100)
DECLARE @IndexTypeDesc VARCHAR(100)
DECLARE @FileGroupName VARCHAR(100)
DECLARE @is_disabled VARCHAR(100)
DECLARE @IndexOptions VARCHAR(max)
DECLARE @IndexColumnId INT
DECLARE @IsDescendingKey INT
DECLARE @IsIncludedColumn INT
DECLARE @TSQLScripCreationIndex VARCHAR(max)
DECLARE @TSQLScripDisableIndex VARCHAR(max)

DECLARE CursorIndex CURSOR
FOR
SELECT schema_name(t.schema_id) [schema_name]
 , t.NAME
 , ix.NAME
 , CASE 
  WHEN ix.is_unique = 1
   THEN 'UNIQUE '
  ELSE ''
  END
 , ix.type_desc
 , CASE 
  WHEN ix.is_padded = 1
   THEN 'PAD_INDEX = ON, '
  ELSE 'PAD_INDEX = OFF, '
  END + CASE 
  WHEN ix.allow_page_locks = 1
   THEN 'ALLOW_PAGE_LOCKS = ON, '
  ELSE 'ALLOW_PAGE_LOCKS = OFF, '
  END + CASE 
  WHEN ix.allow_row_locks = 1
   THEN 'ALLOW_ROW_LOCKS = ON, '
  ELSE 'ALLOW_ROW_LOCKS = OFF, '
  END + CASE 
  WHEN INDEXPROPERTY(t.object_id, ix.NAME, 'IsStatistics') = 1
   THEN 'STATISTICS_NORECOMPUTE = ON, '
  ELSE 'STATISTICS_NORECOMPUTE = OFF, '
  END + CASE 
  WHEN ix.ignore_dup_key = 1
   THEN 'IGNORE_DUP_KEY = ON, '
  ELSE 'IGNORE_DUP_KEY = OFF, '
  END + 'SORT_IN_TEMPDB = OFF, FILLFACTOR =' + CASE 
  WHEN ix.fill_factor = 0
   THEN '100'
  ELSE CAST(ix.fill_factor AS VARCHAR(3))
  END AS IndexOptions
 , ix.is_disabled
 , FILEGROUP_NAME(ix.data_space_id) FileGroupName
FROM sys.tables t
INNER JOIN sys.indexes ix ON t.object_id = ix.object_id
WHERE ix.type > 0
 AND ix.is_primary_key = 0
 AND ix.is_unique_constraint = 0 --and schema_name(tb.schema_id)= @SchemaName and tb.name=@TableName
 AND t.is_ms_shipped = 0
 AND t.NAME <> 'sysdiagrams'
ORDER BY schema_name(t.schema_id)
 , t.NAME
 , ix.NAME

OPEN CursorIndex

FETCH NEXT
FROM CursorIndex
INTO @SchemaName
 , @TableName
 , @IndexName
 , @is_unique
 , @IndexTypeDesc
 , @IndexOptions
 , @is_disabled
 , @FileGroupName

WHILE (@@fetch_status = 0)
BEGIN
 DECLARE @IndexColumns VARCHAR(max)
 DECLARE @IncludedColumns VARCHAR(max)

 SET @IndexColumns = ''
 SET @IncludedColumns = ''

 DECLARE CursorIndexColumn CURSOR
 FOR
 SELECT col.NAME
  , ixc.is_descending_key
  , ixc.is_included_column
 FROM sys.tables tb
 INNER JOIN sys.indexes ix ON tb.object_id = ix.object_id
 INNER JOIN sys.index_columns ixc ON ix.object_id = ixc.object_id
  AND ix.index_id = ixc.index_id
 INNER JOIN sys.columns col ON ixc.object_id = col.object_id
  AND ixc.column_id = col.column_id
 WHERE ix.type > 0
  AND (
   ix.is_primary_key = 0
   OR ix.is_unique_constraint = 0
   )
  AND schema_name(tb.schema_id) = @SchemaName
  AND tb.NAME = @TableName
  AND ix.NAME = @IndexName
 ORDER BY ixc.index_column_id

 OPEN CursorIndexColumn

 FETCH NEXT
 FROM CursorIndexColumn
 INTO @ColumnName
  , @IsDescendingKey
  , @IsIncludedColumn

 WHILE (@@fetch_status = 0)
 BEGIN
  IF @IsIncludedColumn = 0
   SET @IndexColumns = @IndexColumns + QUOTENAME(@ColumnName) + CASE 
     WHEN @IsDescendingKey = 1
      THEN ' DESC, '
     ELSE ' ASC, '
     END
  ELSE
   SET @IncludedColumns = @IncludedColumns + QUOTENAME(@ColumnName) + ', '

  FETCH NEXT
  FROM CursorIndexColumn
  INTO @ColumnName
   , @IsDescendingKey
   , @IsIncludedColumn
 END

 CLOSE CursorIndexColumn

 DEALLOCATE CursorIndexColumn

 SET @IndexColumns = substring(@IndexColumns, 1, len(@IndexColumns) - 1)
 SET @IncludedColumns = CASE 
   WHEN len(@IncludedColumns) > 0
    THEN substring(@IncludedColumns, 1, len(@IncludedColumns) - 1)
   ELSE ''
   END
 --print @IndexColumns
 --print @IncludedColumns
 SET @TSQLScripCreationIndex = ''
 SET @TSQLScripDisableIndex = ''
 SET @TSQLScripCreationIndex = 'CREATE ' + @is_unique + @IndexTypeDesc + ' INDEX ' + QUOTENAME(@IndexName) + ' ON ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) + '(' + @IndexColumns + ') ' + CASE 
   WHEN len(@IncludedColumns) > 0
    THEN CHAR(13) + 'INCLUDE (' + @IncludedColumns + ')'
   ELSE ''
   END + CHAR(13) + 'WITH (' + @IndexOptions + ') ON ' + QUOTENAME(@FileGroupName) + ';'

 IF @is_disabled = 1
  SET @TSQLScripDisableIndex = CHAR(13) + 'ALTER INDEX ' + QUOTENAME(@IndexName) + ' ON ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) + ' DISABLE;' + CHAR(13)

 PRINT @TSQLScripCreationIndex
 PRINT @TSQLScripDisableIndex

 FETCH NEXT
 FROM CursorIndex
 INTO @SchemaName
  , @TableName
  , @IndexName
  , @is_unique
  , @IndexTypeDesc
  , @IndexOptions
  , @is_disabled
  , @FileGroupName
END

CLOSE CursorIndex

DEALLOCATE CursorIndex
 ----------------------Create indexes end--------

Comma separated values from sql table :

--Create temp table & Insert data-----
Create table #temp(id int, tech nvarchar(10))
Insert into #temp(id,tech) values(12, 'c#'),(12,'java'),(13,'sql'),(13,'Oracle'),(13,'Mysql'),(15,'hadoop')
Select * from #temp
--------------------------------------
--Process data------------------------
;WITH cte AS
(
SELECT DISTINCT id 
FROM #temp
)
SELECT Id, STUFF((SELECT ', ' + CAST(t2.tech AS NVARCHAR(MAX))
     FROM #temp t2 WHERE t1.ID = t2.ID
     FOR XML PATH('')),1,1,'') skills
into #temp1
FROM cte t1
--Getting Result---------------------
Select * from #temp1
--Drop temp tables-------------------
Drop table #temp
Drop table #temp1
-------------------------------------

sp_MSforeachtable Stored Procedure

sp_MSforeachtable stored procedure provided by Microsoft, is used for smooth processing on all tables of a single database. So why to use complex query when we have already that procedure.

How and when to use sp_MSFooreachtable :

Parameters

sp_MSforeachtable @command1, @replacechar, @command2,  @command3, @whereand, @precommand, @postcommand

• @command1 nvarchar(2000) – is the first command to be executed
• @replacechar nchar(1) – is a character in the command string that will be replaced with the table name being processed (default character is “?”)
• @command2 nvarchar(2000)-  additional command that can be run for each table. It runs after @ command1
• @command2 nvarchar(2000)-  another additional command that can be run for each table. It runs after @ comman2
• @whereand nvarchar(2000)- you can specify the where clause in this parameter
• @precommand nvarchar(2000) -  specifies a command to be run prior to processing any table
• @postcommand nvarchar(2000)- command to be executed  after all commands have been processed

Eg 1 : Retriving all tables starting with 'c' and their row count CREATE TABLE  #rowcount (tablename varchar(128), cnt int) EXEC sp_MSforeachtable  @command1 = 'Insert into #rowcount select ''?'',               count(*) from ?',  @whereand = 'and o.name like ''p%''',  @postcommand = 'SELECT * from #rowcount' DROP TABLE  #rowcount Eg 2:  Disabling all constraints on all tables

EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'

Eg 3:  Enabling all constraints on all tables

EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'

Eg 4:  Deleting rows from all tables

EXEC sp_MSForEachTable 'DELETE FROM ?'

Eg 5:  Reseed identity column

If any table doesn't contain identity column, it raise error and as a quality of stored procedure it will move on EXEC sp_MSforeachtable 'DBCC CHECKIDENT ( ''?'', RESEED, 0)' Eg 6:   Rebuild clustered index/all non clusted indexes on all the tables EXEC sp_MSforeachtable 'ALTER INDEX ALL ON ? REBUILD WITH (ONLINE=OFF)'