I want to update a history table.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

I want to update a history table.

Author

Topic

boggyboy
Yak Posting Veteran

57 Posts

Posted - 2010-03-15 : 11:40:01

I have a table (Resoures) that stores the “Last Connect Date” of a user

User_UID, Last_Connect_date
‘C54E3172-5B6D-4EB7-8D0D-3348988EC3F5’, 1/3/2010
‘2043F274-744E-40D0-8107-7C0158F60B15’, 1/8/2010
‘566697F2-FD97-48CC-85C6-CAC80581939A’, null

A second table (LoginsByMonth) keeps track of user logins by Month

MonthNo, YearNo,UserUID,LoggedInBit
1,2010, ‘C54E3172-5B6D-4EB7-8D0D-3348988EC3F5’, 1
1, 2010, ‘2043F274-744E-40D0-8107-7C0158F60B15’, 1

I use the following query to update the loginsByMonth Table

Select
datePart(mm,a.LAST_CONNECT_DATE),
datePart(yy,a.LAST_CONNECT_DATE),
a.User_UID,
1
from Resources a
where a.LAST_CONNECT_DATE is not null
and not exists(select * from LoginsByMonth b
where a.Res_UID = b.Res_UID
and b.MonthNo = datePart(mm,a.LAST_CONNECT_DATE)
and b.YearNo = datePart(yy,a.LAST_CONNECT_DATE))

This works well, but now I want to insert a false into my loginsByMonth LoggedInBit column if the user has never logged in (in that case, the last_connect_date will be null)

Consequently, my LoginsByMonth table would be updated to look like this

1,2010, ‘C54E3172-5B6D-4EB7-8D0D-3348988EC3F5’, 1
1, 2010, ‘2043F274-744E-40D0-8107-7C0158F60B15’, 1
Null, null, ‘566697F2-FD97-48CC-85C6-CAC80581939A’, 0

Of course my query as it stands wont do this.. any suggestions?

Nick W Saban

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-15 : 11:44:16

[code]INSERT INTO LoginsByMonth (MonthNo, YearNo,UserUID,LoggedInBit)
SELECT NULL,NULL,User_UID,0
FROM Resoures
WHERE Last_Connect_date IS NULL
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

boggyboy
Yak Posting Veteran

57 Posts

Posted - 2010-03-15 : 12:30:02

Thanks Visakh16. I want to write records to the history database only one time if the user has never logged in. I've got this but it writes records each time i run it. Thoughts?

insert into LoginsByMonth
(
MonthNo, YearNo, User_UID, LoggedIn
)

Select
null,
null,
a.User_UID,
0
from Resources a

where a.LAST_CONNECT_DATE is null
and not exists(select * from loginsbymonth b
where a.User_UID = b.User_UID
and b.MonthNo = null
and b.YearNo = null)

Nick W Saban

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-15 : 12:34:37

quote:
Originally posted by boggyboy

Thanks Visakh16. I want to write records to the history database only one time if the user has never logged in. I've got this but it writes records each time i run it. Thoughts?

insert into LoginsByMonth
(
MonthNo, YearNo, User_UID, LoggedIn
)

Select
null,
null,
a.User_UID,
0
from Resources a

where a.LAST_CONNECT_DATE is null
and not exists(select * from loginsbymonth b
where a.User_UID = b.User_UID
and b.MonthNo is null
and b.YearNo is null)

Nick W Saban

modify like above and see

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

boggyboy
Yak Posting Veteran

57 Posts

Posted - 2010-03-15 : 12:40:53

Oh yes!! Thats it. thanks!

Nick W Saban

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-15 : 12:43:47

the reason is under default settings NULL is not regarded as value but it represents condition of unknown or undefined value. so any comparison with NULL using operators =,>,<,... wont work. So use IS NULL or IS NOT NULL for comparisons using NULL

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

boggyboy
Yak Posting Veteran

57 Posts

Posted - 2010-03-15 : 16:34:06

gotcha! Very much appreciated!

Nick W Saban

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-16 : 00:29:48

welcome again

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources