Need help with 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

General SQL Server Forums

New to SQL Server Programming

Need help with query

Author

Topic

maheshrblr
Starting Member

9 Posts

Posted - 2008-11-03 : 06:31:00

Greetings!!

Looks like my question was not explained in detail-- well the code below works fine for a date range of one day

i.e SELECT SID as LoginName, MIN(TimeWritten) as LoginDateTime FROM seclist where timegenerated >= '2008-10-31 00:00:01' and timegenerated <='2008-10-31 23:59:59'
and (SID NOT LIKE '%\IUSR%' and SID NOT LIKE '%\IWM%' and SID NOT LIKE '%Admin%')group by SID

When the date range is changed to an entire month as below the results are corrupted meaning that it generates only for the first day and few results are randomly of other days.

SELECT SID as LoginName, MIN(TimeWritten) as LoginDateTime FROM seclist where timegenerated >= '2008-10-01 00:00:01' and timegenerated <='2008-10-31 23:59:59'
and (SID NOT LIKE '%\IUSR%' and SID NOT LIKE '%\IWM%' and SID NOT LIKE '%Admin%')group by SID

Generates the actual and full result as below--

LoginName LoginDateTime
------------------- -------------------
sqlsrv\user1 2008-11-01 04:06:32
sqlsrv\user2 2008-11-01 07:15:00
sqlsrv\user3 2008-11-01 07:53:28
sqlsrv\user4 2008-11-01 08:00:32
sqlsrv\user5 2008-11-01 08:06:46
sqlsrv\user6 2008-11-01 08:07:11
sqlsrv\user7 2008-11-01 08:12:09
sqlsrv\user8 2008-11-01 08:14:02
sqlsrv\user9 2008-11-01 08:18:13
sqlsrv\user10 2008-11-01 08:19:12
sqlsrv\user11 2008-11-01 08:24:57
sqlsrv\user12 2008-11-01 08:25:36
sqlsrv\user13 2008-11-01 08:42:40
sqlsrv\user14 2008-11-01 08:54:13
sqlsrv\user15 2008-11-01 09:02:59
sqlsrv\user16 2008-11-01 09:23:47
sqlsrv\user17 2008-11-01 09:28:25
sqlsrv\user18 2008-11-01 09:36:26
sqlsrv\user19 2008-11-01 09:40:05
sqlsrv\user20 2008-11-01 10:17:33
sqlsrv\user21 2008-11-01 10:34:02
sqlsrv\user22 2008-11-01 13:33:51
sqlsrv\user23 2008-11-01 16:01:11
sqlsrv\user24 2008-11-03 08:10:39
sqlsrv\user25 2008-11-03 08:52:17
sqlsrv\user26 2008-11-03 08:53:02
sqlsrv\user27 2008-11-03 09:00:54
sqlsrv\user28 2008-11-03 09:50:45
sqlsrv\user29 2008-11-03 10:23:29

Typically the result should be like as below--

sqlsrv\user1 2008-10-01
sqlsrv\user2 2008-10-01
sqlsrv\user3 2008-10-01
sqlsrv\user4 2008-10-01
sqlsrv\user5 2008-10-01
sqlsrv\user6 2008-10-01
sqlsrv\user7 2008-10-01
sqlsrv\user8 2008-10-01

sqlsrv\user1 2008-10-02
sqlsrv\user2 2008-10-02
sqlsrv\user3 2008-10-02
sqlsrv\user4 2008-10-02
sqlsrv\user5 2008-10-02
sqlsrv\user6 2008-10-02
sqlsrv\user7 2008-10-02
sqlsrv\user8 2008-10-02

and so on till the last date of the month--

sqlsrv\user1 2008-10-31
sqlsrv\user2 2008-10-31
sqlsrv\user3 2008-10-31
sqlsrv\user4 2008-10-31
sqlsrv\user5 2008-10-31
sqlsrv\user6 2008-10-31
sqlsrv\user7 2008-10-31
sqlsrv\user8 2008-10-31

Best Regards,
Mahesh

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-03 : 06:40:28

do you mean you want to strip off time part? if yes do like below

SELECT SID as LoginName, MIN(DATEADD(dd,DATEDIFF(dd,0,TimeWritten),0)) as LoginDateTime FROM seclist where timegenerated >= '2008-10-31 00:00:01' and timegenerated <='2008-10-31 23:59:59'
and (SID NOT LIKE '%\IUSR%' and SID NOT LIKE '%\IWM%' and SID NOT LIKE '%Admin%')group by SID

maheshrblr
Starting Member

9 Posts

Posted - 2008-11-03 : 06:50:13

Hi Visakh,

No i do not want to strip off the time part--it was just to illustrate the result format..as login report for the entire month in individual days.

Best Regards,
Mahesh

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-03 : 07:01:51

so is reqmnt to fill in all dates for month een if no data exists for some of them in table?

maheshrblr
Starting Member

9 Posts

Posted - 2008-11-03 : 07:08:39

quote:
Originally posted by visakh16

so is reqmnt to fill in all dates for month even if no data exists for some of them in table?

Hi,

since there will be no data for holidays and weekend dates, the result will have no data in them. Only dates which have data in them according to the query will return the value, else there will be no data to return.

Best Regards,
Mahesh

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-03 : 07:15:16

Ok. then what's your problem? i didnt understand what you're asking for??

maheshrblr
Starting Member

9 Posts

Posted - 2008-11-03 : 07:37:09

quote:
Originally posted by visakh16

Ok. then what's your problem? i didnt understand what you're asking for??

Hi Visakh,

The problem is that i want to generate a entire month's login report for all users , but the query presently does not do that. It generates only the report as follows though the db contains data for the entire month--

LoginName LoginDateTime
------------------- -------------------
sqlsrv\user1 2008-11-01 04:06:32
sqlsrv\user2 2008-11-01 07:15:00
sqlsrv\user3 2008-11-01 07:53:28
sqlsrv\user4 2008-11-01 08:00:32
sqlsrv\user5 2008-11-01 08:06:46
sqlsrv\user6 2008-11-01 08:07:11
sqlsrv\user7 2008-11-01 08:12:09
sqlsrv\user8 2008-11-01 08:14:02
sqlsrv\user9 2008-11-01 08:18:13
sqlsrv\user10 2008-11-01 08:19:12
sqlsrv\user11 2008-11-01 08:24:57
sqlsrv\user12 2008-11-01 08:25:36
sqlsrv\user13 2008-11-01 08:42:40
sqlsrv\user14 2008-11-01 08:54:13
sqlsrv\user15 2008-11-01 09:02:59
sqlsrv\user16 2008-11-01 09:23:47
sqlsrv\user17 2008-11-01 09:28:25
sqlsrv\user18 2008-11-01 09:36:26
sqlsrv\user19 2008-11-01 09:40:05
sqlsrv\user20 2008-11-01 10:17:33
sqlsrv\user21 2008-11-01 10:34:02
sqlsrv\user22 2008-11-01 13:33:51
sqlsrv\user23 2008-11-01 16:01:11
sqlsrv\user24 2008-11-03 08:10:39
sqlsrv\user25 2008-11-03 08:52:17
sqlsrv\user26 2008-11-03 08:53:02
sqlsrv\user27 2008-11-03 09:00:54
sqlsrv\user28 2008-11-03 09:50:45
sqlsrv\user29 2008-11-03 10:23:29

Typically the result should be like as below--

sqlsrv\user1 2008-11-01 04:06:32
sqlsrv\user2 2008-11-01 07:15:00
sqlsrv\user3 2008-11-01 07:53:28
sqlsrv\user4 2008-11-01 08:00:32
sqlsrv\user5 2008-11-01 08:06:46
sqlsrv\user6 2008-11-01 08:07:11
sqlsrv\user7 2008-11-01 08:12:09
sqlsrv\user8 2008-11-01 08:14:02

sqlsrv\user1 2008-11-02 04:06:32
sqlsrv\user2 2008-11-02 07:15:00
sqlsrv\user3 2008-11-02 07:53:28
sqlsrv\user4 2008-11-02 08:00:32
sqlsrv\user5 2008-11-02 08:06:46
sqlsrv\user6 2008-11-02 08:07:11
sqlsrv\user7 2008-11-02 08:12:09
sqlsrv\user8 2008-11-02 08:14:02

and so on till the last date of the month--

sqlsrv\user1 2008-11-31 04:06:32
sqlsrv\user2 2008-11-31 07:15:00
sqlsrv\user3 2008-11-31 07:53:28
sqlsrv\user4 2008-11-31 08:00:32
sqlsrv\user5 2008-11-31 08:06:46
sqlsrv\user6 2008-11-31 08:07:11
sqlsrv\user7 2008-11-31 08:12:09
sqlsrv\user8 2008-11-31 08:14:02

Best Regards,
Mahesh

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-03 : 09:16:17

i guess thats because you applied group by. i dont think you need group by if you want all the data.

SELECT SID as LoginName, DATEADD(dd,DATEDIFF(dd,0,TimeWritten),0) as LoginDateTime FROM seclist 
where timegenerated >= '2008-11-01 00:00:01' and timegenerated <='2008-11-31 23:59:59'
and (SID NOT LIKE '%\IUSR%' and SID NOT LIKE '%\IWM%' and SID NOT LIKE '%Admin%')

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources