I need crosstab or pivot table By select Datetime.
Table filesTA
EmpNo ChkDate ChkIn
00001 2012-10-10 00:00:00.000 2012-10-10 07:22:00.000
00002 2012-10-10 00:00:00.000 2012-10-10 07:30:00.000
00001 2012-10-11 00:00:00.000 2012-10-11 07:13:00.000
00002 2012-10-11 00:00:00.000 2012-10-11 07:34:00.000
00001 2012-10-12 00:00:00.000 2012-10-12 07:54:00.000
00002 2012-10-12 00:00:00.000 2012-10-12 07:18:00.000
I have tried following
SELECT tf.EmpNo,tf.ChkDate,tf.ChkIn
FROM (SELECT EmpNo,ChkDate,ChkIn
,ROW_NUMBER() OVER(PARTITION BY EmpNo ORDER BY ChkDate) as tfNum
FROM filesTA) AS tf
PIVOT(MIN(ChkDate) FOR tfNum IN ('2012-10-10'))
WHERE tf.ChkDate Between '2012-10-10' and '2012-10-12'
But getting following error
Incorrect syntax near 'PIVOT'. You may need to set the compatibility
level of the current database to a higher value to enable this feature.
See help for the SET COMPATIBILITY_LEVEL option of ALTER DATABASE.
Desired Output:
EmpNo 10 11 12
00001 07:22 07:13 07:54
00002 07:30 07:34 07:18
I'm beginning learn pivot and crosstab. please help me to get my query working.
If you are not able to use the PIVOT
function, then you can use an aggregate function with a CASE
statement:
select empno,
max(case when datepart(d, chkdate) = 10
then convert(char(5), ChkIn, 108) end) [10],
max(case when datepart(d, chkdate) = 11
then convert(char(5), ChkIn, 108) end) [11],
max(case when datepart(d, chkdate) = 12
then convert(char(5), ChkIn, 108) end) [12]
from filesTA
where ChkDate Between '2012-10-10' and '2012-10-12'
group by empno
If you have access to PIVOT
, then your syntax will be:
select empno, [10], [11], [12]
from
(
select empno, datepart(d, chkdate) chkdate,
convert(char(5), ChkIn, 108) chkin
from filesTA
) src
pivot
(
max(chkin)
for chkdate in ([10], [11], [12])
) piv