Click here to Skip to main content
15,918,706 members

Comments by Member 12314309 (Top 44 by date)

Member 12314309 12-Apr-19 15:34pm View    
SQL Server 2008 from Microsoft
Member 12314309 12-Apr-19 15:24pm View    
sql query
Member 12314309 20-Jan-19 21:49pm View    
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
Member 12314309 20-Jan-19 21:36pm View    
yes have a question ,i have created pivot table for attendance table in which Present and absent are coming ,but now i want to mentioned leave type in a attendance register ,leave application cols(ApplyingDate,Startdate to Enddate,Noofday,leave type(pl,sl,cl)), now i want to mentioned these type in attendance register ,according to leave applied date (startDate to enddate) ..here is my pivot table query which is not join with attendance leaves,so how it will join with attendance register


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


-- Building the query with dynamic dates
--DECLARE @qry NVARCHAR(4000)
--SET @qry =
--'SELECT * FROM
--(SELECT EmpID, Status , ReportingDate
--FROM EmployeesAttendance)emp
--PIVOT (MAX(Status) FOR ReportingDate IN (' + @cols + ')) AS stat'

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)

END
Member 12314309 20-Jan-19 21:30pm View    
i have created already Employeetime table in which intime and outtime are coming and i also created employee leave table in which leave type(PL,SL,CL),Dayfrom to DateTo ,NoofDays are define ,now how will i mentioned leave status in attendance register ,,,i created pivot table for attendance register in which absent and Present is mentioned,now how can i mentioned leave type in employee attendance register?



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


-- Building the query with dynamic dates
--DECLARE @qry NVARCHAR(4000)
--SET @qry =
--'SELECT * FROM
--(SELECT EmpID, Status , ReportingDate
--FROM EmployeesAttendance)emp
--PIVOT (MAX(Status) FOR ReportingDate IN (' + @cols + ')) AS stat'

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)

END