How to do double comparison on a table.
Posted on 2012-09-14
I need compare one column LeadDate (datatype is datetime ) to the values saved in another table (named EmployeeWorkTime). The format of the LeadDate column is like this "2006-08-08 11:50:51. 000".
The employeeworktime table is like this:
SyStaffId	Associate Name	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday	Sunday
34128 	Natasha Dawson 	0919	0918	0920	0918	0918	WKEND 	WKEND
34169 	Elaine Bueno 	0918	1019	0918	1019	0918	WKEND 	WKEND
34385 	Aida Rodriguez 	0918	1019	0918	1019	0918	WKEND 	WKEND
34419 	Sy Lim 	1019	1019	1019	1019	0918	WKEND 	WKEND
What I want to achieve is comparing the leadtime to the value on the employee time table and if it's within the working hour then count this lead as an effective lead for that day. If the leadtime is after working hour, count the lead into the next day. I have difficulty in writing TSQL code to do this because the column name is a undecided in the employee time table, I need check the day of the leadtime then know which column I can use in the employee time table. I understand there is a strategy called self join to achieve this, but it's too hard for me, please help.
SELECT syStudent.StuNum AS Student#,
CASE WHEN syStudent.SySchoolStatusID <> 111
AND syStudent.SySchoolStatusID <> 149
AND syStudent.LeadDate >= @StartDateBegin
AND syStudent.LeadDate < @StartDateEnd
AND DATEPART(hh, syStudent.LeadDate)> (Select XXXXXXXXXXXXXXXX DONT KNOW HOW TO SELECT THE WORKING HOURS OUT)
THEN 1 ELSE 0
END AS NewLead,