January 8, 2019 at 7:33 pm
I have three table one is EmployeeDetails,Second is EmployeeMachine and Third is EmployeeAttendance Table, I have created store Procedure to calculate Hours,Day,OT,OTAmount and late from INTIME and OUTTIME of AttendanceMachine table and inserting the result of Store Procedure into EmployeeAttendance table,Now i want to use store procedure to update just INTIME and OUTTIMEin EmployeeAttendance and result of column values calculate as below mentioned SP.Now how Update CTE SP will be created to updating INTIME and OUTTIME..please guide thanks....
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
from cte Cross Apply Dates d
Left Join cte2 ON cte2.EmpId= cte.EmpID AND cte2.Date=d.ReportingDate
order by cte.EmpID asc
i have three table one is EmployeeDetails ,Second is AttendanceMachine and third is EmployeeAttendance ,I have made a Store Procedure of CTE to calculate Hours,Day,OT,OTAmount,Late from intime and outtime of employee from attendanceMachine Table after that the result of Store Procedure i insert into EmployeeAttendance Table ,where i want that If EMployee INTIME and OUTIME does not available then I update INTIME and OUTTIME in EmployeeAttendance table,so in this regards need your help to modify my Store Procedure into Update Procedure for EmployeeAttendance table..
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
from cte Cross Apply Dates d
Left Join cte2 ON cte2.EmpId= cte.EmpID AND cte2.Date=d.ReportingDate
order by cte.EmpID asc
January 9, 2019 at 9:31 am
without and ddl, sample data and expected results, it will be hard for anyone to help you.
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
January 10, 2019 at 12:51 am
Mike01 - Wednesday, January 9, 2019 9:31 AMwithout and ddl, sample data and expected results, it will be hard for anyone to help you.
I tried Below but not getting result...Please help me out..
Update EmployeesAttendance
set EmployeesAttendance.INTIME=@INTime,
EmployeesAttendance.OUTTIME=@OUTTIME
where EmployeesAttendance.AttdID=EmployeesAttendance.AttdID AND
Case When (DATEDIFF(Hour, INTIME,OUTTIME)) = Hours
CASE WHEN EmployeesAttendance.Hours >= 8 THEN 1
WHEN EmployeesAttendance.Hours = 0 THEN 0
WHEN EmployeesAttendance.Hours >= 6 THEN 0.5 Day,
CASE WHEN EmployeesAttendance.Hours > EmployeeDetails.Dhour then
EmployeesAttendance .Hours - EmployeesAttendance .Dhour else 0 End as OT,
CASE when EmployeeDetails.OTEntitled = 'Yes'
AND EmployeesAttendance .Hours >= EmployeeDetails.Dhour
THEN (( EmployeesAttendance.Hours - 8) * 100) else 0 END AS OTAmount,
Convert(varchar(10), EmployeesAttendance .INTIME,108) as Time,
Case When Convert(Time, EmployeesAttendance .INTIME,108) > cte.LTime Then 1 else 0 end as Late
from EmployeeDetails
Left Join EmployeesAttendance ON EmployeeDetails.EmpId = EmployeesAttendance.EmpID
order by EmployeeDetails.EmpID asc
January 10, 2019 at 1:54 am
Asked and answered in another thread. https://www.sqlservercentral.com/Forums/2015957/How-to-create-Update-Store-Procedure-in-sql-between-two-table
Please in future stick to a single thread for a question
If you format your code well, these missing brackets errors are easy to spot.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply