Need Help by Update Table !
-
Hallo Experts,
First sorry for my English but i leave School some 100 Years ago.I have a Seminar for Basic T-SQL for two days.Also i am a Rookie. My Work was to build an Application in VB 6.0.
This show the Work Details for Agents. So i have an MSSQL Server
and fill in Data into tables and match the data. But in VB the SV can entry some infos in the DB and if the worktime by an Agent changed i must updtae the table every 5 Minutes.
I write this SQL Statements and in end there is the Problem !
They don't update the Data and it doesn't works good.
Please have a look about the complete Statement because i am an Newbie...
Here the Statement:
--1.Truncate all Tables without SVLISTEKS
use ks_sv
truncate table svliste
truncate table svtcs
truncate table svavaya
--2. Insert Data with two DTS Packages
--3.Setting ID's for find out who's not works or is avail
use ks_sv
update SVTCS
set groupid = bausteinetcs.Groupid
from bausteinetcs where svtcs.code=bausteinetcs.code
update SVTCS
set groupid = (
select max(groupid)from SVTCS ks
where ks.personalnummer=ks2.personalnummer and ks.datum=ks2.datum
group by ks.personalnummer,ks.datum
)
from svtcs KS2
--4.delete contains there never interests
delete svtcs
where code in ('Pause','Pause1','Pause2','Shift','Lunch')
delete svtcs
where code in ('CS','OE','Home') and Groupid = 20
--5.delete double with two contains Home is prio one
delete from svtcs
where code = 'OE' and personalnummer in (select ks1.personalnummer from svtcs ks1, svtcs ks2
where ks1.personalnummer=ks2.personalnummer
and ks1.code = 'OE' and ks2.code = 'Home'
and ks1.start_moment = ks2.start_moment)
and start_moment in (select ks1.start_moment from svtcs ks1, svtcs ks2
where ks1.personalnummer=ks2.personalnummer
and ks1.code = 'OE' and ks2.code = 'Home'
and ks1.start_moment = ks2.start_moment)
and stop_moment in (select ks1.stop_moment from svtcs ks1, svtcs ks2
where ks1.personalnummer=ks2.personalnummer
and ks1.code = 'OE' and ks2.code = 'Home'
and ks1.stop_moment = ks2.stop_moment)
--6.search for the Workroom and set it
UPDATE svavaya
SET Raum = n.raum
FROM nebenstellen n
WHERE svavaya.nst = n.nebenstelle
--7.identify the Personalnumber
update svavaya
set personalnummer = acdkennung.personalnummer
from acdkennung
where acdkennung.acdkennung = svavaya.loginid
and acdkennung.personalnummer in (select personalnummer from svtcs)
--8. look for time changes on the schedule
CREATE TABLE #tcs
(
personalnummer int default null,
agentname char (30)default null,
eintrittsdatum char(20)default null,
datum char(20)default null,
code char(10)default null,
start_moment datetime default null,
stop_moment datetime default null,
groupid int default null
)
INSERT INTO #tcs
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)
--9.delete the changed entries
delete from svtcs
where personalnummer in(select distinct personalnummer from #tcs)
and svtcs.start_moment in (select distinct start_moment from #tcs)
--10. insert all into svtcs
insert into svtcs
select *
from #tcs
--11. look for a differenz between start & stop Moment from Login
insert into svliste
select distinct
o.agentname,
ltrim(lo.Raum) ,
o.code ,
o.datum ,
o.start_moment ,
o.stop_moment ,
lo.start ,
lo.stop ,
null,null,null
from svtcs o left outer join svavaya lo on
lo.personalnummer = o.personalnummer
and datediff(n,lo.start,o.start_moment)between -29 and 29
drop table #tcs
--12.Update in Table SVLISTEKS
use ks_sv
insert into svlisteks
select * from svliste sv
where NOT EXISTS ( select * from svlisteks svks
where svks.agent = sv.agent)
Thanks for all
Greetings
Thomas
-
WOw, thanx for the complete details... can you tell us which query is failing and why you think it's not working?
Also some sample data and expected output would be usefull.
-
Beat me to it Remi!
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
Is that a request or a fact
. -
Hey thanks for replay,
@ Phil sorry i dont understand the meaning "Beat me to it remi"
What is not OK from me??
@ Remi.. I Cant upload some Data.I Dont now how.
There are two kinds of data.
First they look by the schedule Times it likes:
Personalnumber,PlanStart,Planstop like the Planning Programm
2nd. they look on the Tecom Data likes:
Personalnumber,realstart,realstop
Also i will Match this Data every 5 Minutes.
If i truncate the tables and fill in every 5 Minutes all
the Match is 99,9% OK.
But I will stored the Table with the Information who i sent with the VB Application. Resume the table must grow from yesteday till now. But this is the Problem. In my description under --12.
If the Data i match not exists in the table update else nothing.
This is a complete select by my name !
It doesn't overwrite.
Scherner, Thomas OE 1 OE 2005-08-24 00:00:00.0002005-08-24 07:45:00.0002005-08-24 12:00:00.0002005-08-24 07:44:49.000NULLNULLNULLNULL
In this select for today it no match at the Iststart Time you see!?
xxxxxxxx, xxxxxxxxx NULLOE 2005-08-25 00:00:00.0002005-08-25 16:00:00.0002005-08-26 00:45:00.000NULLNULLNULLNULLNULL
So i think there is a Problem with the Update.
If anythink change for the Day an Start or Stop Time overwrite else nothink an its ok.
Is this OK for description ???
Thomas
-
Hi Thomas - as I was writing my last post, I noticed that Remi had already written almost exactly what I was typing - so he "beat me to it" ie posted before me. Remi - this was not an invitation to whip me to a musical accompaniment
I'm afraid that you might need to get someone whose English is a little better to help you write your post - I cannot work out what you want to do and what is going wrong.
Regards, Phil
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
I can't make out what you want either.
Looks like you application is doing a lot of stuff but it's not working for all the data. Is it possible that you have some inserts taking places before the whole operation is done??
Check out this link to find out to send data (insert statements), it's in the last part of the page.
-
Hey Phil,
sorry for the question for your insites .
I work alone but going on search for an german site sql or any who write english for me.
I think only an update in a table with no duplicates will help.
The Time and Date for any Agent must be unique.
Thanks all for spending time.
I am looking for your tip to upload Remi thanks.
Regards
Thomas
-
Thanks for all !
Sorry Remi i be stupid and i can't understand how to upload.
But this Forum is great and spent a lot of Time for same like me.
Fun a long the Way
Thomas
-
http://vyaskn.tripod.com/code.htm#inserts
Download the sp, install it and follow there instructions to see hwo to generate the insert statements.
-
INSERT INTO #tcs
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)
this insert is weired
Do you get the data that you want in #tcs?
Kindest Regards,Vasc
-
Hallo Vasc,
thanks for looking. This Statement is ok.
in Table there are data like this
Thomas 8:00 12:00
then i take a next shift an in data stand
Thomas 8:00 12:00
Thomas 7:00 8:00
so i will show that Thomas work from 7:00 till 12:00
The Second Stoptime is the First Starttime..
Greetings
Thomas
-
So i think i have the Data 🙂
Here are the Table with Data from svlisteks:
--INSERTs generated by 'sp_generate_inserts' stored procedure written by Vyas
--Build number: 22
--Problems/Suggestions? Contact Vyas @ vyaskn@hotmail.com
--http://vyaskn.tripod.com
SET NOCOUNT ON
PRINT 'Inserting values into [titlesCopy]'
INSERT INTO [titlesCopy] ([Agent],[Ort],[DEP],[Datum],[SollStart],[SollStop],[IstStart],[IstStop],[Zusatzinfo],[Bemerkung],[Enviroment])VALUES('Koch, Dana',NULL,'PROJ','Aug 26 2005 12:00:00:000AM','Aug 26 2005 9:00:00:000AM','Aug 26 2005 4:00:00:000PM',N
INSERT INTO [titlesCopy] ([Agent],[Ort],[DEP],[Datum],[SollStart],[SollStop],[IstStart],[IstStop],[Zusatzinfo],[Bemerkung],[Enviroment])VALUES('Kraft, Rosemarie',NULL,'OE','Aug 26 2005 12:00:00:000AM','Aug 26 2005 12:30:00:000PM','Aug 26 2005 4:45:00:000P
INSERT INTO [titlesCopy] ([Agent],[Ort],[DEP],[Datum],[SollStart],[SollStop],[IstStart],[IstStop],[Zusatzinfo],[Bemerkung],[Enviroment])VALUES('Krug, Susanne',NULL,'OE','Aug 26 2005 12:00:00:000AM','Aug 26 2005 9:00:00:000AM','Aug 26 2005 12:15:00:000PM',
INSERT INTO [titlesCopy] ([Agent],[Ort],[DEP],[Datum],[SollStart],[SollStop],[IstStart],[IstStop],[Zusatzinfo],[Bemerkung],[Enviroment])VALUES('Kuellmer, Andrea',NULL,'CS','Aug 26 2005 12:00:00:000AM','Aug 26 2005 10:00:00:000AM','Aug 26 2005 4:30:00:000P
INSERT INTO [titlesCopy] ([Agent],[Ort],[DEP],[Datum],[SollStart],[SollStop],[IstStart],[IstStop],[Zusatzinfo],[Bemerkung],[Enviroment])VALUES('Kuellmer, Andrea',NULL,'PROJ','Aug 26 2005 12:00:00:000AM',NULL,NULL,NULL,NULL,NULL,NULL,NULL)
INSERT ,N
PRINT 'Done'
SET NOCOUNT OFF
And this are the Data from SVLISTE
--INSERTs generated by 'sp_generate_inserts' stored procedure written by Vyas
--Build number: 22
--Problems/Suggestions? Contact Vyas @ vyaskn@hotmail.com
--http://vyaskn.tripod.com
SET NOCOUNT ON
PRINT 'Inserting values into [svliste]'
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
INSERT INTO [svliste] VALUES('Abels, Brigitte','OE 1','OE','Aug 26 2005 12:00:00:000AM','Aug 26 2005 7:45:00:000AM','Aug 26 2005 2:00:00:000PM','Aug 26 2005 7:44:42:000AM','Aug 26 2005 2:01:53:000PM',NULL,NULL,NULL)
INSERT INTO [svliste] VALUES('Acikgoez, Erdogan',NULL,'OE','Aug 26 2005 12:00:00:000AM','Aug 26 2005 8:00:00:000PM','Aug 26 2005 11:15:00:000PM',NULL,NULL,NULL,NULL,NULL)
INSERT INTO [svliste] VALUES('Aliaga, Kathleen',NULL,'PROJ','Aug 26 2005 12:00:00:000AM','Aug 26 2005 9:00:00:000AM','Aug 26 2005 4:00:00:000PM',NULL,NULL,NULL,NULL,NULL)
INSERT INTO [svliste] VALUES('Aman, Christina',NULL,'OE','Aug 26 2005 12:00:00:000AM','Aug 26 2005 2:00:00:000PM','Aug 26 2005 8:30:00:000PM',NULL,NULL,NULL,NULL,NULL)
INSERT INTO [svliste] VALUES('Aman, Christina','OE 1','OE','Aug 26 2005 12:00:00:000AM','Aug 26 2005 1:30:00:000PM','Aug 26 2005 8:30:00:000PM','Aug 26 2005 1:29:53:000PM',NULL,NULL,NULL,NULL)
INSERT INTO [svliste] VALUES('Aman, Christina','OE 1','OT','Aug 26 2005 12:00:00:000AM','Aug 26 2005 1:30:00:000PM','Aug 26 2005 8:30:00:000PM','Aug 26 2005 1:29:53:000PM',NULL,NULL,NULL,NULL)
INSERT INTO [svliste] VALUES('Amlung, Gabriele',NULL,'CS','Aug 26 2005 12:00:00:000AM','Aug 26 2005 5:30:00:000PM','Aug 27 2005 12:00:00:000AM',NULL,NULL,NULL,NULL,NULL)
INSERT INTO [svliste] VALUES('Apel, Melanie',NULL,'OE','Aug 26 2005 12:00:00:000AM','Aug 26 2005 8:30:00:000PM','Aug 27 2005 3:00:00:000AM',NULL,NULL,NULL,NULL,NULL)
INSERT INTO [svliste] VALUES('Arnold, Birgit',NULL,'OE','Aug 26 2005 12:00:00:000AM','Aug 26 2005 8:30:00:000PM','Aug 27 2005 12:45:00:000AM',NULL,NULL,NULL,NULL,NULL)
INSERT INTO [svliste] VALUES('Auer, Beate',NULL,'CS','Aug 26 2005 12:00:00:000AM','Aug 26 2005 5:30:00:000PM','Aug 27 2005 12:00:00:000AM',NULL,NULL,NULL,NULL,NULL)
INSERT INTO [svliste] VALUES('Aydin, Nurdane','OE 1','OE','Aug 26 2005 12:00:00:000AM','Aug 26 2005 1:00:00:000PM','Aug 26 2005 7:30:00:000PM','Aug 26 2005 12:59:15:000PM',NULL,NULL,NULL,NULL)
INSERT INTO [svliste] VALUES('Aydin, Sara',NULL,'URLAUB','Aug 26 2005 12:00:00:000AM',NULL,NULL,NULL,NULL,NULL,NULL,NULL)
INSERT INTO [svliste] VALUES('Bachmann, Mark',NULL,'URLAUB','Aug 26 2005 12:00:00:000AM',NULL,NULL,NULL,NULL,NULL,NULL,NULL)
INSERT INTO [svliste] VALUES('Bachmann, Nadine','OE 1','OE','Aug 26 2005 12:00:00:000AM','Aug 26 2005 6:30:00:000AM','Aug 26 2005 11:45:00:000AM','Aug 26 2005 6:29:13:000AM','Aug 26 2005 11:46:49:000AM',NULL,NULL,NULL)
INSERT INTO [svliste] VALUES('Bachmann, Peter',NULL,'UNURLA','Aug 26 2005 12:00:00:000AM',NULL,NULL,NULL,NULL,NULL,NULL,NULL)
INSERT INTO [svliste] VALUES('Bachrodt, Michael',NULL,'HOME','Aug 26 2005 12:00:00:000AM','Aug 26 2005 12:00:00:000AM','Aug 26 2005 1:00:00:000AM',NULL,NULL,NULL,NULL,NULL)
INSERT INTO [svliste] VALUES('Bachrodt, Michael',NULL,'HOME','Aug 26 2005 12:00:00:000AM','Aug 26 2005 9:00:00:000AM','Aug 26 2005 10:00:00:000AM',NULL,NULL,NULL,NULL,NULL)
INSERT INTO [svliste] VALUES('Bachrodt, Michael',NULL,'HOME','Aug 26 2005 12:00:00:000AM','Aug 26 2005 11:30:00:000AM','Aug 26 2005 1:00:00:000PM',NULL,NULL,NULL,NULL,NULL)
PRINT 'Done'
SET NOCOUNT OFF
And so take the data from svliste and update all data in svlisteks !!
I Hope i done it right .
Regards
thomas
-
Cool, now go back to the firs tlink I gave you and reread it... we still the the ddl of the table, the problem code, the expected results and the current results.
Viewing 15 posts - 1 through 15 (of 41 total)
You must be logged in to reply to this topic. Login to reply