dynamic parameter working like hard coded
i am confused by working of my own query after changing it, i am using several tables join to calculate total overtime of an employee based on his Intime and TimeOut. Scenario is that OVERTIME for people of Shift (S001) should only be calculated after 17:00 pm, regardless of his intime, he would only be applicable for calculation of OverTime if he works above 17:00 pm. For 2nd shift(S002) Overtime is after 18:00. So what i did is that i picked ShiftIDs from shift table, and inner joined it with Employee table, Shift table has only 3 shift id, s001, s002, s003. In Employees table all employees have been assigned s001, s002 only.
Confusion: Now when i pass ShiftID hardcoded like S001 or S002 then it works and even if i pass it like this WHEN SHIFTID = SHIFTID thne it works too, QUESTION is that how does it understand that for which shiftID it is asking in comaprison, both are SHIFTID with equal sign, HOW does it know that whether it is s001 or s002. In hardcoded case it's acceptable but not in Dynamic case. HELP.
with times as ( SELECT t1.EmplID , t3.EmplName , min(t1.RecTime) AS InTime , max(t2.RecTime) AS [TimeOut] , t4.ShiftId as ShiftID , t4.StAtdTime as ShStartTime , t4.EndAtdTime as ShEndTime , cast(min(t1.RecTime) as datetime) AS InTimeSub , cast(max(t2.RecTime) as datetime) AS TimeOutSub , t1.RecDate AS [DateVisited] FROM AtdRecord t1 INNER JOIN AtdRecord t2 ON t1.EmplID = t2.EmplID AND t1.RecDate = t2.RecDate AND t1.RecTime < t2.RecTime inner join HrEmployee t3 ON t3.EmplID = t1.EmplID inner join AtdShiftSect t4 ON t3.ShiftId = t4.ShiftId group by t1.EmplID , t3.EmplName , t1.RecDate , t4.ShiftId , t4.StAtdTime , t4.EndAtdTime ) SELECT EmplID ,EmplName ,InTime ,[TimeOut] ,ShiftId ,ShStartTime ,ShEndTime ,[DateVisited] ,convert(char(5),cast([TimeOutSub] - InTimeSub as time), 108) TotalWorkingTime ,CONVERT(char(5),CASE WHEN CAST([TimeOutSub] AS DATETIME) >= ShEndTime And ShiftID = ShiftID --works even if i pass 002 Then LEFT(CONVERT(varchar(12), DATEADD(ms, DATEDIFF(ms, CAST(ShEndTime AS DATETIME), CAST([TimeOutSub] AS DATETIME)),0), 108),5) WHEN CAST([TimeOutSub] AS DATETIME) >= ShEndTime And ShiftID = ShiftID --works even i pass 002 Then LEFT(CONVERT(varchar(12), DATEADD(ms, DATEDIFF(ms, CAST(ShEndTime AS DATETIME), CAST([TimeOutSub] AS DATETIME)),0), 108),5) ELSE '00:00' END, 108) AS OverTime FROM times
see i used this in two cases: CAST([TimeOutSub] AS DATETIME) >= ShEndTime And ShiftID = ShiftID i have 2 ShEndTime (17:00, 18:00). so in 2 comparisons how does it know that whether i am check > for 17:00 or 18:00
Column name and bind names if beng same.. column name will be considered for both.. so it comes like 1= 1, making the condition as true always. Try variable names like v_SHIDTID
example: From Microsoft docs.
DECLARE @var int SET @var = 10 SELECT * FROM dbo.foo WHERE Id = @var