views:

35

answers:

0

I have sql 2005 and when i run below query, data from RealTimeLog table transfer to History but when new data come in RealTimeLog table old data not changed by new one means OutTime data is not changed with new data from RealTimeLog.

insert into History (UserID,UserName,LogDate, [InTime], [OutTime])
SELECT UserID,UserName,[LogDate],CONVERT(nvarchar,MIN(CONVERT(datetime, [LogTime], 108)), 108),
CONVERT(nvarchar, MAX(CONVERT(datetime, [LogTime], 108)), 108)
From RealTimeLog where not Exists (select * from History H Where H.UserID = RealTimeLog.UserID
AND H.UserName=RealTimeLog.UserName AND H.LogDate=RealTimeLog.LogDate) 
GROUP BY UserID,UserName,[LogDate] ORDER BY UserID,[LogDate]

for ex.

1   Shanks  02/05/2010  9:00   10:00

if new Max time generated suppose 11:00 in RealtimeLog then it is not inserted in History table and output remain same as above.