compare row values - 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)

compare row values

Author

Topic

ascipio
Starting Member

1 Post

Posted - 2011-05-04 : 08:29:29

ID mobileno dateofregistration registrationstate
44 1674174925 2011-04-18 10:17:30.670 0
45 1677864168 2011-04-18 10:20:22.450 1
46 1677864161 2011-04-18 20:47:35.293 0
47 1674174925 2011-04-19 09:28:55.200 1
48 1674174925 2011-04-19 09:28:56.200 0
49 1674174925 2011-04-19 09:28:57.200 1
50 1674174925 2011-04-18 10:17:30.670 0
51 1677864168 2011-04-18 10:20:22.450 1
52 1677864161 2011-04-20 20:47:35.293 0
53 1674174925 2011-04-22 09:28:55.200 1
54 1674174925 2011-04-28 09:28:56.200 0
55 1674174925 2011-05-28 09:28:57.200 1

my problem is how to count the row with the following conditions
1. registrationstate=1 and datediff(day,dateofregistration)>30 for the individual mobile no.

Here the result will be 1

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-05-04 : 08:37:56

select count(*)
from yourTable
where registrationstate = 1
and datediff(day,dateofregistration,DATETOCOMPAREHERE) > 30

Everyday I learn something that somebody else already knew

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-05-04 : 10:32:32

quote:
Originally posted by ascipio

ID mobileno dateofregistration registrationstate
44 1674174925 2011-04-18 10:17:30.670 0
45 1677864168 2011-04-18 10:20:22.450 1
46 1677864161 2011-04-18 20:47:35.293 0
47 1674174925 2011-04-19 09:28:55.200 1
48 1674174925 2011-04-19 09:28:56.200 0
49 1674174925 2011-04-19 09:28:57.200 1
50 1674174925 2011-04-18 10:17:30.670 0
51 1677864168 2011-04-18 10:20:22.450 1
52 1677864161 2011-04-20 20:47:35.293 0
53 1674174925 2011-04-22 09:28:55.200 1
54 1674174925 2011-04-28 09:28:56.200 0
55 1674174925 2011-05-28 09:28:57.200 1

my problem is how to count the row with the following conditions
1. registrationstate=1 and datediff(day,dateofregistration)>30 for the individual mobile no.

Here the result will be 1

Please show us the ~~record~~ row that will be counted on this conditions because I can't see a row older than 30 days...

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources