if not exists problem - 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)

if not exists problem

Author

Topic

akpaga
Constraint Violating Yak Guru

331 Posts

Posted - 2010-05-19 : 15:32:34

i have a problem with the table customers again.

cust_nbr,cust_name,sigin,signin_date are the fields

if a customer signs in the fields would be

ex:1234,john,1,04/04/2010

if he signs out
1234,john,0,04/08/2010

now since the the latest record for john is that he has opted out
i should not create a new record if john opts out again but should create a new record if he opts in. So vice versa... i mean based on the latest record i should not let him do the same action again but should let him do the other

i was using if not exists ()
insert....

but not able to figure out how... thank you in advance

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-05-19 : 16:21:56

You can use this to find out the latest action of the particular user...and then decide your action based on the signin value returned.

select cust_nbr,cust_name,signin,signin_date 
from
(
select 
row_number() over(partition by cust_nbr order by signin_date desc) as seq
, * 
from customers
) t
where t.seq = 1 and t.cust_nbr = @cust_nbr

akpaga
Constraint Violating Yak Guru

331 Posts

Posted - 2010-05-20 : 13:32:56

quote:
Originally posted by vijayisonly

You can use this to find out the latest action of the particular user...and then decide your action based on the signin value returned.
select cust_nbr,cust_name,signin,signin_date
from
(
select
row_number() over(partition by cust_nbr order by signin_date desc) as seq
, *
from customers
) t
where t.seq = 1 and t.cust_nbr = @cust_nbr

thanks vijay

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-05-20 : 14:14:36

Np. You're welcome.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources