January 18, 2016 at 9:48 pm
Hi, I have a table with the following data:
---------------------------------------------------
EmpID | AttendanceDate | TimeIN | TimeOut
---------------------------------------------------
1 | 2016-01-19 |08:00 AM |05:00PM
2 | 2016-01-19 |08:00 AM |05:00PM
I have created a Pivot for the TimeIN, The pivot will show per date column:
--------------------------------------------------------------------
EmpID | 2016-01-19 | 2016-01-20 | 2016-01-21 |
1 | 08:00 AM
2 | 08:00 AM
I would like some help because I also want to show the TimeOut per date column, I think I need to add another column, so per day, there would be two columns one is for IN and one is for OUT with the date. My code is :
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)
select @cols = STUFF((SELECT ',' + QUOTENAME(convert(char(10), AttendanceDate, 120))
from AttendanceMovement
--where AttendanceDate='2016-01-01'
group by AttendanceDate
order by AttendanceDate asc
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = 'SELECT EmployeeID
,' + @cols + ' from
(
select EmployeeID,Sched1TimeIn
flag,AttendanceDate
from AttendanceMovement
) x
pivot
(
max(flag)
for AttendanceDate in (' + @cols + ')
) p '
execute(@query)
January 19, 2016 at 5:51 am
Not sure exactly what you are looking as you didn't post any expected outputs but try the below which adds a column for type (in or out) and uses union to select the data for Time_in and Time_out.
create table #AttendanceMovement
(EmpIdintnot null
,AttendanceDatedatenot null
,Time_Intimenot null
,Time_Outtimenot null
)
insert into #AttendanceMovement (EmpId,AttendanceDate,Time_In,Time_Out)
values(1,'2016-01-19', '08:00', '17:00')
,(2,'2016-01-19', '08:00', '17:00')
,(1,'2016-01-20', '08:00', '17:00')
,(2,'2016-01-21', '08:00', '17:00')
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)
select @cols = STUFF((SELECT ',' + QUOTENAME(convert(char(10), AttendanceDate, 120))
from #AttendanceMovement
--where AttendanceDate='2016-01-01'
group by AttendanceDate
order by AttendanceDate asc
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = 'SELECT EmpID
,AttType
,' + @cols + '
from
(
selectEmpID
,AttType = ''In''
,AttTime = Time_In
,AttendanceDate
from #AttendanceMovement
union all
selectEmpID
,AttType = ''Out''
,AttTime = Time_Out
,AttendanceDate
from #AttendanceMovement
) x
pivot
(
max(AttTime)
for AttendanceDate in (' + @cols + ')
) p
'
--select @query
execute(@query)
January 19, 2016 at 8:53 am
Here's the approach that I would take. Note that I changed the pivot to cross tabs. The reason behind this is that it will perform better as you only read the table once and avoid complications from a double pivot.
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX);
select @cols = (SELECT ' ,MAX( CASE WHEN AttendanceDate = ' + QUOTENAME(convert(char(8), AttendanceDate, 112), '''')
+ ' THEN TimeIn END) AS ' + QUOTENAME(convert(char(10), AttendanceDate, 120) + 'In') + CHAR( 10)
+' ,MAX( CASE WHEN AttendanceDate = ' + QUOTENAME(convert(char(8), AttendanceDate, 112), '''')
+ ' THEN TimeOut END) AS ' + QUOTENAME(convert(char(10), AttendanceDate, 120) + 'Out') + CHAR( 10)
from AttendanceMovement
--where AttendanceDate='2016-01-01'
group by AttendanceDate
order by AttendanceDate asc
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)') ;
SELECT @query = 'SELECT EmployeeID ' + CHAR(10)
+ @cols
+ 'FROM AttendanceMovement ' + CHAR(10)
+ 'GROUP BY EmployeeID';
PRINT @query;
EXEC(@query);
select @cols = STUFF((SELECT ',' + QUOTENAME(convert(char(10), AttendanceDate, 120))
from AttendanceMovement
--where AttendanceDate='2016-01-01'
group by AttendanceDate
order by AttendanceDate asc
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
January 19, 2016 at 8:55 pm
Thanks you for this, I will try this approach on my other reports, the above union all worked out for me. Thank you so much for the help.
January 20, 2016 at 10:10 am
Just an advice, this should perform better than the UNION ALL approach and give the same results.
set @query = 'SELECT EmployeeID
,AttType
,' + @cols + '
from
(
selectEmployeeID
,AttType
,AttTime
,AttendanceDate
from AttendanceMovement
CROSS APPLY (VALUES( ''In'', TimeIn),
( ''Out'', TimeOut)) x(AttType, AttTime)
) x
pivot
(
max(AttTime)
for AttendanceDate in (' + @cols + ')
) p
'
Find a detailed explanation in here: http://www.sqlservercentral.com/articles/CROSS+APPLY+VALUES+UNPIVOT/91234/
January 20, 2016 at 10:58 pm
Luis Cazares (1/20/2016)
Just an advice, this should perform better than the UNION ALL approach and give the same results.
set @query = 'SELECT EmployeeID
,AttType
,' + @cols + '
from
(
selectEmployeeID
,AttType
,AttTime
,AttendanceDate
from AttendanceMovement
CROSS APPLY (VALUES( ''In'', TimeIn),
( ''Out'', TimeOut)) x(AttType, AttTime)
) x
pivot
(
max(AttTime)
for AttendanceDate in (' + @cols + ')
) p
'
Find a detailed explanation in here: http://www.sqlservercentral.com/articles/CROSS+APPLY+VALUES+UNPIVOT/91234/
Thank you for this, Yes, The performance will be a bit of a problem because i will be deploying it on a local machine with SQL express. I will be using the Cross Apply as per your advice. Thank you so much!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply