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