January 25, 2019 at 7:52 pm
SELECT EmpID, ReportingDate, CASE WHEN [Days] IS null AND EXISTS (SELECT 1 FROM EmpApplication WHERE EmployeesAttendance.Empid = EmpApplication.Empid AND (ReportingDate >= LeavFrom AND ReportingDate <= LeavTo)) THEN 'CL' WHEN [Days] IS null AND EXISTS (SELECT 1 FROM Holidays WHERE ReportingDate = HolidayDate) THEN 'H' WHEN [Days] IS null THEN 'A' WHEN [Days] = 1 THEN 'P' END AS StatusFROM EmployeesAttendance
Please help
i want this output
| Date | 01-1-2019 | 02-1-2019 | 03-1-2019 | 04-1-2019 | 05-1-2019 | 06-1-2019 | 07-1-2019 | 08-1-2019 | 09-1-2019 |
| Emp1 | P | P | CL | CL | H | H | p | p | p |
| Emp2 | CL | SL | P | P | H | H | P | p | p |
| Emp3 | A | A | A | P | H | H | P | P | P |
January 25, 2019 at 8:34 pm
Can you post a CREATE TABLE script and the INSERT scripts for this?
And maybe fix up your SQL so we can read it?
January 25, 2019 at 11:17 pm
You need to understand deeply
this stored procedure is separating INTIME and OUTTIME from column to rows.
ALTER PROCEDURE [dbo].[GetMachineAttendanceFinal5]
AS
BEGIN
SET NOCOUNT ON;
;WITH Dates (ReportingDate)
AS (
SELECT CONVERT(date, '2018-11-26 00:00:00.000', 120) AS ReportingDate
UNION ALL
SELECT DATEADD(day, 1, d.ReportingDate)
FROM Dates d
WHERE d.ReportingDate < CONVERT(date, '2018-12-25 00:00:00.000', 120))
,CTE As
(
select EmployeeDetails.EmpID,EmployeeDetails.EmpName,EmployeeDetails.OTEntitled,EmployeeDetails.Empcur,EmployeeDetails.Dhour,EmployeeDetails.LTime from EmployeeDetails where Empcur='Join'
)
,CTE4 As
(
Select MachineAttendance.EmpID,MachineAttendance.MDate,MachineAttendance.INOUT from MachineAttendance
)
,cte1 AS
(
SELECT CTE4.EmpID, CAST(CTE4.MDate as Date) AS [Date],
CASE WHEN CTE4.INOUT = 1 THEN CTE4.MDate END AS INOUT_INTIME,
CASE WHEN CTE4.INOUT = 2 THEN CTE4.MDate END AS INOUT_OUTTIME
From
CTE4
),
cte2
as
(
select cte1.EmpID, Date, MAX(INOUT_INTIME) AS INTIME,
MAX(INOUT_OUTTIME) AS OUTTIME
, DATEDIFF(Hour, MAX(INOUT_INTIME), MAX(INOUT_OUTTIME)) as [Hours]
FROM CTE1
GROUP BY EmpID, [Date]
)
select cte.EmpID,d.ReportingDate,cte2.Date,cte2.INTIME, cte2.OUTTIME, cte2.[Hours]
, CASE WHEN cte2.[Hours] >= 8 THEN 1
WHEN cte2.[Hours] = 0 THEN 0
WHEN cte2.[Hours] >= 6 THEN 0.5 END AS [Day],
CASE WHEN cte2.[Hours] > CTE.Dhour then cte2.[Hours] - CTE.Dhour else 0 End as OT,
CASE when
cte.OTEntitled = 'Yes' AND cte2.[Hours] >= CTE.Dhour
THEN (( cte2.[Hours] - 8) * 100) else 0 END AS OTAMount,
Convert(varchar(10), cte2.INTIME,108) as [Time],
Case When Convert(Time, cte2.INTIME,108) > cte.LTime Then 1 else 0 end as Late ,
Case when cte2.Hours >= cte.Dhour then 'P' when cte2.Hours <= 6 then 'HD' else 'A' end as Status
from cte Cross Apply Dates d
Left Join cte2 ON cte2.EmpId= cte.EmpID AND cte2.Date=d.ReportingDate
order by cte.EmpID asc END
Then i transfer this data into Employeeattendance Table by using
ALTER PROCEDURE [dbo].[TransferAttendance]
-- Add the parameters for the stored procedure here
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
INSERT INTO EmployeesAttendance
EXEC GetMachineAttendanceFinal5
END
Then i made two more table one is EmpApplicaiton
REATE 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,
CONSTRAINT [PK_EmpAppication] PRIMARY KEY CLUSTERED
(
[AppNo] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[EmpApplication] WITH CHECK ADD CONSTRAINT [FK_EmpAppication_EmployeeDetails] FOREIGN KEY([EmpID])
REFERENCES [dbo].[EmployeeDetails] ([EmpID])
GO
ALTER TABLE [dbo].[EmpApplication] CHECK CONSTRAINT [FK_EmpAppication_EmployeeDetails]
GO
ALTER TABLE [dbo].[EmpApplication] WITH CHECK ADD CONSTRAINT [FK_EmpApplication_tbl_LeaveType] FOREIGN KEY([LeaveTypeId])
REFERENCES [dbo].[tbl_LeaveType] ([LeaveTypeId])
GO
ALTER TABLE [dbo].[EmpApplication] CHECK CONSTRAINT [FK_EmpApplication_tbl_LeaveType]
GO
and Second one is Holiday table
CREATE TABLE [dbo].[Holidays](
[HolidayDate] [date] NOT NULL
) ON [PRIMARY]
GO
If you need more infor please tell me thanks...
January 26, 2019 at 1:57 am
akhterhussain80 - Friday, January 25, 2019 7:52 PMSELECT EmpID, ReportingDate, CASE WHEN [Days] IS null AND EXISTS (SELECT 1 FROM EmpApplication WHERE EmployeesAttendance.Empid = EmpApplication.Empid AND (ReportingDate >= LeavFrom AND ReportingDate <= LeavTo)) THEN 'CL' WHEN [Days] IS null AND EXISTS (SELECT 1 FROM Holidays WHERE ReportingDate = HolidayDate) THEN 'H' WHEN [Days] IS null THEN 'A' WHEN [Days] = 1 THEN 'P' END AS StatusFROM EmployeesAttendancePlease help
i want this output
Date 01-1-2019 02-1-2019 03-1-2019 04-1-2019 05-1-2019 06-1-2019 07-1-2019 08-1-2019 09-1-2019 Emp1 P P CL CL H H p p p Emp2 CL SL P P H H P p p Emp3 A A A P H H P P P
Do you have to do this in T-SQL? You could do this in SSRS with a matrix with no code at all. Otherwise, maybe read Jeff Moden's article on Crosstabs.http://www.sqlservercentral.com/articles/T-SQL/63681/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply