• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 641
  • Last Modified:

How to do double comparison on a table.

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#,
&#9;&#9;&#9;CASE WHEN syStudent.SySchoolStatusID <> 111
&#9;&#9;&#9;&#9;&#9;&#9;AND syStudent.SySchoolStatusID <> 149&#9;&#9;&#9;
&#9;&#9;&#9;&#9;&#9;&#9;AND syStudent.LeadDate >= @StartDateBegin
&#9;&#9;&#9;&#9;&#9;&#9;AND syStudent.LeadDate < @StartDateEnd
&#9;&#9;&#9;&#9;&#9;&#9;AND DATEPART(hh, syStudent.LeadDate)> (Select  XXXXXXXXXXXXXXXX  DONT KNOW HOW TO SELECT THE WORKING HOURS OUT)
&#9;&#9;&#9;&#9;&#9;&#9;
&#9;&#9;&#9;&#9;&#9;THEN 1 ELSE 0
&#9;&#9;&#9;END AS NewLead,
0
Jason Yu
Asked:
Jason Yu
  • 7
1 Solution
 
Jason YuAuthor Commented:
Here is the employeeworktime table and the TSQL code for processing.

thank you.
EmployeeTimeTable.JPG
Code-For-Calculate-New-Leads.txt
0
 
Jason YuAuthor Commented:
To implement the logic in Excel, my colleague used this statement in excel and it works well.


=INDEX('Adm Schedule'!$B$2:$H$8,MATCH(S2,'Adm Schedule'!$A$2:$A$8,0),MATCH(CHOOSE(WEEKDAY(P2,2),"MONDAY","TUESDAY","WEDNESDAY","THURSDAY","FRIDAY","SATURDAY","SUNDAY"),'Adm Schedule'!$B$1:$H$1,0))
0
 
hnasrCommented:
Try to list few records of each table and the expected output.
You may upload a worksheet showing the input and the formula applied, and the output.
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
GhunaimaCommented:
Try creating a view like this using Employee time table & then using it in the conditions

select    systaffid, associatename, 'MONDAY' as DayName,
             convert(integer, left(monday,2)) Start_HH,
             convert(integer, left(monday,2)) End_HH
from EmployeeTiteTable

You can use the above as a subquery in your SQL like this

select LeadDate, case when exists (select * from
               (select systaffid, associatename, 'MONDAY' as DayName,
                          convert(integer, left(monday,2)) Start_HH,
                          convert(integer, left(monday,2)) End_HH
               from EmployeeTiteTable) xx
             where xx.sysstaffid=systaff.systaffid and
                        DATEPART(hh, syStudent.LeadDate) between xx.Start_HH and xx.End_HH) then end) THEN AmExtraCurr.Descrip ELSE NULL END AS ExtraCurricular,
:
:
:
      FROM syStudent .....
0
 
Jason YuAuthor Commented:
Thank you very much, Ghunaima. I am really glad in this weekend I can receive your update. I appreciate your help and hope you have a wonderful weekend. I will try this on Monday, it gave me a new thought to solve similar problem.

thanks.
0
 
Jason YuAuthor Commented:
Hi, Ghunaima:

I am sorry, I didn't quite understand this logic, could you please explain it a little bit for me. I already created a view which is exactly the same as the original "EmployeeWorkTime table.

--------------------------------------------------------
Is EmployeeTiteTable a view?

select    systaffid, associatename, 'MONDAY' as DayName,
             convert(integer, left(monday,2)) Start_HH,
             convert(integer, left(monday,2)) End_HH
from EmployeeTiteTable

What is the "MONDAY"? Is it the "Monday' column in the view?

----------------------------------------------------


What does "xx" mean here in the end of the fifth line below?

select LeadDate, case when exists (select * from
               (select systaffid, associatename, 'MONDAY' as DayName,
                          convert(integer, left(monday,2)) Start_HH,
                          convert(integer, left(monday,2)) End_HH
               from EmployeeTiteTable) xx


---------------------------------------------------------------------------------

And what is this condition: "where xx.sysstaffid=systaff.systaffid and
                        DATEPART(hh, syStudent.LeadDate) between xx.Start_HH and xx.End_HH) then end) "   Is "xx" the view's name?  Are you checking if the hour of the leaddate is within the employee's working hours?

Thank you very much. Are you from CampusVue company, I noticed you even referred AmExtraCurr.Descrip which is another table in campusvue database.

thank you and have a great night.
0
 
Jason YuAuthor Commented:
I got it, I created a view as you suggested.

systaffid      associatename      DayName      Start_HH      End_HH
34128           Natasha Dawson            MONDAY      9      19
34169           Elaine Bueno              MONDAY      9      18
34385           Aida Rodriguez            MONDAY      9      18
34419           Sy Lim                    MONDAY      10      19

But it only contains the start and end hours for Monday. If the leaddate is not in Monday, how could I compare. The hardness of this question is we need judge which day it is in "leaddate" and which compare that lead's time to the admission rep's working hour table to decide if that lead belongs to that day or the day after that lead date.  If the lead comes in after working hours, we count it as the next day lead. So, we can precisely count each admission rep's leads number.

Thank you a thousand time for bring me this new logic solution!
0
 
Jason YuAuthor Commented:
Hi, Ghunaima, I made some progress, I created a view and included all the fields I need for the comparison. The new view is as in the attachment. But when I want to compare the lead time to the values in the time columns. I had some difficulties. Since the leadday is different, they are in column DayofWeekName, and I want to compare the leadtime to only one column which is the same as the lead day. how could I do that?

For example, for the first lead, the dayofweekname is Thursay, and i want to copmare the leadtime which is the leadhour column to the Thursday column, how could I write code to achieve it.

Thank you very much.


The commands used for creating the new view:
Create View dbo.cstAdmissionDailyReportLeadCount_jy
AS
SELECT     dbo.syStudent.AmRepID, dbo.syStudent.LeadDate, DATEPART(dw, syStudent.LeadDate) AS DayofWeekNumber, DATEPART(hh, syStudent.LeadDate)
                      AS LeadHour, dbo.EmployeeWorkTime.[Associate Name], DATENAME(dw, syStudent.LeadDate) AS DayofWeekName, EmployeeWorkTime.Monday,
                      EmployeeWorkTime.Tuesday, EmployeeWorkTime.Wednesday, EmployeeWorkTime.Thursday, EmployeeWorkTime.Friday,
                      EmployeeWorkTime.Saturday, EmployeeWorkTime.Sunday, CASE WHEN DATEPART(hh, syStudent.LeadDate)
                      < 18 THEN 1 ELSE 0 END AS CountAsEffectiveLead
FROM         dbo.syStudent INNER JOIN
                      dbo.EmployeeWorkTime ON dbo.syStudent.AmRepID = dbo.EmployeeWorkTime.SyStaffId
NewView.jpg
admission-report.txt
0
 
Jason YuAuthor Commented:
Good reply and solution!
0

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now