SQL Problem with datediff updating second table.

Question

0.00/5 (No votes)

See more:

I to two tables in SQL Server 2008. One is "Customers" and "CustomerLastAction"
CustomerLastAction has two columns: custID and LastActionDate.
Customers have a columns call "Status", etc. 0=Customer is Active, 1=customer Is not Active.....

When LastActionDate in CustomerLastAction is older than 1 year, update status in customers table to not active

ALTER PROCEDURE [dbo].[uspUpdateStatus]
AS
BEGIN
SET NOCOUNT ON

WHILE @@ROWCOUNT <> 0

BEGIN

UPDATE Customers
SET Customers.custStatus = 1
WHERE Customers.custID = (
SELECT cla.custID
FROM CustomerLastAction cla
WHERE (DATEDIFF(YEAR, cla.LastActionDate, GETDATE())) > 1
)
CONTINUE

END
END

This is not working...what am i missing...

Okay...This works...Took awhile..LOL

ALTER PROCEDURE [dbo].[uspUpdateStatus]
AS
BEGIN
SET NOCOUNT ON
--
DECLARE @ID INT

DECLARE LastAction CURSOR
FOR
SELECT cla.custID
FROM CustomerLastAction cla
WHERE (DATEDIFF(DAY, cla.LastActionDate, GETDATE())) > 365

OPEN LastAction

FETCH NEXT FROM LastAction
INTO @Id

WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE Customers
SET Customers.custStatus = 0
WHERE Customers.custID = @ID
FETCH NEXT FROM LastAction
INTO @Id
END
CLOSE LastAction
DEALLOCATE LastAction
END

Posted 9-May-11 10:15am

VB Overlord

Updated 9-May-11 14:03pm

v6

Add a Solution

Comments

wizardzz 9-May-11 16:41pm

Post error, or at least try breaking down the main query into 2 seperate queries to see what is broken.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Accepted Answer · 2011-05-09T10:41:00

DATEDIFF() function in T-SQL... read more at: http://msdn.microsoft.com/en-us/library/ms189794.aspx[^]

Try the following code with any date (@date1 variable) to understand returning values:

SQL

DECLARE @date1 as DateTime
DECLARE @date2 as DateTime

SET @date1 = '2010-08-05';
SET @date2 = GETDATE()

--return days diff...
SELECT CONVERT(INT,@date1 - @date2) AS RetVal

--return year diff...
SELECT DATEDIFF(yyyy,@date1 , @date2) AS RetVal

Simon_Whale · Accepted Answer · 2011-05-09T11:03:00

Without knowing more about what "This is not working" really means. Personally I would change your datadiff statement to the following

SQL

SELECT cla.custID
FROM CustomerLastAction cla
WHERE (DATEDIFF(day, cla.LastActionDate, GETDATE())) > 365

there are also many other solutions in the this ASP.NET thread on datadiff[^]

SQL Problem with datediff updating second table.

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0