January 17, 2011 at 10:42 am
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!!
January 17, 2011 at 11:15 am
To help people here to help you, please post your table definition, sample data in a readily useable format. To do this please click on the first link in my signature block. That said, am I correct in assuming your table is defined as:
CREATE TABLE #T(EMPLOYEENAME VARCHAR(3)
, EMP_ID INT, CLIENTID INT
, BEGINDATE VARCHAR(10)
, STARTTIME VARCHAR(8)
, ENDTIME VARCHAR(8)
, SIGNDATE VARCHAR(10)
, SIGNTIME VARCHAR(8))
Further then, would this insert your sample data:
INSERT INTO #T
SELECT 'ABC',123,131,'10/16/2009','11:00:00','12:30:00','10/18/2009','13:11:00'
Or am I completely misunderstanding what you have posted?
January 17, 2011 at 11:26 am
Hello Ron,
This is the table definition:
CREATE TABLE [dbo].[Test1](
[EMPLOYEENAME] [char](32) NULL,
[EMP_ID] [int] NULL,
[CLIENT_ID] [int] NULL,
[BEGINDATE] [datetime] NULL,
[STARTTIME] [varchar](10) NULL,
[ENDTIME] [varchar](10) NULL,
[SIGNDATE] [datetime] NULL,
[SIGNTIME] [varchar](10) NULL
And yes your insert statement would work correctly.
January 17, 2011 at 2:39 pm
Others will most likely be more helpful than myself, the only way I can think of performing what you want to do is to combine the date columns (BEGINDATE and SIGNDATE) which are defined as DATETIME type, and the time columns (STARTTIME and SIGNTIME) into a datetime value, this sample bit of code demonstrates a method of performing that chore for the BEGINDATE and STARTTIME columns. Apply it to the SIGNDATE and SIGNTIME columns as well and then use the results so as to be comparing apples to apples.
SELECT CAST((CONVERT(VARCHAR(10) ,BEGINDATE,110) + ' '+STARTTIME) AS DATETIME) FROM #TT
And the next time you post to a forum to make it easier for people who wish to help you by posting table definition(s) and sample data following the instructions to be found by clicking on the first link in my signature block. That said here is an example:
CREATE TABLE #TT(EMPLOYEENAME VARCHAR(3)
, EMP_ID INT, CLIENTID INT
, BEGINDATE DATETIME
, STARTTIME VARCHAR(8)
, ENDTIME VARCHAR(8)
, SIGNDATE DATETIME
, SIGNTIME VARCHAR(8))
INSERT INTO #TT
SELECT 'XYZ',999,888,'10/16/2009','11:12:13','12:30:00','10/18/2009','13:11:00' UNION ALL
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'
January 17, 2011 at 3:46 pm
Here's an approach just based on selecting the data.
The basic concept: Unpivot the data to get on row per start and one per end date.
Based on the unpivoted data two row number streams are used: the first one will order the Dateval column for each EMP_ID. This usually will provide a sort order by showing START and FINISH for each client in chronological order.
The second row number stream is basically an order by EMP_ID and clientid, with START followed by FINISH.
If the data are valid, the difference of the two row number values will be identical for per EMP_ID, clientid. Therefore, the data can be grouped by EMP_ID,CLIENTID,row. Every valid group will consist of two rows.
The alternative would be adding another column and use the "quirky update" method. This approach most probably would perform better than the query below. But I'm not sure if adding another column and "shuffling aroung indices" would be an option. Or even copy the table into a staging table. It all depends.... 😉
So, if there are performance concerns due to the triple grouping let me know and I'll provide the quirky update option, too.
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.
; with cte as
(
SELECT
EMPLOYEENAME,
EMP_ID,
CLIENTID,
Stat,
Dateval,
ROW_NUMBER() OVER(PARTITION BY EMP_ID ORDER BY Dateval)
- ROW_NUMBER() OVER(PARTITION BY EMP_ID,clientid ORDER BY stat desc) row
FROM
(SELECT
EMPLOYEENAME,
EMP_ID,
CLIENTID,
BEGINDATE + STARTTIME AS START,
BEGINDATE +ENDTIME AS FINISH
FROM #TT
) p
UNPIVOT
(Dateval FOR Stat IN (START,FINISH) )AS unpvt
)
SELECT DISTINCT EMP_ID,CLIENTID
FROM cte
GROUP BY EMP_ID,CLIENTID,row
HAVING COUNT(*)<>2
January 17, 2011 at 4:31 pm
Hello Lutz,
Thank you for your quick reply.
This is what i did, since the amount of data was quite huge i copied the 2010 data to a staging table.
However when i ran you query i got the EMP_ID and CLIENT_ID with 20k + rows.
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.
Thanks!
January 17, 2011 at 4:39 pm
Ooopss!! Completely misunderstood the issue... I'm sorry!
I thought you were looking for overlapping time intervals...
Let's see if I can find a way....
January 17, 2011 at 4:48 pm
Next try:
First, select all signdate values and cross check it against the original table if there is a different client and the signdate is within the others client time.
; WITH cte AS
(
SELECT
emp_id,
clientid,
signdate+signtime AS signtotal
FROM #TT
)
SELECT *
FROM cte
INNER JOIN #TT t
ON t.emp_id=cte.emp_id
AND t.clientid<>cte.clientid
AND t.begindate+endtime>=cte.signtotal
AND t.begindate+starttime<=cte.signtotal
January 17, 2011 at 9:57 pm
Just a couple of notes I'd like to add... first, we've just seen what happens when date and time are stored in separate columns for things like this. And there will be no chance of index seeks because of the date-addition that must be done to make a whole date.
Second, there is no end date to go with the end time. If someone pulls an all nighter and the end time ends the next day, it's either going to be a real problem or you have to remember to manually split the dates/times at midnight. Let's see the criteria for a constraint to make all of THAT happen. 😛
My recommendation is to redesign the table to use the DATETIME datatype. Splitting them up like you did saved you only 4 bytes per row and really did open you up to a world of hurt in future code.
--Jeff Moden
January 17, 2011 at 10:23 pm
Hello Jeff,
Thank you for the suggestion. I would love to do what you asked me but unfortunately these tables are from a really obsolete software and there is no way any changes can be made. But i take your suggestion as an advice and will keep it in mind when i design a new database.
Regards!
January 18, 2011 at 2:23 am
How about adding a computed column and index it? Index view?
January 18, 2011 at 7:31 am
chaudharyabhijit (1/17/2011)
Hello Jeff,Thank you for the suggestion. I would love to do what you asked me but unfortunately these tables are from a really obsolete software and there is no way any changes can be made. But i take your suggestion as an advice and will keep it in mind when i design a new database.
Regards!
Understood. Thanks for the feedback.
--Jeff Moden
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply