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.
So now we need to find insights from this table and process that data and create monthly attendance report.
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
