Query to find out time entry error issues!

[TechAbhiVaray]

Hello All,

I have a table with following data
EMPLOYEENAME, EMP_ID, CLIENTID, BEGINDATE, STARTTIME, ENDTIME, SIGNDATE, SIGNTIME


ABC 123 131 10/16/2009 11:00:00 12:30:00 10/18/2009 13:11:00
ABC 123 132 10/15/2009 14:00:00 15:30:00 10/16/2009 11:10:00

In he earlier two lines you can see that employee ABC provided service to a client 131 on 10/16 between 11:00 to 12:30 and entered this time on 10/18 at 13:11.
In the second line you can see the same employee servicing a different client however he entered this on 10/16 at 11:10. The sign time is between StartTime and EndTime from the 1st line.
I want to check for employees who have done the same. I am trying to develop a query for this and its getting really difficult to get it right.

I appreciate any help.

Thanks!!

 

[gmmastros]

I assume your dates and times are stored in varchar columns. I say this because I can't think of a single good reason to separate them unless they are stored in varchars. And... storing them in varchars is probably a big mistake because it makes queries like this much more difficult.

To return the data you want, you can create a self join. Since you "appear" to have varchars, I wrote the query below with a common table expression so that I can merge the date and time data, which allows the between operator to work properly.

;With NormalizedData As
(
  Select EmployeeName, 
         Convert(DateTime, BEGINDATE) + StartTime As StartTime, 
         Convert(DateTime, BEGINDATE) + EndTime As EndTime,
         Convert(DateTime, SIGNDATE) + SIGNTIME As SignTime
  From   YourTableName
)
Select * 
From   NormalizedData As A
       Inner Join NormalizedData As B
         On A.EmployeeName = B.EmployeeName
         And B.SignTime Between A.StartTime And A.EndTime

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[TechAbhiVaray]

Hello gmmastros,

Side Note -
To add the time portion to the date value it's not required to use any explicit conversion.
The implicit conversion will "recognize" the format.

This is what i got when i ran the query you presented:

XYZ 2010-08-19 09:42:00.000 2010-08-19 09:57:00.000 2010-09-03 16:19:00.000
XYZ 2010-08-18 13:00:00.000 2010-08-18 13:38:00.000 2010-08-19 09:51:00.000
XYZ 2010-12-10 09:47:00.000 2010-12-10 10:02:00.000 2011-01-05 07:42:00.000
XYZ 2010-11-18 13:12:00.000 2010-11-18 13:27:00.000 2010-12-10 10:00:00.000
XYZ 2010-12-10 10:15:00.000 2010-12-10 10:30:00.000 2011-01-05 07:49:00.000
XYZ 2010-11-22 10:32:00.000 2010-11-22 10:47:00.000 2010-12-10 10:23:00.000
XYZ 2010-12-10 10:51:00.000 2010-12-10 11:06:00.000 2011-01-05 08:06:00.000
XYZ 2010-11-22 23:00:00.000 2010-11-22 23:15:00.000 2010-12-10 10:51:00.000


However my question still remains the same. My objective is to find entries where an employee has entered two records in the same time. Going back to my 1st post you see the same employee is working for a client in the 1st line and is signing for a client in the second line but at the same time. I would like a query where i can see both the entries side by side hence i can come up with the list of such entries.

 

[simian336]

I ran George's query and got the correct result.

Simi

create table YourTableName (
EMPLOYEENAME varchar(10),
EMP_ID int,
CLIENTID int,
STARTTIME datetime,
ENDTIME datetime,
SIGNtime datetime)

insert into YourTableName values ('ABC', 123, 131 ,'10/16/2009 11:00:00',
'10/16/2009 12:30:00', '10/18/2009 13:11:00')
insert into YourTableName values ('ABC', 123, 132 ,'10/15/2009 14:00:00',
'10/15/2009 15:30:00', '10/16/2009 11:10:00')

Select *
From YourTableName As A
Inner Join YourTableName As B
On A.EmployeeName = B.EmployeeName
And B.SignTime Between A.StartTime And A.EndTime

 

[gmmastros]

Me too.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom