query for this - 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)

query for this

Author

Topic

namishtiwari
Starting Member

11 Posts

Posted - 2009-01-20 : 08:21:24

gjf

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-20 : 08:34:08

[code]
SELECT username
FROM arrfsysauditlog
GROUP BY username
HAVING SUM(CASE WHEN datelogged < DATEADD(mm,-6,GETDATE()) THEN 1 ELSE 0 END)>0
AND SUM(CASE WHEN datelogged > DATEADD(mm,-6,GETDATE()) THEN 1 ELSE 0 END)=0
[/code]

namishtiwari
Starting Member

11 Posts

Posted - 2009-01-20 : 08:35:30

Thanks in advance..

ayamas
Aged Yak Warrior

552 Posts

Posted - 2009-01-20 : 08:38:28

But how is your table design?If a user logs in then are you updating his logintime time or inserting a new logintime?

namishtiwari
Starting Member

11 Posts

Posted - 2009-01-20 : 08:40:06

hi,

we are inserting a new logintime for that particular user.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-20 : 08:40:37

quote:
Originally posted by namishtiwari

hi,

we are inserting a new logintime for that particular user.

did you try my suggestion?

namishtiwari
Starting Member

11 Posts

Posted - 2009-01-20 : 08:43:44

here and GETDATE..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-20 : 08:45:40

quote:
Originally posted by namishtiwari

SELECT username
FROM arrfsysauditlog
GROUP BY username
HAVING SUM(CASE WHEN datelogged < DATEADD(mm,-6,GETDATE()) THEN 1 ELSE 0 END)>0
AND SUM(CASE WHEN datelogged > DATEADD(mm,-6,GETDATE()) THEN 1 ELSE 0 END)=0

what is DATEADD here and GETDATE..

it subtracts 6 month from current date and looks only for records before it

namishtiwari
Starting Member

11 Posts

Posted - 2009-01-20 : 08:47:50

i am getting following error when i execute the query..

Error at Command Line:5 Column:31
Error report:
SQL Error: ORA-00904: "DATEADD": invalid identifier

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-20 : 08:50:14

quote:
Originally posted by namishtiwari

i am getting following error when i execute the query..

Error at Command Line:5 Column:31
Error report:
SQL Error: ORA-00904: "DATEADD": invalid identifier

Ohhhh...so you're using Oracle. then i must say you're in wrong forum. this is MS SQL Server forum. Please try your luck in some oracle forums like www.dbforums.com

namishtiwari
Starting Member

11 Posts

Posted - 2009-01-20 : 08:54:01

I am executing these queries in sql server 2005 and the databse which i am using is oracle.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-20 : 08:56:46