T-SQL script

• HBK_4700

  SSCrazy Eights

  Points: 8932

  More actions

  July 15, 2011 at 9:52 pm

  #389990

  Hi,

  I need to write a script to pull the data for ' how often user's are logging into system'.

  I have attached the tables script and sample data.

  I am able to pull lastlogindatetime of users and count of logins for each user. But I need the how often the users are logging.(for example in days/hours)

  If you see the Loginhistory table there is LoginHistoryDateTime column , it records all the logindatatime records for each user.

  Appreciate your help.

• LutzM

  SSC Guru

  Points: 107049

  More actions

  July 16, 2011 at 3:00 am

  #1353816

  There were more than 40 people so far looking at the issue you posted.

  Even though you did a fine job to present table definitions, some of us refuse to open an xlsx file off the web (including those who simply can't open it due to the missing Excel version).

  Another reason might be the data are expected not to be in a ready to use format (INSERT INTO TABLE () SELECT VALUES()). I recommend you take a look at the Excel file, reformat the sample data to be in a ready to use format and attach it as txt or csv file.

  ---

  
  Lutz
  A pessimist is an optimist with experience.

  How to get fast answers to your question[/url]
  How to post performance related questions[/url]
  Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

• HBK_4700

  SSCrazy Eights

  Points: 8932

  More actions

  July 16, 2011 at 6:58 am

  #1353837

  Thanks LutzM!

  I have attached the data in ready to use format (INSERT INTO TABLE () SELECT VALUES()).

  Also attached sample data in xls format as well as .txt format.

  User's last login time

  select loginid,lastlogindatetime from login

  Count of login

  select loginid ,COUNT (loginid) as 'Number of Logins' from LoginHistory

  group by loginid

  Now I nee to find, how often a user is logging into system, For example user 6 logined 148 times sofar. Thanks!

• LutzM

  SSC Guru

  Points: 107049

  More actions

  July 16, 2011 at 7:15 am

  #1353839

  unfortunately, the data are not ready to use.

  Please have a look at the first link in my signature on how to post sample data.

  ---

  
  Lutz
  A pessimist is an optimist with experience.

  How to get fast answers to your question[/url]
  How to post performance related questions[/url]
  Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

• HBK_4700

  SSCrazy Eights

  Points: 8932

  More actions

  July 16, 2011 at 8:49 am

  #1353852

  here is sample data in ready to use in format- attached

• Welsh Corgi

  SSC Guru

  Points: 116520

  More actions

  July 16, 2011 at 8:56 am

  #1353854

  laddu4700 (7/16/2011)

  ---

  here is sample data in ready to use in format- attached

  Why do you insist on providing the data as an attachment?

  For better, quicker answers on T-SQL questions, click on the following...
  http://www.sqlservercentral.com/articles/Best+Practices/61537/

  For better answers on performance questions, click on the following...
  http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

• HBK_4700

  SSCrazy Eights

  Points: 8932

  More actions

  July 16, 2011 at 9:20 am

  #1353856

  --table defination

  SET ANSI_NULLS ON

  GO

  SET QUOTED_IDENTIFIER ON

  GO

  CREATE TABLE [dbo].[LoginHistory](

  [LoginID] [bigint] NOT NULL,

  [LoginHistoryID] [smallint] NOT NULL,

  [LoginHistoryDateTime] [datetime] NOT NULL,

  PRIMARY KEY CLUSTERED

  (

  [LoginID] ASC,

  [LoginHistoryID] ASC

  )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

  ) ON [PRIMARY]

  GO

  ALTER TABLE [dbo].[LoginHistory] WITH CHECK ADD CONSTRAINT [ILoginHistory1] FOREIGN KEY([LoginID])

  REFERENCES [dbo].[Login] ([LoginID])

  GO

  ALTER TABLE [dbo].[LoginHistory] CHECK CONSTRAINT [ILoginHistory1]

  GO

  ---sample data

  INSERT INTO lOGINHISTORY

  (LOGINID,LOGINHISTORYID,LOGINHISTORYDATETIME)

  SELECT '6','1','Jan 11 2010 11:00PM', UNION ALL

  SELECT '6','2','Jan 11 2010 11:00PM', UNION ALL

  SELECT '6','3','Jan 11 2010 11:06PM', UNION ALL

  SELECT '6','4','Jan 11 2010 11:15PM', UNION ALL

  SELECT '6','5','Jan 11 2010 11:35PM', UNION ALL

  SELECT '6','6','Jan 12 2010 2:44AM', UNION ALL

  SELECT '6','7','Jan 12 2010 5:20PM', UNION ALL

  SELECT '6','8','Jan 12 2010 7:16PM', UNION ALL

  SELECT '6','9','Jan 12 2010 7:27PM', UNION ALL

  SELECT '6','10','Feb 17 2010 8:32PM', UNION ALL

  SELECT '6','11','Feb 17 2010 8:41PM', UNION ALL

  SELECT '6','12','Feb 17 2010 9:08PM', UNION ALL

  SELECT '6','13','Feb 17 2010 9:17PM', UNION ALL

  SELECT '6','14','Feb 18 2010 9:37PM', UNION ALL

  SELECT '6','15','Feb 22 2010 2:40PM', UNION ALL

  SELECT '6','16','Feb 25 2010 3:59PM', UNION ALL

  SELECT '6','17','Mar 1 2010 9:31PM', UNION ALL

  SELECT '6','18','Mar 2 2010 2:22AM', UNION ALL

  SELECT '6','19','Mar 2 2010 2:25AM', UNION ALL

  SELECT '6','20','Mar 3 2010 2:26PM', UNION ALL

  SELECT '6','21','Mar 3 2010 3:21PM', UNION ALL

  SELECT '6','22','Mar 8 2010 3:34PM', UNION ALL

  SELECT '6','23','Mar 8 2010 7:24PM', UNION ALL

  SELECT '6','24','Mar 9 2010 2:35PM', UNION ALL

  SELECT '6','25','Mar 9 2010 2:41PM', UNION ALL

  SELECT '6','26','Mar 10 2010 3:06PM', UNION ALL

  SELECT '6','27','Mar 11 2010 3:24AM', UNION ALL

  SELECT '6','28','Mar 11 2010 6:43PM', UNION ALL

  SELECT '6','29','Mar 11 2010 7:19PM', UNION ALL

  SELECT '6','30','Mar 12 2010 2:55AM', UNION ALL

  SELECT '6','31','Mar 12 2010 3:35PM', UNION ALL

  SELECT '6','32','Mar 12 2010 7:35PM', UNION ALL

  SELECT '6','33','Mar 15 2010 1:44AM', UNION ALL

  SELECT '6','34','Mar 23 2010 2:42PM', UNION ALL

  SELECT '6','35','Mar 23 2010 6:34PM', UNION ALL

  SELECT '6','36','Mar 23 2010 6:35PM', UNION ALL

  SELECT '6','37','Mar 24 2010 3:00PM', UNION ALL

  SELECT '6','38','Mar 24 2010 3:26PM', UNION ALL

  SELECT '6','39','Mar 29 2010 11:22PM', UNION ALL

  SELECT '6','40','Mar 31 2010 5:25PM', UNION ALL

  SELECT '6','41','Apr 1 2010 12:31AM', UNION ALL

  SELECT '6','42','Apr 1 2010 3:18PM', UNION ALL

  SELECT '6','43','Apr 1 2010 4:11PM', UNION ALL

  SELECT '6','44','Apr 1 2010 4:28PM', UNION ALL

  SELECT '6','45','Apr 1 2010 6:08PM', UNION ALL

  SELECT '6','46','Apr 1 2010 6:49PM', UNION ALL

  SELECT '6','47','Apr 1 2010 8:44PM', UNION ALL

  SELECT '6','48','Apr 2 2010 7:07PM', UNION ALL

  SELECT '6','49','Apr 5 2010 9:45PM', UNION ALL

  SELECT '6','50','Apr 6 2010 3:15AM', UNION ALL

  SELECT '6','51','Apr 9 2010 5:54PM', UNION ALL

  SELECT '6','52','Apr 15 2010 10:03PM', UNION ALL

  SELECT '6','53','Apr 19 2010 6:00PM', UNION ALL

  SELECT '6','54','Apr 26 2010 4:15PM', UNION ALL

  SELECT '6','55','Apr 27 2010 2:47PM', UNION ALL

  SELECT '6','56','Apr 28 2010 2:12PM', UNION ALL

  SELECT '6','57','Apr 28 2010 2:31PM', UNION ALL

  SELECT '6','58','Apr 28 2010 5:44PM', UNION ALL

  SELECT '6','59','Apr 28 2010 6:25PM', UNION ALL

  SELECT '6','60','Apr 28 2010 8:43PM', UNION ALL

  SELECT '6','61','Apr 28 2010 10:23PM', UNION ALL

  SELECT '6','62','Apr 29 2010 1:25PM', UNION ALL

  SELECT '6','63','Apr 29 2010 2:49PM', UNION ALL

  SELECT '6','64','Apr 29 2010 9:06PM', UNION ALL

  SELECT '6','65','Apr 30 2010 9:45PM', UNION ALL

  SELECT '6','66','Apr 30 2010 9:51PM', UNION ALL

  SELECT '6','67','May 3 2010 8:22PM', UNION ALL

  SELECT '6','68','May 4 2010 3:52PM', UNION ALL

  SELECT '6','69','May 4 2010 6:44PM', UNION ALL

  SELECT '6','70','May 5 2010 3:35PM', UNION ALL

  SELECT '6','71','May 5 2010 8:07PM', UNION ALL

  SELECT '6','72','May 5 2010 9:25PM', UNION ALL

  SELECT '6','73','May 6 2010 5:58PM', UNION ALL

  SELECT '6','74','May 10 2010 5:40PM', UNION ALL

  SELECT '6','75','May 19 2010 10:25PM', UNION ALL

  SELECT '6','76','May 21 2010 1:56PM', UNION ALL

  SELECT '6','77','May 21 2010 2:56PM', UNION ALL

  SELECT '6','78','Jun 24 2010 7:17PM', UNION ALL

  SELECT '6','79','Jun 30 2010 10:10PM', UNION ALL

  SELECT '6','80','Jul 1 2010 7:44PM', UNION ALL

  SELECT '6','81','Jul 6 2010 6:54PM', UNION ALL

  SELECT '6','82','Jul 7 2010 6:58PM', UNION ALL

  SELECT '6','83','Jul 9 2010 7:19PM', UNION ALL

  SELECT '6','84','Jul 12 2010 2:11PM', UNION ALL

  SELECT '6','85','Jul 12 2010 5:56PM', UNION ALL

  SELECT '6','86','Jul 15 2010 6:10PM', UNION ALL

  SELECT '6','87','Jul 19 2010 7:17PM', UNION ALL

  SELECT '6','88','Jul 20 2010 7:55PM', UNION ALL

  SELECT '6','89','Jul 23 2010 7:56PM', UNION ALL

  SELECT '6','90','Jul 26 2010 2:29AM', UNION ALL

  SELECT '6','91','Jul 26 2010 4:17AM', UNION ALL

  SELECT '6','92','Jul 27 2010 4:15PM', UNION ALL

  SELECT '6','93','Jul 29 2010 3:01PM', UNION ALL

  SELECT '6','94','Aug 6 2010 7:24PM', UNION ALL

  SELECT '6','95','Aug 12 2010 5:58PM', UNION ALL

  SELECT '6','96','Aug 12 2010 7:28PM', UNION ALL

  SELECT '6','97','Sep 7 2010 2:01PM', UNION ALL

  SELECT '6','98','Sep 29 2010 7:01PM', UNION ALL

  SELECT '6','99','Dec 7 2010 7:51PM', UNION ALL

  SELECT '6','100','Jan 6 2011 6:31PM', UNION ALL

  SELECT '6','101','Jan 6 2011 6:39PM', UNION ALL

  SELECT '6','102','Jan 6 2011 6:48PM', UNION ALL

  SELECT '6','103','Jan 6 2011 7:13PM', UNION ALL

  SELECT '6','104','Jan 13 2011 10:29PM', UNION ALL

  SELECT '6','105','Jan 31 2011 9:58PM', UNION ALL

  SELECT '6','106','Mar 24 2011 5:04PM', UNION ALL

  SELECT '6','107','Apr 13 2011 7:49PM', UNION ALL

  SELECT '6','108','Apr 15 2011 4:00PM', UNION ALL

  SELECT '6','109','Apr 15 2011 4:49PM', UNION ALL

  SELECT '6','110','Apr 15 2011 8:00PM', UNION ALL

  SELECT '6','111','Apr 18 2011 5:29PM', UNION ALL

  SELECT '6','112','Apr 25 2011 10:56PM', UNION ALL

  SELECT '6','113','Apr 29 2011 2:44AM', UNION ALL

  SELECT '6','114','Apr 29 2011 1:50PM', UNION ALL

  SELECT '6','115','Apr 29 2011 1:53PM', UNION ALL

  SELECT '6','116','Apr 29 2011 3:57PM', UNION ALL

  SELECT '6','117','Apr 29 2011 4:04PM', UNION ALL

  SELECT '6','118','May 10 2011 2:00AM', UNION ALL

  SELECT '6','119','May 11 2011 3:47PM', UNION ALL

  SELECT '6','120','May 12 2011 6:11PM', UNION ALL

  SELECT '6','121','May 13 2011 2:21PM', UNION ALL

  SELECT '6','122','May 13 2011 2:48PM', UNION ALL

  SELECT '6','123','May 13 2011 2:51PM', UNION ALL

  SELECT '6','124','May 13 2011 2:55PM', UNION ALL

  SELECT '6','125','May 20 2011 2:46PM', UNION ALL

  SELECT '6','126','May 31 2011 5:48PM', UNION ALL

  SELECT '6','127','Jun 2 2011 3:56PM', UNION ALL

  SELECT '6','128','Jun 2 2011 4:09PM', UNION ALL

  SELECT '6','129','Jun 3 2011 10:30PM', UNION ALL

  SELECT '6','130','Jun 6 2011 10:45PM', UNION ALL

  SELECT '6','131','Jun 6 2011 11:11PM', UNION ALL

  SELECT '6','132','Jun 8 2011 2:39PM', UNION ALL

  SELECT '6','133','Jun 8 2011 2:47PM', UNION ALL

  SELECT '6','134','Jun 8 2011 2:59PM', UNION ALL

  SELECT '6','135','Jun 8 2011 3:05PM', UNION ALL

  SELECT '6','136','Jun 8 2011 3:07PM', UNION ALL

  SELECT '6','137','Jun 8 2011 3:12PM', UNION ALL

  SELECT '6','138','Jun 8 2011 3:20PM', UNION ALL

  SELECT '6','139','Jun 8 2011 5:37PM', UNION ALL

  SELECT '6','140','Jun 9 2011 3:27PM', UNION ALL

  SELECT '6','141','Jun 9 2011 3:32PM', UNION ALL

  SELECT '6','142','Jun 9 2011 3:42PM', UNION ALL

  SELECT '6','143','Jun 9 2011 3:53PM', UNION ALL

  SELECT '6','144','Jun 9 2011 4:22PM', UNION ALL

  SELECT '6','145','Jun 9 2011 4:26PM', UNION ALL

  SELECT '6','146','Jun 13 2011 2:41PM', UNION ALL

  SELECT '6','147','Jun 16 2011 6:31PM', UNION ALL

  SELECT '8','1','Jan 1 1753 12:00AM', UNION ALL

  SELECT '8','2','Jan 11 2010 11:58PM', UNION ALL

  SELECT '8','3','Jan 12 2010 12:00AM', UNION ALL

  SELECT '8','4','Jan 13 2010 8:18PM', UNION ALL

  SELECT '8','5','Jan 13 2010 8:39PM', UNION ALL

  SELECT '8','6','Jan 13 2010 8:40PM', UNION ALL

  SELECT '8','7','Jan 14 2010 1:24AM', UNION ALL

  SELECT '8','8','Feb 5 2010 6:29PM', UNION ALL

  SELECT '8','9','Feb 18 2010 5:33PM', UNION ALL

  SELECT '8','10','Feb 18 2010 5:38PM', UNION ALL

  SELECT '8','11','Feb 19 2010 5:06PM', UNION ALL

  SELECT '8','12','Mar 3 2010 3:28PM', UNION ALL

  SELECT '8','13','Mar 6 2010 5:37PM', UNION ALL

  SELECT '8','14','Apr 1 2010 5:55PM', UNION ALL

  SELECT '8','15','Apr 1 2010 6:25PM', UNION ALL

  SELECT '8','16','Apr 1 2010 10:31PM', UNION ALL

  SELECT '8','17','Apr 2 2010 3:00PM', UNION ALL

  SELECT '8','18','Apr 6 2010 6:32PM', UNION ALL

  SELECT '8','19','Apr 26 2010 10:28PM', UNION ALL

  SELECT '8','20','Apr 26 2010 10:33PM', UNION ALL

  SELECT '8','21','Apr 26 2010 10:53PM', UNION ALL

  SELECT '8','22','Jun 7 2010 7:43PM', UNION ALL

  SELECT '8','23','Jun 7 2010 7:44PM', UNION ALL

  SELECT '8','24','Jun 7 2010 8:31PM', UNION ALL

  SELECT '8','25','Jun 7 2010 8:40PM', UNION ALL

  SELECT '8','26','Jun 7 2010 8:42PM', UNION ALL

  SELECT '8','27','Jun 8 2010 2:43PM', UNION ALL

  SELECT '8','28','Mar 9 2011 4:35PM', UNION ALL

  SELECT '8','29','Mar 16 2011 3:56PM', UNION ALL

  SELECT '8','30','Apr 29 2011 4:21PM', UNION ALL

  SELECT '8','31','Apr 29 2011 4:30PM', UNION ALL

  ---------------------------------------------------------

  --table defination

  SET ANSI_NULLS ON

  GO

  SET QUOTED_IDENTIFIER ON

  GO

  CREATE TABLE [dbo].[Login](

  [LoginID] [bigint] IDENTITY(1,1) NOT NULL,

  [LoginEmail] [nvarchar](100) NOT NULL,

  [LoginFirstName] [nvarchar](50) NOT NULL,

  [LoginMiddleName] [nvarchar](1) NULL,

  [LoginLastName] [nvarchar](50) NOT NULL,

  [LoginGender] [int] NULL,

  [LoginPassword] [nvarchar](60) NOT NULL,

  [UserTypeID] [smallint] NOT NULL,

  [StatusID] [smallint] NOT NULL,

  [StatusReasonID] [smallint] NULL,

  [PortalLanguageID] [smallint] NOT NULL,

  [LastLoginDateTime] [datetime] NULL,

  [LastLoginHistoryID] [int] NOT NULL,

  [LoginCreatedDate] [datetime] NOT NULL,

  [LoginModifiedDate] [datetime] NOT NULL,

  [LoginResetPassword] [bit] NOT NULL,

  [LoginStatusReasonDate] [datetime] NULL,

  [LastLoginSurveyID] [int] NOT NULL,

  PRIMARY KEY CLUSTERED

  (

  [LoginID] ASC

  )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

  ) ON [PRIMARY]

  GO

  ALTER TABLE [dbo].[Login] WITH CHECK ADD CONSTRAINT [GX_002G00SU] FOREIGN KEY([StatusID])

  REFERENCES [dbo].[Status] ([StatusID])

  GO

  ALTER TABLE [dbo].[Login] CHECK CONSTRAINT [GX_002G00SU]

  GO

  ALTER TABLE [dbo].[Login] WITH CHECK ADD CONSTRAINT [ILogin1] FOREIGN KEY([StatusID], [StatusReasonID])

  REFERENCES [dbo].[StatusReason] ([StatusID], [StatusReasonID])

  GO

  ALTER TABLE [dbo].[Login] CHECK CONSTRAINT [ILogin1]

  GO

  ALTER TABLE [dbo].[Login] WITH CHECK ADD CONSTRAINT [ILogin2] FOREIGN KEY([UserTypeID])

  REFERENCES [dbo].[UserType] ([UserTypeID])

  GO

  ALTER TABLE [dbo].[Login] CHECK CONSTRAINT [ILogin2]

  GO

  ALTER TABLE [dbo].[Login] WITH CHECK ADD CONSTRAINT [ILogin3] FOREIGN KEY([PortalLanguageID])

  REFERENCES [dbo].[Language] ([LanguageID])

  GO

  ALTER TABLE [dbo].[Login] CHECK CONSTRAINT [ILogin3]

  GO

  ALTER TABLE [dbo].[Login] ADD DEFAULT ((0)) FOR [LastLoginSurveyID]

  GO

  --sample data

  --===== All Inserts into the IDENTITY column

  SET IDENTITY_INSERT LOGIN ON

  --===== Insert the test data into the test table

  INSERT INTO lOGIN

  (LoginID, LoginEmail,LoginFirstName,LoginMiddleName,LoginLastName,LoginGender,LoginPassword,UserTypeID,StatusID,StatusReasonID,PortalLanguageID,LastLoginDateTime,LastLoginHistoryID,LoginCreatedDate,LoginModifiedDate,LoginResetPassword,LoginStatusReasonDate,LastLoginSurveyID)

  SELECT '6','XXX@GMAIL.COM','XXX',[' ],'XXX',[2 ],'$2a$12$3P3kdJptN9PqkQnVXT6qBeXWnBBtE/OOYndKOzyDGIIJR3p7ifq8.','6','1',[' ],'1',[Jun 16 2011 7:],'147','Jan 11 2010 11:00PM','Jun 16 2011 7:23PM','0',[' ],'0', UNION ALL

  SELECT '8','XXX@GMAIL.COM','XXX',[' ],'XXX',[1 ],'$2a$12$PofDpKJleDIJ60sAO8YfI.2/mldyXJ0UDNAJPo40C45gFTC7tS97K','1','2',[' ],'1',[May 11 2011 9:],'31','Jan 11 2010 11:48PM','Jun 10 2011 12:00PM','0',[' ],'48'

  --===== Set the identity insert back to normal

  SET IDENTITY_INSERT LOGIN OFF