0

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--

enter image description here

Abdullah Al Mamun
  • 392
  • 1
  • 4
  • 13
  • No need to do GROUP BY when no aggregate functions are involved. If you want distinct rows, do SELECT DISTINCT. – jarlh Sep 17 '18 at 09:44
  • In here I tried to use order by SELECT @col = ( SELECT DISTINCT ','+QUOTENAME(WorkDate) FROM #tempOT FOR XML PATH ('') ) and If I delete the Group By then another error occured **The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified. ** – Abdullah Al Mamun Sep 17 '18 at 09:54

0 Answers0