Solved

MS SQL update/insert query

Posted on 2009-04-05
7
188 Views
Last Modified: 2012-05-06
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

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)

Open in new window

0
Comment
Question by:HAKSA
  • 5
  • 2
7 Comments
 
LVL 41

Expert Comment

by:pcelba
Comment Utility
It should be much easier to have two Time columns in Table2:
SELECT username, computer, IP, MAC, status, MIN(Time01) FirstTime, MAX(Time01) LastTime

  INTO Table2

  FROM Table1

 GROUP BY username, computer, IP, MAC, status

Open in new window

0
 
LVL 41

Expert Comment

by:pcelba
Comment Utility
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

Open in new window

0
 

Author Comment

by:HAKSA
Comment Utility
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.
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

Open in new window

0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 41

Expert Comment

by:pcelba
Comment Utility
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.
0
 
LVL 41

Accepted Solution

by:
pcelba earned 500 total points
Comment Utility
Let suppose the unique key in T_2 table is username + computer + status (I would prefer username + MAC + status but it does not matter).

You have to group records from T_1 always to avoid duplicities in T_2. And we may suppose both min and max time may change:

SELECT username, computer, IP, MAC, status, MIN(Time01) FirstTime, MAX(Time01) LastTime

  INTO #TempTime

  FROM T_1

 GROUP BY username, computer, IP, MAC, status 
 

UPDATE T_2

   SET Firsttime = CASE WHEN T_2.FirstTime < #TempTime.FirstTime THEN T_2.FirstTime ELSE #TempTime.FirstTime END,

       Lasttime  = CASE WHEN T_2.LastTime > #TempTime.LastTime THEN T_2.LastTime ELSE #TempTime.LastTime END

 FROM T_2

 JOIN #TempTime 

   ON T_2.username = #TempTime.username 

	and T_2.computer = #TempTime.computer 

	and T_2.status = #TempTime.status
 

INSERT INTO T_2

SELECT username, computer, IP, MAC, status, FirstTime, LastTime

  FROM #TempTime

 WHERE NOT EXIST 

   (SELECT null FROM T_2 

     WHERE username = #TempTime.username 

       AND computer = #TempTime.computer

       AND status = #TempTime.status)
 

DROP TABLE #TempTime

Open in new window

0
 
LVL 41

Expert Comment

by:pcelba
Comment Utility
WHERE NOT EXISTS   of course

Other possible solution exist, as well.
0
 

Author Closing Comment

by:HAKSA
Comment Utility
It works just like i want it. Thanks
0

Featured Post

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now