Hi,
I have a stored procedure to update metrics for each employee in a table.
I need to update each record for each employee when the status in column CurrStat = 'In Progress' and StartDate (datetime column) is the last one for ActivityCategory = 'Process' or 'Non-Process'. It works, but, when an employee adds a new record for a different activity (not 'Process' or 'Non-Process') stored procedure should update previous one - with 'Process' or 'Non'Process' and 'In progress' status, but there is no update. I think, the issue here is with MAX(StartDate). Cloud you please help me to solve this puzzle?
ALTER PROCEDURE [dbo].[sp_Metrics_Upload] @ActivAcnon nvarchar(100) = 'Non-Process', @ActivAc nvarchar(100) = 'Process', @Away nvarchar(100) = 'Locked', @Startup nvarchar(100) = 'Startup', @Stat nvarchar(20) = 'In Progress', @NOW datetime = null, @LogDate date = null AS IF @LogDate IS NULL SET @LogDate = GETDATE() IF @NOW IS NULL SET @NOW = DATEADD(HOUR, 2, GETDATE()) BEGIN
UPDATE [dbo].[tbl_Main_Timer] SET Productivity = T4.Productivity, [Availability] = T4.Availabilit, Utilization = T4.Utilization, Efficiency = T4.Efficiency,
EfficiencyLoss = T4.EffLoss, NonProcess = T4.NonProcCount, Process = T4.ProcCount, TotalElapsed = T4.TotalElapsed, StartupElapsed = T4.StartupElaps FROM
(SELECT DISTINCT T3.Enumber, T3.Ename, T3.MRU, T3.Tower, T3.Market, FORMAT((SUM(((T3.ProcElaps) * T3.Avi) / T3.StartupElaps)),'p') as Productivity, FORMAT((T3.Avi), 'p') as Availabilit,
FORMAT((SUM((T3.ProcElaps) / T3.StartupElaps)), 'p') as Utilization, FORMAT((T3.Effi),'p') as Efficiency, FORMAT((T3.EffiLoss),'p') as EffLoss, T3.NonProcCount, T3.ProcCount, T3.StartupElaps,
T3.LogDate, T3.MstartDate, T3.TotalElapsed FROM
(SELECT DISTINCT T2.Enumber, T2.Ename, T2.MRU, T2.Tower, T2.Market, T2.NonProcCount, T2.ProcCount, T2.LogDate, T2.MstartDate, T2.NonProcElaps, T2.StartupElaps, SUM((T2.StartupElaps - (T2.NonProcElaps)) / T2.StartupElaps) as Avi,
SUM((T2.ProcElaps) / (T2.StartupElaps - (T2.NonProcElaps))) as Effi, T2.ProcElaps, SUM((T2.StartupElaps - (T2.ProcElaps + T2.NonProcElaps))/T2.StartupElaps) as EffiLoss, T2.TotalElapsed FROM
(SELECT DISTINCT T1.Enumber, T1.Ename, T1.MRU, T1.Tower, T1.Market, T1.NonProcCount, T1.ProcCount, T1.LogDate, T1.MstartDate, T1.NonProcElaps, T1.StartupElaps, T1.ProcElaps, T1.TotalElapsed FROM
(SELECT DISTINCT Enumber, Ename, MRU, Tower, Market, LogDate, MAX(StartDate) as MstartDate,
SUM (
CASE WHEN (ActivityCategory = @ActivAc) AND ([LogDate] = @LogDate) AND (EndDate IS NOT NULL) THEN (NULLIF(Elapsed / 60 , 0))
WHEN (ActivityCategory = @ActivAc) AND ([LogDate] = @LogDate) AND (EndDate IS NULL) THEN (NULLIF(ElapsedSplits / 60, 0)) Else 0 END) AS ProcElaps,
SUM (
CASE WHEN (ActivityCategory = @Startup) AND ([LogDate] = @LogDate) AND (EndDate IS NOT NULL) THEN (DATEDIFF(s, StartDate, @NOW))
WHEN (ActivityCategory = @Startup) AND ([LogDate] = @LogDate) AND (EndDate IS NULL) THEN (NULLIF(ElapsedSplits / 60, 0)) ELSE 0 END) AS StartupElaps,
SUM (
CASE WHEN (ActivityCategory = @ActivAcnon) AND ([LogDate] = @LogDate) AND (EndDate IS NOT NULL) THEN (NULLIF(Elapsed / 60 , 0))
WHEN (ActivityCategory = @ActivAcnon) AND ([LogDate] = @LogDate) AND (EndDate IS NULL) THEN (NULLIF(ElapsedSplits / 60, 0)) ELSE 0 END) AS NonProcElaps,
COUNT(DISTINCT
CASE WHEN (ActivityCategory = @ActivAcnon) AND ([LogDate] = @LogDate) THEN ID ELSE 0 END) AS NonProcCount,
COUNT(DISTINCT
CASE WHEN (ActivityCategory = @ActivAc) AND ([LogDate] = @LogDate) THEN ID ELSE 0 END) AS ProcCount,
SUM (
CASE WHEN (ActivityCategory = @ActivAcnon OR ActivityCategory = @ActivAc) AND ([LogDate] = @LogDate) AND (EndDate IS NOT NULL) THEN (NULLIF(Elapsed / 60 , 0))
WHEN (ActivityCategory = @ActivAcnon OR ActivityCategory = @ActivAc) AND ([LogDate] = @LogDate) AND (EndDate IS NULL) THEN (NULLIF(ElapsedSplits / 60, 0)) ELSE 0 END) AS TotalElapsed
FROM tbl_Main_Timer as MT WHERE ([LogDate] = @LogDate)
GROUP BY Enumber, Ename, MRU, Tower, Market, LogDate) AS T1
WHERE Enumber = T1.Enumber
Group By T1.Enumber, T1.Ename, T1.MRU, T1.Tower, T1.Market, T1.NonProcCount, T1.ProcCount, T1.LogDate, T1.MstartDate, T1.NonProcElaps, T1.StartupElaps, T1.ProcElaps, T1.TotalElapsed) AS T2
WHERE Enumber = T2.Enumber
Group BY T2.Enumber, T2.Ename, T2.MRU, T2.Tower, T2.Market, T2.NonProcCount, T2.ProcCount, T2.LogDate, T2.MstartDate, T2.NonProcElaps, T2.StartupElaps, T2.ProcElaps, T2.TotalElapsed) AS T3
INNER JOIN (
SELECT Enumber, MDate = (SELECT MAX(StartDate) FROM tbl_Main_Timer WHERE Enumber = A.Enumber AND CurrStat = @Stat) FROM tbl_Main_Timer A
WHERE A.CurrStat = @Stat AND A.LogDate = @LogDate
GROUP BY A.Enumber
) B ON T3.Enumber = B.Enumber AND T3.MstartDate = B.MDate
WHERE T3.Enumber = B.Enumber AND T3.LogDate = @LogDate AND T3.MstartDate = B.MDate
Group By T3.Enumber, T3.Ename, T3.MRU, T3.Tower, T3.Market, T3.NonProcCount, T3.ProcCount, T3.LogDate, T3.MstartDate, T3.NonProcElaps, T3.StartupElaps, T3.ProcElaps, T3.Avi, T3.Effi, T3.EffiLoss, T3.TotalElapsed) AS T4
WHERE CurrStat = @Stat And tbl_Main_Timer.Enumber = T4.Enumber
Thank you in advance