November 12, 2005 at 4:47 am
Hallo Experts,
i have a problem do an table select.
There are diff variables for an Agent to do a shift.
1st
Dateu_id start_time stop_time
01.01.050108:0012:00
i need
01.01.050108:0012:00 is simply and no problem
Dateu_id start_time stop_time
01.01.050108:0012:00
01.01.050107:0008:00
i need
01.01.050107:0012:00
Dateu_id start_time stop_time
01.01.050108:0012:00
01.01.050112:0013:00
i need
01.01.050108:0013:00
Dateu_id start_time stop_time
01.01.050108:0012:00
01.01.050107:0008:00
01.01.050112:0013:00
i need
01.01.050107:0013:00
The other 3/4 are against
Dateu_id start_time stop_time
01.01.050108:0012:00
i need
01.01.050108:0012:00 is simply and no problem
Dateu_id start_time stop_time
01.01.050108:0012:00
01.01.050108:0009:00
i need
01.01.050109:0012:00
Dateu_id start_time stop_time
01.01.050108:0012:00
01.01.050111:0012:00
i need
01.01.050108:0011:00
Dateu_id start_time stop_time
01.01.050108:0012:00
01.01.050108:0009:00
01.01.050111:0012:00
i need
01.01.050109:0011:00
So the list can be shown extreme so
Dateu_id start_time stop_time
01.01.050108:0012:00
01.01.050108:0009:00
01.01.050111:0012:00
01.01.050208:0012:00
01.01.050207:0008:00
01.01.050212:0013:00
I need
Dateu_id start_time stop_time
01.01.050109:0011:00
01.01.050207:0013:00
Thanks for help
Regarts
Thomas
November 12, 2005 at 6:00 am
I fail to grasp the logic
first is looks like min(starttime) max (stoptime)
But how you come to?
Date u_id start_time stop_time
01.01.05 01 08:00 12:00
01.01.05 01 08:00 09:00
01.01.05 01 11:00 12:00
01.01.05 02 08:00 12:00
01.01.05 02 07:00 08:00
01.01.05 02 12:00 13:00
I need
Date u_id start_time stop_time
01.01.05 01 09:00 11:00 /*huh?*/
01.01.05 02 07:00 13:00 /*min(starttime) max (stoptime)*/
November 12, 2005 at 6:10 am
Hallo and thanks for replay,
at this entries
Date u_id start_time stop_time
01.01.05 01 08:00 12:00
01.01.05 01 08:00 09:00
01.01.05 01 11:00 12:00
the agent short this staffed.
The shift is 8:00 till 12:00
so he can say please cann i go home earlier
so the first shift stored with
8:00 12:00
and the second entry is
11:00 12:00
but i need then
8:00 11:00
Also he come 1hour later for his shift.
Greetings
Thomas
Regards
November 12, 2005 at 6:47 am
Hey here is the statement i build from your tip:
SELECT distinct t1.personalnummer,t1.agentname,t1.eintrittsdatum,
t1.datum,t1.code,min(t1.start_moment),max(t2.stop_moment),t1.groupid
FROM svtemp T1 JOIN svtemp T2 ON (t1.personalnummer=t2.personalnummer)
group by t1.personalnummer,t1.agentname,t1.eintrittsdatum,
t1.datum,t1.code,t1.groupid
it's OK for build the first part because the Agent take
a longer shift on begin earlyer or placed longer.
Thomas
November 12, 2005 at 3:15 pm
How can I understand from the data you provided which line means what?
01.01.05 01 08:00 09:00
01.01.05 01 11:00 12:00
look absolutely the same way for me and my SQL Server.
Why one of them must be included in result time and another one must be excludud?
_____________
Code for TallyGenerator
November 12, 2005 at 3:47 pm
Hey , sorry for late answere.
at first time i work with this statement:
SELECT distinct t1.personalnummer,t1.agentname,t1.eintrittsdatum,
t1.datum,t1.code,t1.start_moment,t2.stop_moment,t1.groupid
FROM svtcs T1 JOIN svtcs T2 ON (t1.personalnummer=t2.personalnummer)
WHERE t1.stop_moment =
(SELECT MAX(t3.stop_moment)
FROM svtcs T3
WHERE (t3.personalnummer=t1.personalnummer)
AND t3.stop_moment =t2.start_moment)
There is no key where i can see it .I think this is the way.
For this example:
Date u_id start_time stop_time
01.01.05 01 08:00 12:00
01.01.05 01 08:00 09:00
Take the min(stop_time)as start_time where the start_time=start_time
and the Max(stop_time)where the start_time=start_time
else min(start_time)and max(stop_time)
For this example:
01.01.05 01 08:00 12:00
01.01.05 01 08:00 09:00
01.01.05 01 11:00 12:00
Take the min(stop_time)as start_time where the start_time=start_time
and the min(start_time)as stop_tim where the stop_time=stop_time
else min(start_time) and max(stop_time)
Sorry i can write it but can't take it in an statement.
Greetings
Thomas
November 12, 2005 at 3:56 pm
Sorry i am again.
There is a difference.. the dep.
Date u_id start_time stop_time dep
01.01.05 01 08:00 12:00 oe
01.01.05 01 08:00 09:00 VERKÜR
01.01.05 01 11:00 12:00 VERKÜR
This is what i found in the table.
Thomas
November 12, 2005 at 4:33 pm
How can I understand from the data you provided which line means what?
01.01.05 01 08:00 09:00
01.01.05 01 11:00 12:00
This are two shifts. NO Changed
In your example the agent work at two times and not from 9 till 11.
Only there are two or three containers where on time match another time it must changed
01.01.05 01 08:00 09:00
01.01.05 01 08:00 12:00
Thomas
November 13, 2005 at 3:02 pm
November 13, 2005 at 3:07 pm
Hey yes of course you are right.
Thomas
November 15, 2005 at 2:44 pm
Thanks at all for Help !
I am fixed this and Matched all what i need.
Regards
Thomas
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply