Count the number of Logins - 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)

Count the number of Logins

Author

Topic

boggyboy
Yak Posting Veteran

57 Posts

Posted - 2010-03-02 : 21:40:25

I have a table @foo that keeps track of log ins by users. If a user logs in during the week an entry gets created.

I want to determine the number of login's from the previous week. My query for returning login count works except during the first week of a new year where it returns the wrong data.

Help much appreciated.

declare @foo table
(
WeekNo int
,YearNo int
,Resource_UID uniqueIdentifier
,Res_RBS nVarchar(255)
,Loggedin bit

)

insert into @foo
select
10,2010,'b30059b7-268f-4ac6-ba9f-7e073c80022b','MT Bank.Operations.Central Technology.Project Management Office',1 union all
select 10,2010,'c54e3172-5b6d-4eb7-8d0d-3348988ec3f5','MT Bank.Sales and Marketing.Marketing',1 union all
select 9,2010,'c54e3172-5b6d-4eb7-8d0d-3348988ec3f5','MT Bank.Sales and Marketing.Marketing',1 union all
select 9,2010,'c54e3172-5b6d-4eb7-8d0d-3348988ec3f5','MT Bank.Sales and Marketing.Marketing',1 union all
select 9,2010,'b30059b7-268f-4ac6-ba9f-7e073c80022b','MT Bank.Operations.Central Technology.Project Management Office',1 union all
select 52,2010,'c54e3172-5b6d-4eb7-8d0d-3348988ec3f5','MT Bank.Sales and Marketing.Marketing',1 union all
select 1,2011,'c54e3172-5b6d-4eb7-8d0d-3348988ec3f5','MT Bank.Sales and Marketing.Marketing',1

select count(LoggedIn) from @foo
where Res_RBS = 'MT Bank.Sales and Marketing.Marketing' and WeekNo = datePart(week,GetDate()-7) and YearNo= datePart(year,GetDate())

John W Goodson

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2010-03-03 : 00:01:53

select count(LoggedIn) from @foo
where Res_RBS = 'MT Bank.Sales and Marketing.Marketing'
and WeekNo = datePart(week,GetDate()-7) and YearNo= datePart(year,GetDate()-7)

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

boggyboy
Yak Posting Veteran

57 Posts

Posted - 2010-03-03 : 09:26:10

great!! thanks!

Nick W Saban

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources