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