Error in pivot statement in sqlserver

Posted by Klbaiju under Sql Server on 3/24/2014 | Points: 10 | Views : 460 | Status : [Member] | Replies : 0
Hi,

following is working code

declare @dte as datetime ='2014-01-01'

--declare @tdate as datetime ='2013-07-01'

declare @StDt as Datetime = DATEADD(dd,-(DAY(GETDATE())-1),@dte)
declare @EnDt as datetime = DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@dte)+1,0))
Declare @Cnt as int = datepart(dd,@EnDt)
Declare @inc as int = 0
Create table #temp (Month_date datetime)
while @inc < @cnt
begin
insert into #temp
select DATEADD(dd, @inc, DATEADD(dd,-(DAY(@dte)-1),@dte))
set @inc = @inc + 1
end
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(empid)
from Baiju.dbo.Empmaster
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')

create table #temp1(EmpId numeric(18,0),workingday varchar(15), intime varchar(15),outime varchar(15),EmpName nvarchar(20),dstatus char(10),totaltime varchar(10))
insert into #temp1

select EmpId,CONVERT(VARCHAR(20), InTime,106 )as workingday,left(CONVERT(TIME,MIN(InTime),108),5)as intime,left(CONVERT(TIME,Max(outTime),108),5)as outtime ,EmpName,dstatus=
(CASE
WHEN CONVERT(TIME,MIN(InTime),108)>'08:35' and CONVERT(TIME,MIN(InTime),108) <'11:00'
THEN 'L'
WHEN CONVERT(TIME,MIN(InTime),108)>'11:00'
THEN 'halfday'
else 'right' end ),left(cast(CAST(DATEADD(MINUTE, SUM(DATEDIFF(MI, inTime, outTime))-45, 0) AS TIME) as varchar),5) TotalTime
FROM Baiju.dbo.HbaEmp GROUP BY EmpId , EmpName,CONVERT(VARCHAR(20), InTime,106 )


SET @Query='SELECT CONVERT(VARCHAR(20), Month_date,106 )as workingday ,'+ @cols +'
from (
select t.Month_date,b.empid,b.workingday,b.[totaltime]
from #temp t
left outer join #temp1 b on t.Month_date=b.workingday
) x
pivot
(
max(totaltime)
for empid in (' + @cols + ')
) p
order by Month_date'

exec(@query)
select * from #temp1
drop table #temp
drop table #temp1

following is the output

workingday 2500 2501 2502 2503 2504 2505 2506 2507
01 Jan 2014 09:15 08:55 NULL NULL NULL NULL NULL NULL
02 Jan 2014 10:15 09:55 NULL NULL NULL NULL NULL NULL
03 Jan 2014 NULL NULL NULL NULL NULL NULL NULL NULL
04 Jan 2014 NULL NULL NULL NULL NULL NULL NULL NULL

'

30 Jan 2014 NULL NULL NULL NULL NULL NULL NULL NULL
31 Jan 2014 NULL NULL NULL NULL NULL NULL NULL NULL

My requirement is i want to display name on column headers instead of id

just like

workingday Arun Kiran sarun aaa bbb eee yyyy xxxx




Responses

(No response found.)

Login to post response