Stored Procedure - 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

Stored Procedure

Author

Topic

mangler
Starting Member

4 Posts

Posted - 2011-03-25 : 02:32:47

you have the following structure :
Drugstores(id,name)
Drugs(id,name,stats)
Claims(id,drug_id,price,drugstore_id)
write an after update trigger on table Drugs (or procedure) which writes to the field stats the aggregated data taken from Claims in this form:
(drugstore where that drug was sold,max price,min price)
example :
Drugstore : (1,'GPC'),(2,'PSP')
Drugs : (10,'Analgin',''),(11,'Citramon','')
Claims : (100,10,5.1,1),(101,10,5.2,1)(102,10,5.0,2)
should update Drugs and set stats of Analgin to
('GPC',5.1,5.2)('PSP',5.0,5.0)

can you please help me doing this

CREATE PROCEDURE update_stats
@drug_name varchar(50)
AS
DECLARE @drug_id int, @drugstore_id int, @drugstore_name varchar(50), @min float, @max float
SELECT @drug_id = id FROM Drugs WHERE name = @drug_name;
SELECT DISTINCT @drugstore_id = drugstore_id FROM Claims WHERE drug_id = @drug_id ;
SELECT @drugstore_name = name FROM Drugstores WHERE id = @drugstore_id;
SELECT @max = MAX(price) FROM Claims WHERE drug_id = @drug_id AND drugstore_id = @drugstore_id;
SELECT @min = MIN(price) FROM Claims WHERE drug_id = @drug_id AND drugstore_id = @drugstore_id;
UPDATE Drugs SET stats = ( '(' + @drugstore_name + ',' + CONVERT(VARCHAR(50),@max) + ',' + CONVERT(VARCHAR(50),@min) + ')') WHERE name=@drug_name;

-----------------------------------------------------------------
EXEC update_stats 'Analgin'
my code just writes ('PSP',5.0,5.0) and misses ('GPC',5.1,5.2) plz help

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-03-25 : 03:35:18

post your trigger code here

KH
[spoiler]Time is always against us[/spoiler]

mangler
Starting Member

4 Posts

Posted - 2011-03-25 : 03:39:30

CREATE PROCEDURE update_stats
@drug_name varchar(50)
AS
DECLARE @drug_id int, @drugstore_id int, @drugstore_name varchar(50), @min float, @max float
SELECT @drug_id = id FROM Drugs WHERE name = @drug_name;
SELECT DISTINCT @drugstore_id = drugstore_id FROM Claims WHERE drug_id = @drug_id ;
SELECT @drugstore_name = name FROM Drugstores WHERE id = @drugstore_id;
SELECT @max = MAX(price) FROM Claims WHERE drug_id = @drug_id AND drugstore_id = @drugstore_id;
SELECT @min = MIN(price) FROM Claims WHERE drug_id = @drug_id AND drugstore_id = @drugstore_id;
UPDATE Drugs SET stats = ( '(' + @drugstore_name + ',' + CONVERT(VARCHAR(50),@max) + ',' + CONVERT(VARCHAR(50),@min) + ')') WHERE name=@drug_name;

i'm trying to do it with procedure, but the result is that it writes into stats filed only ('PSP',5.0,5.0), ignorring ('GPC',5.1,5.2), tryied stats = stats + ..... but still doesn't work

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-03-25 : 03:55:09

I thought you are suppose to write a update trigger ?

KH
[spoiler]Time is always against us[/spoiler]

mangler
Starting Member

4 Posts

Posted - 2011-03-25 : 03:57:04