DECLARE @TEIR int SET @TEIR = 0 --temp table CREATE TABLE #TempA( .......... ) Select @TEIR = ROLEID FROM UserInfo WHERE UserID = @WHO_WHERE; Insert into #TempA(...............) SELECT ................ FROM( Select .......... From Work tt Left Join Error tf on tf.UserID=tt.UserID and tf.SubmitDate=tt.TimeDate Union All select ............. from Error tf Left Join Work tt on tf.UserID=tt.UserID and tf.SubmitDate=tt.TimeDate where tt.UserID is null) BF Inner Join EmployeeManagement_History A on A.UserID=BF.UserID and BF.TimeDate between A.StartDate and isnull(A.EndDate,GetDate()) Where CASE @TEIR WHEN 1 THEN [Employee] WHEN 2 THEN [Supervisor] WHEN 3 THEN [Manager] END = @WHO_WHERE AND BF.TimeDate between @StartDate and @EndDate OPTION(MAXDOP 1); -- Moved all the calculations down to here, so speed up the process Select Distinct ............. from #TempA A LEFT JOIN ( SELECT B.TimeDate,B.WorkDescription, UnitWorkedCummulative = ( SELECT sum(UnitWorked) FROM #TempA A WHERE A.TimeDate <= B.TimeDate AND A.WorkDescription = B.WorkDescription), ErrorCummulative = ( SELECT sum(TotalErrors) FROM #TempA C WHERE C.TimeDate <= B.TimeDate AND C.WorkDescription = B.WorkDescription) FROM #TempA B ) B ON (A.TimeDate =B.TimeDate AND A.WorkDescription = B.WorkDescription ) WHERE A.Work is not null Order by A.WorkDescription, A.TimeDate OPTION(MAXDOP 1); Drop Table #TempA;
Gain unlimited access to on-demand training courses with an Experts Exchange subscription.Get Access
Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.
When asked, what has been your best career decision?
Deciding to stick with EE.
Being involved with EE helped me to grow personally and professionally.
We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE
Connect with Certified Experts to gain insight and support on specific technology challenges including: