Update specific record for each employee for the last datetime

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

August 20th, 2015 7:03am

I have seen worse queries, but nevertheless, it would need some reformatting to be readable. For instance, make all the elements in the FROM clause to stand out with starting everyone on a new line.

The proprieatary form of UPDATE that you use has some advantages, not the least in simplicity. However, a drawback is that if you are not careful, you may end up writing a statement that is not deterministic. That is, a row in the target table may match multiple rows in the source. This results in ambiguity and you don't know which row in the source that "wins". To make matters worse, it's not a lottery, but the code may seem to work. And then  ond day, the query plan changes, and you get a different result.

Also, in your case, I cannot even see the target table in the FROM clause. And what this means, I don't even know.

I would recommmend that you scrutinise the query for ambiguities. That is, make sure that a row in the target can only match one row in the source.

Free Windows Admin Tool Kit Click here and download it now
August 20th, 2015 6:34pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics