January 18, 2019 at 7:07 pm
below mentioned case when statement now i want to use it in pivot table....i have three column in EmployeesAttendance Empid,Status,ReportingDate
SELECT SUM(CASE WHEN status = 'P' THEN 1
WHEN status = 'HD' THEN 0.5 WHEN status = 'A' THEN 0 END) AS [T.P],
SUM(CASE WHEN status = 'A' THEN 1 WHEN status = 'HD' THEN 0.5 END) AS [A],
SUM(CASE WHEN status = 'P' THEN 1
WHEN status = 'HD' THEN 1 WHEN status = 'A' THEN 1 END) AS [TDay ]
FROM EmployeesAttendance
--WHERE (ReportingDate BETWEEN @StartDate AND @Enddate)
GROUP BY EmpID
--Pivot Table
SELECT DISTINCT ReportingDate INTO #Dates FROM EmployeesAttendance ORDER BY Reportin Date DECLARE @cols NVARCHAR(4000)SELECT @cols = COALESCE(@cols + ',[' + CONVERT(varchar, DATEPART(DAY, ReportingDate), 112) + ']','[' + CONVERT(varchar,DATEPART(DAY, ReportingDate), 112) + ']')FROM #Dates ORDER BY ReportingDate DECLARE @qry NVARCHAR(4000) =
N'SELECT *
FROM (SElECT EmployeeDetails.EmpID,EmployeeDetails.EmpName,EmployeesAttendance.Status,
DATEPART(DAY, EmployeesAttendance.ReportingDate)as DDate
FROM EmployeesAttendance Inner Join EmployeeDetails on EmployeesAttendance.EmpID=EmployeeDetails.Empid )
emp
PIVOT (MAX(Status) FOR DDate IN (' + @cols + ')) AS stat '-- Executing the queryEXEC(@qry)
January 18, 2019 at 9:30 pm
How about some data? CREATE TABLE and INSERT scripts?
Why not do the CASE statement in an inner query, and then pivot that result?
January 18, 2019 at 10:53 pm
pietlinden - Friday, January 18, 2019 9:30 PMHow about some data? CREATE TABLE and INSERT scripts?
Why not do the CASE statement in an inner query, and then pivot that result?
i have data in a table of employeeAttendance in which i am converting column into row then making sum of each employee id in case when,but i do not know that how this query will merge with pivot table query,so i request you to do little help regarding this,
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply