By using this query I can get my desired output
but I cannot sort it datewise using workdate
I have tried this solution How to use DISTINCT and ORDER BY in same SELECT statement? but won't work.
DECLARE @sql_ nvarchar(max)
SELECT @sql_ = N'drop table #tempOT'
EXEC sp_executesql @sql_
select EmployeeCode,GivenDesignation,WorkDate,Duration
INTO #tempOT
from
(
SELECT A.* FROM
(SELECT E.EmployeeCode, E.EmployeeName, REPLACE(CONVERT(VARCHAR(11), E.DOJ, 113), ' ', '-') DOJ,
D.UserName Designation, U.UserName Unit, Dv.UserName Division, Dp.UserName Department,
S.UserName Section, SB.UserName SubSection, L.UserName Line
,CONVERT(VARCHAR(5), ARIN.OutTime-ARIN.InTime, 108) Duration
,ARIN.WorkDate,ARIN.InTime, ARIN.OutTime, DD.UserName GivenDesignation
FROM dbo.EmployeeInformation E
left join AttdnProcessData ARIN on ARIN.EmpSystemID=e.SystemId
LEFT JOIN ORG.Unit U ON E.UnitID = U.Id
LEFT JOIN ORG.Division Dv ON E.DivisionID = Dv.Id
LEFT JOIN ORG.Department Dp ON E.DepartmentID = Dp.Id
LEFT JOIN ORG.Section S ON E.SectionID = S.Id
LEFT JOIN ORG.SubSection SB ON E.SubSectionID = SB.Id
LEFT JOIN ORG.Line L ON E.LineID = L.Id
LEFT JOIN HKP.Designation D ON E.DesignationSystemID = D.Id
LEFT JOIN HKP.Designation DD ON E.GivenDesignationId = DD.Id
WHERE E.PlantID = '20189' AND ARIN.WorkDate BETWEEN '01-Jul-2018' AND '30-Jul-2018' AND E.EmployeeStatus='Active' ) A
GROUP BY A.EmployeeCode, A.EmployeeName, A.DOJ, A.Designation, A.Unit, A.Division, A.Department,
A.Section, A.SubSection, A.Line, A.WorkDate, A.InTime, A.OutTime, A.GivenDesignation,A.Duration
) TT
DECLARE @sql nvarchar(max),
@col nvarchar(max)
SELECT @col = (
SELECT DISTINCT ','+QUOTENAME(WorkDate)
FROM #tempOT
FOR XML PATH ('')
)
SELECT @sql = N'
(SELECT *
FROM #tempOT
PIVOT (
MAX([Duration]) FOR [WorkDate] IN ('+STUFF(@col,1,1,'')+')
) as pvt)'
EXEC sp_executesql @sql
Here is the picture of the output of the above query--
