SQL Function Help

[pbeeke]

Hiya chaps

I have a table called movements which tracks the issuing of safety wear etc.
movement_id int (identity yes)
emp_no char(8)
clothing_id int (fk)
create_dte smalldatetime
create_by_emp_n o char(8)
clothing_price decimal

I need to create a function that will check first that a user has or has not received an item (param clothing_id)
if they have not then insert a new entry.
else
If they have had an item that matches the clothing_id param - check if it is within one year (if it is then do not insert) if the user has had this item and it was longer than a year insert the item.

Ideally I would like the function to return a 1 if a new record was inserted (for the first time)

a 2 if a record was inserted(user has had this item longer than a year ago)

a 3 if user has this item within one year (no record inserted)

Dont know if this is feasable as I am new to transact sql. Thanks

[SharpDeveloper]

Hello

Please find the draft copy of the function. Check the syntax as well.

CREATE FUNCTION HasUserReceived Cloth(@clothing _id NUMBER)
RETURNS int
AS
BEGIN
DECLARE @ClothCounter int
DECLARE @mORETHANYEAR INT
DECLARE @RETURN_VALUE INT
--Check in the movements table for this clothing_id
SELECT COUNT(clothing_ id) INTO @ClothCounter
FROM MOVEMENTS
WHERE clothing_id = @clothing_id

--user has not received
IF @ClothCounter=0 THEN
INSERT INTO MOVEMENTS() VALUES()--USE VALID INSERT STATEMENT
SET @RETURN_VALUE=1
ELSE IF @ClothCounter>0 THEN
--Check user has had this item longer than a year ago
SELECT COUNT(clothing_ id) INTO @mORETHANYEAR
FROM MOVEMENTS
WHERE clothing_id = @clothing_id AND
DATEPART(yy,cre ate_dte) = DATEPART(yy,GET DATE)
--If yes then insert into table
IF @mORETHANYEAR=0 THEN
INSERT INTO MOVEMENTS() VALUES()--USE VALID INSERT STATEMENT
SET @RETURN_VALUE=2
ELSE
SET @RETURN_VALUE=3
END IF
END IF
RETURN(@RETURN_ VALUE)
END


Hope this helps.

Let me know if you need any help.

Hiya chaps

I have a table called movements which tracks the issuing of safety wear etc.
movement_id int (identity yes)
emp_no char(8)
clothing_id int (fk)
create_dte smalldatetime
create_by_emp_n o char(8)
clothing_price decimal

I need to create a function that will check first that a user has or has not received an item (param clothing_id)
if they have not then insert a new entry.
else
If they have had an item that matches the clothing_id param - check if it is within one year (if it is then do not insert) if the user has had this item and it was longer than a year insert the item.

Ideally I would like the function to return a 1 if a new record was inserted (for the first time)

a 2 if a record was inserted(user has had this item longer than a year ago)

a 3 if user has this item within one year (no record inserted)

Dont know if this is feasable as I am new to transact sql. Thanks