How to use case when condition in pivot query?

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

i have two query one is Pivot and another one is case when ,now i want to merge them ..plz guide

What I have tried:

SELECT DISTINCT ReportingDate,
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 ]
     INTO #Dates
FROM EmployeesAttendance
ORDER BY ReportingDate 

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 query

EXEC(@qry)

Posted 18-Jan-19 7:30am

Member 12314309

Add a Solution

Comments

CHill60 20-Jan-19 16:00pm

Provide sample data for the tables you have used and show us the results that you expect from the "merge"

Member 12314309 20-Jan-19 21:49pm

here is my employeeattendance table

CREATE TABLE [dbo].[EmployeesAttendance](
[AttIDS] [int] IDENTITY(1,1) NOT NULL,
[EmpID] [int] NULL,
[ReportingDate] [date] NULL,
[Date] [date] NULL,
[INTIME] [datetime] NULL,
[OUTTIME] [datetime] NULL,
[Hours] [int] NULL,
[Days] [int] NULL,
[OT] [int] NULL,
[OTAmount] [int] NULL,
[Time] [time](7) NULL,
[Late] [int] NULL,
[Status] [varchar](50) NULL,

here is my employee leave table

CREATE TABLE [dbo].[EmpApplication](
[AppNo] [int] IDENTITY(1,1) NOT NULL,
[EmpID] [int] NULL,
[ApplyingDate] [date] NULL,
[LeavFrom] [date] NULL,
[LeavTo] [date] NULL,
[leavDay] [int] NULL,
[LeaveTypeId] [int] NULL,

here is my attendance register pivot table to display cols into rows from Employee attendance table

ALTER Procedure [dbo].[Pivot_Attendance]
@StartDate Date,
@Enddate Date

As
Begin

SELECT DISTINCT ReportingDate INTO #Dates
FROM EmployeesAttendance
WHERE (ReportingDate BETWEEN @StartDate AND @Enddate)
ORDER BY ReportingDate

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

here is my final output which i want in pivot table
i want this output
Date (will be come as per two date parameter)

Date 1,2,3,4,5,6 TotalDay Absent Present leave
emp 1 p,p,p,p,A,A 6 2 4 0
emp 2 L,P,P,A,L,P 6 1 3 2

please guide thanks... @CHILL60

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)