Build History of User 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)

Build History of User Logins

Author

Topic

JohnBGood
Starting Member

48 Posts

Posted - 2010-02-19 : 08:38:36

Not sure how to approach this!!

Table A looks like this

userID, userName, lastLogin

1, Joe, 2010-02-17 13:40:36.287
2. Sally, 2010-02-18 05:18:22.530
3, Bill, null
4, Nick, 2010-02-19 04:03:02.040

Table B looks like this

TIMEBYDAY WeekOFYEAR
2011-01-01 00:00:00.000 1
2011-01-02 00:00:00.000 2
2011-01-03 00:00:00.000 2
2011-01-04 00:00:00.000 2
2011-01-05 00:00:00.000 2
2011-01-06 00:00:00.000 2
2011-01-07 00:00:00.000 2
2011-01-08 00:00:00.000 2
2011-01-09 00:00:00.000 3
2011-01-10 00:00:00.000 3
2011-01-11 00:00:00.000 3
2011-01-12 00:00:00.000 3
2011-01-13 00:00:00.000 3
2011-01-14 00:00:00.000 3

I would like to build a table that stores the sum of weekly logins. Something like this

WeekOF, SumLogins

1/1/10, 0
1/8,10, 1
1/15,`0, 0
1/22/10, 1
1/20/10, 2

Any advice much appreciated!!!

JohnBGood
Starting Member

48 Posts

Posted - 2010-02-19 : 08:44:45

I should add that I have the means to automate a daily query.

Sachin.Nand

2937 Posts

Posted - 2010-02-19 : 09:18:54

What is the relation between 2 tables?

PBUH

JohnBGood
Starting Member

48 Posts

Posted - 2010-02-19 : 09:28:36

No hard relationship between Table A and B. Table B may not be needed. Not sure.

Sachin.Nand

2937 Posts

Posted - 2010-02-19 : 09:31:17

Try this



;with cte
as
(
      select MIN(TIMEBYDAY)as mindate,MAX(TIMEBYDAY)as maxdate  from tableA group by WeekOFYEAR
)

select  COUNT(t1.LastLogin)as sumlogins,
        mindate from cte left join tableB t1 on DATEPART(wk,mindate)=DATEPART(wk,t1.dates)
        group by mindate

PBUH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources