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

 

Splitting Data Query

DECLARE @STR VARCHAR(100) = 'Hi how are you doing? I am good.'
DECLARE @dev VARCHAR(1) = ' '

SELECT Split.a.value('.', 'VARCHAR(100)') AS E
FROM (
 SELECT Cast('<M> ' + Replace(Replace(@str, @dev, @dev + '</M><M>'), '.', '') + ' </M>' AS XML) AS yo
 ) a
CROSS APPLY yo.nodes('/M') AS SPLIT(a)