Query to find out time entry error issues!

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Query to find out time entry error issues!

Author

Topic

TechAbhi
Starting Member

8 Posts

Posted - 2011-01-17 : 12:44:27

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!!

RobertKaucher
Posting Yak Master

169 Posts

Posted - 2011-01-17 : 21:31:43

What are the data types of the columns?

I also want to say that the best thing to do is prevent this at the time of entry. This is probably a little sloppy but you might try something like this. I included a sample table variable so I could play with the data more easily.


DECLARE @A TABLE
(
EMPLOYEENAME VARCHAR(5),
EMP_ID TINYINT,
CLIENTID TINYINT,
BEGINDATE VARCHAR(10),
STARTTIME VARCHAR(8),
ENDTIME VARCHAR(10),
SIGNDATE VARCHAR(10),
SIGNTIME VARCHAR(8)
)

INSERT @A
SELECT 'ABC', 123, 131, '10/16/2009', '11:00:00', '12:30:00', '10/18/2009', '13:11:00' UNION ALL
SELECT 'ABC', 123, 132, '10/15/2009', '14:00:00', '15:30:00', '10/16/2009', '11:10:00' UNION ALL
SELECT 'ABC', 123, 131, '10/20/2009', '11:00:00', '12:30:00', '10/22/2009', '13:11:00' UNION ALL
SELECT 'ABC', 123, 132, '10/19/2009', '14:00:00', '15:30:00', '10/20/2009', '11:10:00' UNION ALL 
SELECT 'ABC', 123, 131, '11/01/2009', '11:00:00', '12:30:00', '11/02/2009', '13:11:00' UNION ALL
SELECT 'ABC', 123, 132, '11/19/2009', '14:00:00', '15:30:00', '11/20/2009', '11:10:00' 

;WITH Employee_CTE(EMPLOYEENAME
	           ,EMP_ID
                   ,CLIENTID
                   ,BEGINDATE
                   ,STARTDATETIME
                   ,ENDDATETIME
                   ,SIGNDATETIME
                   )
                   AS
                   (
	     SELECT EMPLOYEENAME
		   ,EMP_ID
                   ,CLIENTID
                   ,BEGINDATE
                   ,BEGINDATE +' '+STARTTIME AS StartDateTime
                   ,BEGINDATE +' '+ENDTIME AS EndDateTime
                   ,SIGNDATE +' '+ SIGNTIME AS SignDateTime
                   
                   FROM @A
					)
					
	     SELECT a.EMPLOYEENAME
	           ,a.EMP_ID
                   ,a.CLIENTID
                   ,a.BEGINDATE
                   ,a.STARTDATETIME
                   ,a.ENDDATETIME
                   ,a.SIGNDATETIME
                  
       FROM Employee_CTE a
       INNER JOIN (
             SELECT EMPLOYEENAME
		   ,EMP_ID
                   ,CLIENTID
                   ,BEGINDATE
                   ,STARTDATETIME
                   ,ENDDATETIME
                   ,SIGNDATETIME
                   FROM Employee_CTE) b ON a.EMP_ID = b.EMP_ID AND a.CLIENTID != b.CLIENTID 
       WHERE CAST(b.SIGNDATETIME AS DATETIME) BETWEEN CAST(a.STARTDATETIME AS DATETIME) AND CAST(a.ENDDATETIME AS DATETIME)

===
http://www.ElementalSQL.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources