HAKSA
asked on
MS SQL update/insert query
Hello experts, Im newbie to MS SQL. I have two tables both hold the same fields Time, computer name, MAC, IP and Status which can be either logon or logoff. Table1 is populated by a logon script and Table2 should read and maintain from table1 only the first and last time a user logged on\off to any workstation.
I have written the following query which update only the last time a user logged on and off. I would like to refine it so it would also keep the first time a user logged on/off and insert a new record if username and computer name is not matching. I set max pts as this is needed asap. Thanks
I have written the following query which update only the last time a user logged on and off. I would like to refine it so it would also keep the first time a user logged on/off and insert a new record if username and computer name is not matching. I set max pts as this is needed asap. Thanks
UPDATE Table2
SET username = Table1.username
, computer = Table1.computer
, status = Table1.status
, IP = Table1.IP
, MAC = Table1.MAC
, Time01 = Table1.time01
FROM Logonoff2
JOIN Table1
ON Table2.username = logonoff_all.username
and Table12.computer = Table1.computer
and Table1.status = Table2.status
and Table1.time01 > Table2.time01
INSERT INTO Table2 (Username, Computer, Status, IP, time01)
SELECT Table1.username, Table1.computer, Table1.status
, Table1.IP, Table1.time01
FROM Table1
WHERE NOT EXISTS ( SELECT null FROM Table1
WHERE Table2.userName = Table2.username
and computer.Table2 = Table1.computer)
If you need just one pair of time values independently on status, then you may remove status column from above select. If the status is important then you may use following:
SELECT t1.username, t1.computer, t1.IP, t1.MAC,
t1.FirstTime, t1min.Status FirstStatus, t1.LastTime, t1max.Status LastStatus
FROM (SELECT username, computer, IP, MAC, MIN(Time01) FirstTime, MAX(Time01) LastTime
FROM Table1
GROUP BY username, computer, IP, MAC) t1
INNER JOIN Table1 t1min ON t1min.username = t1.username AND t1min.MAC = t1.MAC AND t1min.Time01 = t1.FirstTime
INNER JOIN Table1 t1max ON t1max.username = t1.username AND t1max.MAC = t1.MAC AND t1max.Time01 = t1.LastTime
ASKER
Hi pcelba,
thanks for your reply. I agree with you having 2 time columns in t2 made it much easier but I still have one small problem. Considering that t1 is a transition table and it will be cleaned up or regular bases, How can I maintain the min (time) in t2 even after the original record is deleted in t1.
thanks for your reply. I agree with you having 2 time columns in t2 made it much easier but I still have one small problem. Considering that t1 is a transition table and it will be cleaned up or regular bases, How can I maintain the min (time) in t2 even after the original record is deleted in t1.
UPDATE T_2
SET username = T_1.username
, computer = T_1.computer
, status = T_1.status
, IP = T_1.IP
, MAC= T_1.MAC
, Firsttime= (select min(Time01) from T_1)
, Lasttime = (select max(time01) from T_1)
FROM T_2
JOIN T_1
ON T_2.username = T_1.username
and T_1.computer = T_1.computer
and T_1.status = T_2.status
OK, this means you cannot create T_2 by one SELECT command but you have to go back to Insert/Update scenario. Then it should be sufficient to update just max time because the min time cannot change once calculated. It, of course, depends on the way how data are stored in T_1.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
WHERE NOT EXISTS of course
Other possible solution exist, as well.
Other possible solution exist, as well.
ASKER
It works just like i want it. Thanks
Open in new window