DateDiff 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

General SQL Server Forums

New to SQL Server Programming

DateDiff Problem

Author

Topic

WelshPunk
Yak Posting Veteran

67 Posts

Posted - 2008-04-29 : 11:54:45

Hi Everyone
I have a table that stores information that is used to help monitor the issuing of safety clothing. I have a Transact SQL procedure that checks if a user has had an item within the last year. THe procedure returns values:

1 the item does not exist for any user so insert
2 the item exists but was issued more than a year ago so insert
3 the iem exists but was issued within one year so do not insert

I had some help from this forum initially and I thought that I had finally solved this problem but it still is not quite right. Please could someone take a quick look and point me in the right direction

CREATE PROCEDURE [DBO].[check_values]
@param_clothing_id int,
@empNo char(8),
@createdBy char(8),
@price decimal,
@qty int,
@returnVal int=0 OUTPUT
AS

SET NOCOUNT ON

/*First check if the record already exists */
IF EXISTS (SELECT * FROM movement WHERE emp_no =@empNo AND clothing_id=@param_clothing_id )

BEGIN
/*Record already exists - So now check if it is less than a year old */

IF EXISTS (SELECT * FROM movement WHERE emp_no =@empNo AND clothing_id =@param_clothing_id AND DATEDIFF(dd, create_dte, getdate())>366)

BEGIN

/*Record is less than a year old so return a 3*/

SELECT @returnval=3
END
ELSE

/*Record older than a year so insert the record and return a 2 */

BEGIN

INSERT INTO movement
(emp_no,
clothing_id,
qty,
create_by_emp_no,
clothing_price)

VALUES(
@empNo,
@param_clothing_id,
@qty,
@createdBy,
@price)

SELECT @returnval=2
END

END
ELSE

BEGIN
/*Record does not exist at all - So insert the new record and return a 1 */

INSERT INTO movement
(emp_no,
clothing_id,
qty,
create_by_emp_no,
clothing_price)
VALUES(
@empNo,
@param_clothing_id,
@qty,
@createdBy,
@price)

SELECT @returnval=1
END
GO

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-29 : 12:00:18

What's the error you're getting?

WelshPunk
Yak Posting Veteran

67 Posts

Posted - 2008-04-29 : 12:02:44

Sorry no error but I am getting a return code of 2 when it should be 3 i.e. it is allowing new records to be created that are less than a year old

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-29 : 12:08:15

quote:
Originally posted by WelshPunk

Sorry no error but I am getting a return code of 2 when it should be 3 i.e. it is allowing new records to be created that are less than a year old

it will as per your logic

IF EXISTS (SELECT * FROM movement WHERE emp_no =@empNo AND clothing_id =@param_clothing_id AND DATEDIFF(dd, create_dte, getdate())>366)

BEGIN

/*Record is less than a year old so return a 3*/


SELECT @returnval=3
END
ELSE

/*Record older than a year so insert the record and return a 2 */

BEGIN


INSERT INTO movement
(emp_no,
clothing_id,
qty,
create_by_emp_no,
clothing_price)

VALUES(
@empNo,
@param_clothing_id,
@qty,
@createdBy, 
@price)

SELECT @returnval=2
END

this condition checks whether there are records existing which is more than 366 days old and if yes, it will return 3 and if not (record within 1 year) it will insert and return 2.
As per your current explanation the if condition should be checking condition <366

WelshPunk
Yak Posting Veteran

67 Posts

Posted - 2008-04-29 : 12:45:57

Doh!

Thanks a lot. Its a good job the SQL Team are here!

Phill

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources