need help with query
-
Hello all,
Can someone suggests the following:
I have an Employee table with 3 fields:
Client_Rep,
Begin_Work,
End_Work
When Client_Rep starts his job,
Begin_Work in Employee table is being updated and
when he finishes his job
End_Work in Employee table is being updated.
During the day the table looks like that:
Client_Rep Begin_Work End_Work
Vitalio Arini 9.00 9.45
Vitalio Arini 10.00 12.00
Nino Verto 8.30 11.00
Vitalio Arini 14.30 17.00
Nino Verto 15.00 17.00
Ken Coles 9.00 12.30
Ken Coles 13.00 17.00
etc
Client_Rep suppose to have only 1hr break. How can I write a query to find out who has break for more then 1 hr.
TIA
-
Hi
Use DATEDIFF function.
SELECT * from Employee
from employee
where
DATEDIFF(Hour, Begin_Work , End_Work) >= 1
-
Thank you,
I won't work. I have to, somehow, check the difference betwen End_work of the previous entry with Begin_work for the next entry for the same employee.
Any other ideas?
-
Barsuk,
Are you stuck with and existing table or are you still developping?
this structure implies that you have some method for putting the entries in order so that you always have the exact previous record then you will have to have a cursor and scroll through setting the previous end time then comparing to the current start time.....all very klunky.
if you can still change the table I would. I would put the times into a seperate table with a column for the type of time it is and also a date column to have a way to sort them.
HTH tal McMahon
-
Thanks Tal for your response,
This is pretty much the existing structure and I cannot make any changes to it! What can I do is to create a temp table based on the existing structure-the one you just mentioned.
Any idea how to write the code then?
TIA
-
Hi Tal,
I rearrange the table slightly ( which can be done in #temp)
Well. What I am trying to do is the following.
Client_Rep Begin_Work End_Work
Vitalio Arini 9.00 9.45
Vitalio Arini 10.00 12.00
Vitalio Arini 14.30 17.00
Nino Verto 8.30 11.00
Nino Verto 15.00 17.00
Ken Coles 9.00 12.30
Ken Coles 13.00 17.00
Ken Coles has only half on hour break. So I don't need him.
Vitalio has 2 breaks. First 15 minues break (10 am - 9.45 am) which I don't need and 2nd break- 2.5 hours (14.30-12) which I need some how to catch.
Nino has 4 hours break. I need to catch too.
If the method you mentioned is the only -then that is what I need.
I was trying to solve it with subquery, but got stuck.
TIA
-
sorry, I cannot think of any easy way with a subquery, what you have is a mess ...lol
I have never seen a good way to compare details from different rows and different columns with a subquery.
I hope your tables are not that large cuz it is kludge code you are going to need
Good luck.
tal
-
My table is relatively small.
So any idea(s) how to tackle this without subquery.
Cursor maybe or another solution.
Someone must have done it before!
TIA
-
Any Gury around???
-
Barsuk (heck of a handle there),
First, I hope your times look like 12:30 and not 12.30 or we'll have to do a bit more wittling...
The key to the problem is the sort order of names and times in relation to an "ID" field. So... the first step is to dump the data into a clearly defined temp table in the correct order. The rest is "elementary:...
CREATE TABLE dbo.#MyTemp (
ID int IDENTITY (1, 1) NOT NULL ,
Client_Rep varchar (20) NULL ,
Begin_Work datetime NULL ,
End_Work datetime NULL)
INSERT INTO #MyTemp
SELECT Client_Rep,Begin_Work,End_Work
FROM Employee
ORDER BY Client_Rep,Begin_Work,End_Work
SELECT t1.Client_Rep,
t2.End_Work,
t1.Begin_Work,
DATEDIFF(mi,0,t1.Begin_Work-t2.End_Work) AS BreakMinutes
FROM #MyTemp t1,
#MyTemp t2
WHERE t1.ID=t2.ID+1
AND t1.Client_Rep=t2.Client_Rep
AND DATEDIFF(mi,0,t1.Begin_Work-t2.End_Work)>60
DROP TABLE #MyTemp
Hope that's "Gury" enough
And, look Ma! 
No CURSOR! 
You can also do away with the CREATE TABLE like this...
- SELECT IDENTITY (INT,1,1) AS ID,Client_Rep,Begin_Work,End_Work
- INTO #MyTemp
FROM Employee
ORDER BY Client_Rep,Begin_Work,End_Work
- SELECT t1.Client_Rep,
t2.End_Work,
t1.Begin_Work,
DATEDIFF(mi,0,t1.Begin_Work-t2.End_Work) AS BreakMinutes
FROM #MyTemp t1,
#MyTemp t2
WHERE t1.ID=t2.ID+1
AND t1.Client_Rep=t2.Client_Rep
AND DATEDIFF(mi,0,t1.Begin_Work-t2.End_Work)>60
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Thanks Jeff!
You are really a GURU!
-
CREATE TABLE #work(Client_Rep VARCHAR(20) NOT NULL,Begin_Work DATETIME NOT NULL,End_Work DATETIME NOT NULL,
PRIMARY KEY(Client_Rep,Begin_Work))
INSERT #work(Client_Rep,Begin_Work,End_Work)
SELECT 'Vitalio Arini','9:00','9:45' UNION
SELECT 'Vitalio Arini','10:00','12:00' UNION
SELECT 'Vitalio Arini','14:30','17:00' UNION
SELECT 'Nino Verto','8:30','11:00' UNION
SELECT 'Nino Verto','15:00','17:00' UNION
SELECT 'Ken Coles','9:00','12:30' UNION
SELECT 'Ken Coles','13:00','17:00'
SELECT * FROM #work
SELECT
*, DATEDIFF(MINUTE,End_Work,Return_Work) AS Break_In_Minutes
FROM
( SELECT
w1.Client_Rep,
--w1.Begin_Work,
w1.End_Work,
MIN( w2.Begin_Work ) AS Return_Work
FROM
#work w1 JOIN #work w2
ON w1.Client_Rep = w2.Client_Rep
AND w1.End_Work < w2.Begin_Work
AND FLOOR(CONVERT(FLOAT,w1.Begin_Work)) = FLOOR(CONVERT(FLOAT,w2.Begin_Work))
GROUP BY
w1.Client_Rep,
--w1.Begin_Work,
w1.End_Work ) AS give_me_a_break
WHERE
DATEDIFF(MINUTE,End_Work,Return_Work) > 60
SELECT
outrwrk.Client_Rep,
outrwrk.Begin_Work,
outrwrk.End_Work,
(SELECT MIN(Begin_Work) FROM #work inrwrk
WHERE inrwrk.Client_Rep = outrwrk.Client_Rep AND inrwrk.Begin_Work > outrwrk.End_Work
AND FLOOR(CONVERT(FLOAT,inrwrk.Begin_Work)) = FLOOR(CONVERT(FLOAT,outrwrk.Begin_Work))) AS Return_Work,
DATEDIFF(MINUTE,outrwrk.End_Work,(SELECT MIN(Begin_Work) FROM #work inrwrk
WHERE inrwrk.Client_Rep = outrwrk.Client_Rep AND inrwrk.Begin_Work > outrwrk.End_Work
AND FLOOR(CONVERT(FLOAT,inrwrk.Begin_Work)) = FLOOR(CONVERT(FLOAT,outrwrk.Begin_Work)))) AS Break_In_Minutes
FROM
#work outrwrk
WHERE
DATEDIFF(MINUTE,outrwrk.End_Work,(SELECT MIN(Begin_Work) FROM #work inrwrk
WHERE inrwrk.Client_Rep = outrwrk.Client_Rep AND inrwrk.Begin_Work > outrwrk.End_Work
AND FLOOR(CONVERT(FLOAT,inrwrk.Begin_Work)) = FLOOR(CONVERT(FLOAT,outrwrk.Begin_Work)))) > 60
DROP TABLE #work
--/rockmoose
You must unlearn what You have learnt -
Sorry,
Didn't notice there were several pages at first...
Oh well at least skipped the temporary tables
And for explanation:
The FLOOR(CONVERT(FLOAT,w1.Begin_Work)) = FLOOR(CONVERT(FLOAT,w2.Begin_Work) stuff,
is just to make sure we are comparing times on the same day.
We do have a problem if they have breaks around midnight .. though
Cheers,
/rockmoose
You must unlearn what You have learnt -
Rockmoose,
>We do have a problem if they have breaks around midnight .. though
Yeah, I had the same problem and decided that it wasn't so bad to use a temp table just for that reason. Could have used the new "table variable" type but people forget that that's a resource (memory) hog, too. Guess it depends on what you value the most.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply