August 30, 2005 at 9:43 am
OK this is exact what i want 🙂
INSERT into svlisteks from svliste when we have a different WORKING DATE for the agent (svlisteks contains records for a certain agent from YESTERDAY and older... and svliste HAS a record from today)
That was i think its the first step how i must do.
Ex. one agent hat a shift today from 8 till 12.
it is in the svliste and must insert in SVLISTEKS
This are the datas in the mail from today !
The Agent have a Shift in SVLISTEKS from three days ago but not from today.
second for updated:
-UPDATE those records in svlisteks for which I can find a record in svliste for same WORKING date BUT some fields like... changed
Only the sollstart,sollstop and the DEP can changed in the SVLISTE. Like a Agent changed the Shift from 8 till 13 at the same Datum
for the svliste like this
INSERT INTO [SVLISTE] VALUES('Amlung, Gabriele','CS','CS','Aug 30 2005 12:00:00:000AM','Aug 30 2005 8:00:00:000AM','Aug 30 2005 2:30:00:000PM','Aug 30 2005 7:59:20:000AM',NULL,NULL,NULL,NULL)
This put first in the empty SVLISTEKS
In The next intervall this Agent Data changed like this:
INSERT INTO [SVLISTE] VALUES('Amlung, Gabriele','CS','CS','Aug 30 2005 12:00:00:000AM','Aug 30 2005 8:00:00:000AM','Aug 30 2005 3:30:00:000PM','Aug 30 2005 7:59:20:000AM',NULL,NULL,NULL,NULL)
also the SVLISTEKS must be update because this is changed:
'Aug 30 2005 3:30:00:000PM'
At last the Supervisior can do an information into SVLISTEKS
like :
INSERT INTO [SVLISTE] VALUES('Amlung, Gabriele','CS','CS','Aug 30 2005 12:00:00:000AM','Aug 30 2005 8:00:00:000AM','Aug 30 2005 3:30:00:000PM','Aug 30 2005 7:59:20:000AM',NULL,NULL,fast supporter,tscherner)
If the Data from the Agent changed against ('Aug 30 2005 4:30:00:000PM')like:
INSERT INTO [SVLISTE] VALUES('Amlung, Gabriele','CS','CS','Aug 30 2005 12:00:00:000AM','Aug 30 2005 8:00:00:000AM','Aug 30 2005 4:30:00:000PM','Aug 30 2005 7:59:20:000AM',NULL,NULL,NULL,NULL) the entrys from before also this (fast supporter,tscherner) don't overwrite !
This must the Result from SVLISTEKS after the last Step :
INSERT INTO [SVLISTE] VALUES('Amlung, Gabriele','CS','CS','Aug 30 2005 12:00:00:000AM','Aug 30 2005 8:00:00:000AM','Aug 30 2005 4:30:00:000PM','Aug 30 2005 7:59:20:000AM',NULL,NULL,fast supporter,tscherner)
Thank you so much. You are a great Partner for Help !!
Regards
Thomas
August 30, 2005 at 10:04 am
--first part INSERT missing records in svlisteks
insert into svlisteks
select sv.* from
svliste sv left outer join svlisteks svks on
svks.agent=sv.agent
where
sv.datum<>svks.Datum -- a record in svliste has a diff Datum than ANY in svlisteks for a certain agent
OR
svks.agent is NULL -- we have a record in svliste and the AGENT is not in svlisteks (no history record for this NEW agent)
--second part UPDATE records in svlisteks for which something changed for same datum
update svlisteks
set sollstart=a.sollstart,sollstop=a.sollstop, DEP=a.DEP
from
(select sv.* from
svliste sv inner join svlisteks svks
on svks.agent = sv.agent --same agent
where svks.datum=sv.datum and --same datum
(sv.sollstart<>svks.sollstart or sv.sollstop<>svks.sollstop or sv.DEP<>svks.DEP)--some data changed !
) a
where a.agent=svlisteks.agent and a.datum=svlisteks.datum
Vasc
August 30, 2005 at 10:11 am
--if you want to ADD the changes
insert into svlisteks
select sv.* from
svliste sv left outer join svlisteks svks
on svks.agent = sv.agent --same agent
and svks.datum=sv.datum and --same datum
sv.sollstart=svks.sollstart and sv.sollstop=svks.sollstop and sv.DEP=svks.DEP where sv.agent IS NULL
Vasc
August 30, 2005 at 12:37 pm
Hallo sorry for answere so late.
If i take the first statement it fill in the data. But if i do it again there fill in the same data and so on. The Statement doesnt found the exits data.
The Update also filled in the same data. I am at home right now and in the next hour i can loged in me on the workserver to taste again.
sorry for the problems i have.
I write as soon i can
kind regards
Thomas
August 30, 2005 at 12:55 pm
insert into svlisteks
select sv.* from
svliste sv left outer join svlisteks svks on
svks.agent=sv.agent
and
sv.datum=svks.Datum -- a record in svliste has a diff Datum than ANY in svlisteks for a certain agent
where
svks.agent is NULL -- we have a record in svliste and the AGENT is not in svlisteks (no history record for this NEW agent)
this is the correct version for first part
Vasc
August 30, 2005 at 2:59 pm
--the second part
update svlisteks
set sollstart=a.sollstart,sollstop=a.sollstop, DEP=a.DEP
from
(
select sv.* from
svliste sv left outer join svlisteks svks
on svks.agent = sv.agent --same agent
and svks.datum=sv.datum --same datum
and ISNULL(sv.sollstart,'')=ISNULL(svks.sollstart,'')
and ISNULL(sv.sollstop,'')=ISNULL(svks.sollstop,'')
and ISNULL(sv.DEP,'')=ISNULL(svks.DEP,'')
where svks.agent IS NULL
) a
where a.agent=svlisteks.agent and a.datum=svlisteks.datum
this ONLY WORKS if there is 1 record in the svlisteks for 1 agent in 1 day otherwise you can insert them
insert into svlisteks
select sv.* from
svliste sv left outer join svlisteks svks
on svks.agent = sv.agent --same agent
and svks.datum=sv.datum --same datum
and ISNULL(sv.sollstart,'')=ISNULL(svks.sollstart,'')
and ISNULL(sv.sollstop,'')=ISNULL(svks.sollstop,'')
and ISNULL(sv.DEP,'')=ISNULL(svks.DEP,'')
where svks.agent IS NULL
Vasc
August 30, 2005 at 3:15 pm
Hallo Vasc,
i test it just for this second.
It WORKS GREAT !!! I Test just any changes and Infos entrys.
Please can you look twice on my script Step1 till Step7?
I ve changed your part Script and i runs very faster.
Can i do anything that is better ??
Thank you so Much.. Tomorrow i see the results and i think its SUPER.
Greetings Thomas
August 31, 2005 at 7:51 am
delete svtcs
where code in ('Pause','Pause1','Pause2','Shift','Lunch')
delete svtcs
where code in ('CS','OE','Home') and Groupid = 20
--can be
delete svtcs
where code in ('Pause','Pause1','Pause2','Shift','Lunch')
or (code in ('CS','OE','Home') and Groupid = 20)
--7.identify the Personalnumber
update svavaya
set personalnummer = acdkennung.personalnummer
from acdkennung
where acdkennung.acdkennung = svavaya.loginid
and exists(select personalnummer from svtcs where personalnummer=acdkennung.personalnummer)
--or
update svavaya
set personalnummer = Tbl.personalnummer
from
(select acdkennung.personalnummer,acdkennung.acdkennung
from acdkennung inner join svtcs
on svtcs.personalnummer=acdkennung.personalnummer
)tbl
where tbl.acdkennung = svavaya.loginid
--5.delete double with two contains Home is prio one
delete from svtcs
where code = 'OE' and
EXISTS(
select ks.personalnummer from svtcs ks
where ks.personalnummer=personalnummer and ks.code='Home' and ks.start_moment=start_moment)
and
EXISTS(
select ks.personalnummer from svtcs ks
where ks.personalnummer=personalnummer and ks.code='Home' and ks.stop_moment=stop_moment)
--or if you don't have records like this
-- persnumm code stop start
-- 1 OE s1 s2
-- 1 OE s3 s4
-- 1 Home s1 s4
-- 1 Home s3 s2
--I m not sure if your query is what you want
--just look at this sample data and calculate your result
--if you don't have the above situation or if ALL 4 records are CORRECT in this case you can write
delete from svtcs
where code = 'OE' and
EXISTS(
select ks.personalnummer from svtcs ks
where ks.personalnummer=personalnummer
and ks.code='Home'
and ks.start_moment=start_moment
and ks.stop_moment=stop_moment
)
--or
delete ks1
from svtcs ks1 inner join ks2
on ks1.personalnummer=ks1.personalnummer
and ks1.start_moment=ks2.start_moment
and ks1.stop_moment=ks2.stop_moment
where ks1.code = 'OE' and ks2.code = 'Home'
Vasc
August 31, 2005 at 8:43 am
Hallo Vasc,
thia is the third time i will send this Post.
Thanks for tuning my statements. I test i just in Time and give you feedback.
In the last mail i have the Problem that the DEP not updateed.
I have a round trip by train this night so i can answere next time tomorrow morning .
Greetings
Thomas
September 5, 2005 at 3:06 am
Hallo Vasc,
sorry but i was ill the last days. The runs are very good and fast. I had an Problem by the update an last Step insert.
First:The Agent iststart and the update is correct.
The Agent iststop and the update lost.
This is from SVLISTE :
INSERT INTO [svliste] VALUES('Markgraf, Elke','OE 1','OE','Sep 5 2005 12:00:00:000AM','Sep 5 2005 6:00:00:000AM','Sep 5 2005 10:15:00:000AM','Sep 5 2005 5:56:08:000AM','Sep 5 2005 10:16:20:000AM',NULL,NULL,NULL)
This is from SVLISTEKS:
INSERT INTO [svlisteks] VALUES('Markgraf, Elke',NULL,'OE','Sep 5 2005 12:00:00:000AM','Sep 5 2005 1:00:00:000PM','Sep 5 2005 5:15:00:000PM',NULL,NULL,NULL,NULL,NULL)
Second: There are two entries by the same Agent.
INSERT INTO [svlisteks] VALUES('Markgraf, Elke',NULL,'OE','Sep 5 2005 12:00:00:000AM','Sep 5 2005 6:00:00:000AM','Sep 5 2005 10:15:00:000AM',NULL,NULL,NULL,NULL,NULL)
At first the Supervisior see :
INSERT INTO [svlisteks] VALUES('Markgraf, Elke',NULL,'OE','Sep 5 2005 12:00:00:000AM','Sep 5 2005 6:00:00:000AM','Sep 5 2005 10:15:00:000AM',NULL,NULL,NULL,NULL,NULL)
The Agent begin to work:
Then the SV see:
INSERT INTO [svlisteks] VALUES('Markgraf, Elke','OE 1','OE','Sep 5 2005 12:00:00:000AM','Sep 5 2005 1:00:00:000PM','Sep 5 2005 5:15:00:000PM',NULL,NULL,NULL,NULL,NULL)
The Agent stop work:
Then the SV see:
INSERT INTO [svliste] VALUES('Markgraf, Elke','OE 1','OE','Sep 5 2005 12:00:00:000AM','Sep 5 2005 6:00:00:000AM','Sep 5 2005 10:15:00:000AM','Sep 5 2005 5:56:08:000AM','Sep 5 2005 10:16:20:000AM',NULL,NULL,NULL)
In this three Parts the can be add Zusatzinfo and / Or Bemerkung.
Kind Regards
Thomas
September 5, 2005 at 3:12 am
Hey sorry i forget the script i take. I test to modify yours.
With this script you see the Data in the Post before. I think i don't understand anythink :
insert into svlisteks
select sv.* from
svliste sv left outer join svlisteks svks on
svks.agent=sv.agent
and
sv.datum=svks.Datum -- a record in svliste has a diff Datum than ANY in svlisteks for a certain agent
where
svks.agent is NULL -- we have a record in svliste and the AGENT is not in svlisteks (no history record for this NEW agent)
--the second part
update svlisteks
set sollstart=a.sollstart,sollstop=a.sollstop,
iststart=a.iststart,iststop=a.iststop, DEP=a.DEP,ort=a.ort
from
(
select sv.* from
svliste sv left outer join svlisteks svks
on svks.agent = sv.agent --same agent
and svks.datum=sv.datum --same datum
and (sv.sollstart)=(svks.sollstart) --same Sollstart
and (sv.sollstop)=(svks.sollstop) --same sollstop
where svks.agent IS NULL
) a
where a.agent=svlisteks.agent and a.datum=svlisteks.datum
and a.sollstart=svlisteks.sollstart and a.sollstop = svlisteks.sollstop
and (a.iststart)=(svlisteks.iststart)and (a.iststop )=(svlisteks.iststop)
insert into svlisteks
select sv.* from
svliste sv left outer join svlisteks svks
on svks.agent = sv.agent --same agent
and svks.datum=sv.datum --same datum
and ISNULL(sv.sollstart,'')=ISNULL(svks.sollstart,'')
and ISNULL(sv.sollstop,'')=ISNULL(svks.sollstop,'')
and ISNULL(sv.ort,'')=ISNULL(svks.ort,'')
where svks.agent IS NULL
September 6, 2005 at 7:47 am
update svlisteks
set sollstart=a.sollstart,sollstop=a.sollstop, DEP=a.DEP
from
(
select sv.* from
svliste sv left outer join svlisteks svks
on svks.agent = sv.agent --same agent
and svks.datum=sv.datum --same datum
and ISNULL(sv.sollstart,'')=ISNULL(svks.sollstart,'')
and ISNULL(sv.sollstop,'')=ISNULL(svks.sollstop,'')
and ISNULL(sv.DEP,'')=ISNULL(svks.DEP,'')
where svks.agent IS NULL
) a
where a.agent=svlisteks.agent and a.datum=svlisteks.datum
this ONLY WORKS if there is 1 record in the svlisteks for 1 agent in 1 day otherwise you can insert them
insert into svlisteks
select sv.* from
svliste sv left outer join svlisteks svks
on svks.agent = sv.agent --same agent
and svks.datum=sv.datum --same datum
and ISNULL(sv.sollstart,'')=ISNULL(svks.sollstart,'')
and ISNULL(sv.sollstop,'')=ISNULL(svks.sollstop,'')
and ISNULL(sv.DEP,'')=ISNULL(svks.DEP,'')
where svks.agent IS NULL
You shouldn't use both at the same time! Cause they treat diff cases depending on the LOGIC of your app.
I m updating only sollstart,sollstop,DEP
since you said "Only the sollstart,sollstop and the DEP can changed in the SVLISTE"
You are loosing IsStop because I wasn't updating that. IsStart is probably the same cause I DON'T update that either (you can see that in the update line:set sollstart=a.sollstart,sollstop=a.sollstop, DEP=a.DEP)
Vasc
Viewing 12 posts - 31 through 41 (of 41 total)
You must be logged in to reply to this topic. Login to reply