Whats wrong with this Query - SQL Server Forums

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)

Whats wrong with this Query

Author

Topic

boggyboy
Yak Posting Veteran

57 Posts

Posted - 2010-03-09 : 11:59:21

I've been banging my head trying to get this to work. I have 2 tables.

Logins - stores users and the last time they connected. the "Last Connected" column gets overridden each time user logs in

LoginHistory - logs a record if the user logged in during the month. I want an entry into this table when a user logs in (1 entry per month).

---------
insert into LoginHistory
Select
datePart(mm,a. Last_Connected_Date),
datePart(yy,a. Last_Connected_Date),
a.Res_UID,
null,
1

from Logins a
--
where a.Res_UID not in (select b.Res_UID from mt_LoginHistory b where b.MonthNo = cast(DatePart(mm,GetDate()) as int)
and b.YearNo = Cast(DatePart(yy,GetDate())as int) )
and a.Last_Connected_Date is not null

Each time I run this query it adds records to the LoginHistory table (not good) I cant figure it out!

Nick W Saban

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-03-09 : 12:42:44

Your insert goes into LoginHistory and your subselect reads in mt_LoginHistory.
Is that correct or a typo?

No, you're never too old to Yak'n'Roll if you're too young to die.

boggyboy
Yak Posting Veteran

57 Posts

Posted - 2010-03-09 : 12:58:53

yeah... thats a typo. I changed the table name before posting.

Nick W Saban

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-03-09 : 13:04:26

Try this:


insert into LoginHistory
Select 
datePart(mm,a. Last_Connected_Date), 
datePart(yy,a. Last_Connected_Date), 
a.Res_UID,
null,
1 

from Logins a
--
where a.Last_Connected_Date is not null
  and not exists(select * from LoginHistory b
                 where a.Res_UID = b.ResUID
                   and b.MonthNo = datePart(mm,a. Last_Connected_Date)
                   and b.YearNo = datePart(yy,a. Last_Connected_Date))