February 26, 2008 at 2:50 am
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_no 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
February 27, 2008 at 9:56 am
hi beeke.
you can't insert/update/delete data with a function. you'll have to use a stored procedure instead. this sample code below should get you started.
create table movements (
movement_id int IDENTITY(1,1) not null,
emp_no char(8),
clothing_id int not null,
create_dte smalldatetime default getdate(),
create_by_emp_no char(8) null,
clothing_price decimal null,
CONSTRAINT PK_movements PRIMARY KEY ( movement_id ASC )
)
go
insert into movements (emp_no, clothing_id, create_dte)
select 'SAM', 20, '1/1/2007' union
select 'SALLY', 20, '1/1/2008'
-- seed the table with some old and recent data
go
create procedure uspIssueClothing( @emp_no char(8), @clothing_id int )
as
begin
declare @issueDate smalldatetime
select @issueDate = max(create_dte) from movements
where emp_no = @emp_no and clothing_id = @clothing_id
if (@issueDate > dateadd( year, -1, getdate())) return 3 -- less than one year so do nothing
insert into movements (emp_no, clothing_id)
select @emp_no, @clothing_id
-- join to clothing table to get current price
-- pass in create_by_emp_no
if (@issueDate is null) return 1 -- @issueDate will be null if no prior record
return 2 -- @issueDate was one year or older
end
go
declare @ret int
exec @ret = uspIssueClothing 'JOHN', 22
print @ret
exec @ret = uspIssueClothing 'SAM', 22
print @ret
exec @ret = uspIssueClothing 'SALLY', 22
print @ret
exec @ret = uspIssueClothing 'SAM', 20
print @ret
exec @ret = uspIssueClothing 'BOB', 20
print @ret
exec @ret = uspIssueClothing 'SALLY', 20
print @ret
select * from movements
February 27, 2008 at 9:59 am
Thank you very much. Much appreciated
Regards Phill
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply