Datediff between two columns - 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

Datediff between two columns

Author

Topic

oap
Yak Posting Veteran

60 Posts

Posted - 2012-10-23 : 06:09:30

Hi.

I have a table named "trial". It allows trial access to a site until an expiration date is reached.

username (varchar)// issued (datetime) // expires (datetime)
example:
Joe // 2012-10-23 05:28:27.000 // 2012-11-05 05:28:27.000

Usernames are added to 'username' column, todays date to 'issued' and a future date to the expires column. The expiration will be from 1-14 days only.

When an expiration date has been reached I need to do two things:
a) delete the row
b) revert the member's access to '1'

for A) DELETE from trial WHERE datediff(d,issued,expires) > 0
(edit: on second thought it may be < 0 ?)

for b) UPDATE access set level = '1' where username = 'Joe'

I'm not sure how to combine this into one command if at all possible. I believe it would call for an INNER JOIN but to combine a DELETE complicates things.

If I had to make a poorly educated guess at the code which won't work, I'd guess:

DELETE from trial WHERE (update access set level = '1' where trial.username = access.username)

Any assistance would be appreciated.

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2012-10-23 : 06:24:34

Just use 2 statements, no need to combine them. I did a quick trial, but got an interesting message from SQL Server:

Msg 10727, Level 15, State 1, Line 25
A nested INSERT, UPDATE, DELETE, or MERGE statement is not allowed on either side of a JOIN or APPLY operator.

How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

oap
Yak Posting Veteran

60 Posts

Posted - 2012-10-23 : 06:35:24

I've no idea how to.

My best guess would be

"update access set a.level = '1' a INNER JOIN trial b ON a.username = b.username where datediff(d,issued,expires) > 0"

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-10-23 : 06:43:41

delete trial where expires > getdate()

update a
set level='1'
from access a
where not exists(select * from trial t where t.username = a.username)

Too old to Rock'n'Roll too young to die.

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2012-10-23 : 06:48:07

Here is a sample creating some temp tables, filling with some data, and an update for it.

create table #access (username  varchar(10), [level] int)
create table #trial (username varchar(10), issued  datetime, expires  datetime)

insert into #access 
	values ('Tim',2)
		,('Fred',2)
		,('John',2)

insert into #trial 
		values ('Tim','20120101','20120112')
		,('Fred','20120101','20120201')

select * from #access 
select * from #trial

update 
	a 
set 
	[level] = '1'
FROM
	#access a 
INNER JOIN 
	(SELECT 
		username		
	FROM
		#trial  
	where 
	datediff(d,issued,expires) > 13
	) b
ON 
	a.username = b.username 


select * from #access

How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-10-23 : 07:43:49

How you are getting 'expiration date reached users' with this condition ?
datediff(d,issued,expired) < 0

Your Inner query ( i. e. SELECT username FROM trial where datediff(d,issued,expired) < 0) never give a single record.

--
Chandu

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-10-23 : 09:51:32

Case-1:
If issueDate is today then ExpireDate might be greate than the today date...........

It is your input data... right??

If my understanding is correct, you never get records for datediff(d,issue, expired)<0

Case-2:
Try this once.......

In the case of issueDate > expireDate,

strSQL = "update a set userlevel = '3' FROM users WHERE username IN (SELECT username FROM trial where datediff(d,issued,exprdate) < 0)"

--
Chandu

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-10-23 : 10:18:21

It has NOTHING to do with the issue date.
There is an expired date.
So you have to check if the expired date has reached the actual date - nothing more and nothing less.

Some entries above I have given a solution that should work.
Maybe you can give it a shot instead of ignoring it?

Too old to Rock'n'Roll too young to die.

oap
Yak Posting Veteran

60 Posts

Posted - 2012-10-23 : 10:27:17

Bandi and Webfred were correct, the code was not correct as neither date fields would ever change. Only the real time clock would change.

This code works perfectly now. Thank you to everyone who helped. :)

update a set userlevel = '1' FROM users a INNER JOIN (SELECT username FROM trial where datediff(d,expire,getdate()) < 0) b ON a.username = b.username

delete trial where exprdate < getdate()

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources