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.

Query:

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

Answers


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

Need Your Help

How to force Firefox to render textarea padding the same as in a div?

css firefox textarea padding

I'm attempting to provide a consistent width per line in pixels inside of a textarea across IE8, Firefox and Safari, so that text content wraps lines as predictably and consistently as possible.

Cannot set mnemonic in JLabel with HTML text

java swing

The following associates a JLabel with a JTextArea and sets a mnemonic that decorates the label. Pressing Alt-X on Windows moves the focus to the JTextArea.