February 23, 2005 at 5:20 am
Hello,
I would like to create a stored procedure that will add a new record to a table.But one of the columns of the table will SUM of the other columns with a given criteria.Code is like this:
----------
UPDATE MYTABLE
set LASTDEBT=(SELECT SUM(LASTDEBT) FROM MYTABLE) + (@ENTERED_QTY* @ENTERED_UNIT/1000) + (@ENTERED_QTY * @ENTEREDISC)
from MYTABLE where ACCOUNT_NO=@ACCOUNT_NO and fisno=@FISNO and myDATE=@myDATE AND LINENO=@LINENO
---------------
But there are some problems with this code.
I would like to add just a new record that will calculate the new debt information as a column.
How can I do this?Please help
February 23, 2005 at 5:36 am
Now, if I understand this correct:
UPDATE MYTABLE set LASTDEBT=LASTDEBT + (@ENTERED_QTY* @ENTERED_UNIT/1000) + (@ENTERED_QTY * @ENTEREDISC) from MYTABLE where ACCOUNT_NO=@ACCOUNT_NO and fisno=@FISNO and myDATE=@myDATE AND LINENO=@LINENO
However, this won't add a new row, but rather update already existing column(s) that meet that criteria.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
February 23, 2005 at 6:01 am
Thank for your attention.
The original code is like this:
------------------------------------------------------------------------
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER PROC ml_goldEntrance2
@FISNO int,
@HESAP_NO int,
@TARIH smalldatetime,
@SATIRNO int,
@STOK_KODU varchar(15),
@STOK_KODU2 varchar(15),
@GIRILEN_MIKTAR decimal(9),
@GIRILEN_MILYEM int,
@GIRILENISCILIK float
AS
BEGIN TRAN
INSERT INTO Islem_ana (fisno,hesap_no,tarih,satirno,islem_kodu,stok_kodu,stok_kodu2,miktar,tutar,hasborc)
VALUES(@FISNO,@HESAP_NO,@TARIH,@SATIRNO,'AG',@STOK_KODU,@STOK_KODU2,@GIRILEN_MIKTAR,(@GIRILEN_MIKTAR*@GIRILENISCILIK)
UPDATE Islem_ana
set HASBORC=(SELECT SUM(HASBORC) FROM Islem_ana) + (@GIRILEN_MIKTAR * @GIRILEN_MILYEM/1000) + (@GIRILEN_MIKTAR * @GIRILENISCILIK)
from Islem_ana where HESAP_NO=@HESAP_NO and fisno=@FISNO and tariH=@TARIH AND satirno=@SATIRNO
COMMIT
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
--------------------------------------------------------------------
How can I update the HASBORC column by SUM function with the give criteria?
February 23, 2005 at 7:07 am
Does this help?
UPDATE Islem_ana
set HASBORC=SELECT SUM(HASBORC) + (@GIRILEN_MIKTAR * @GIRILEN_MILYEM/1000) + (@GIRILEN_MIKTAR * @GIRILENISCILIK)
from Islem_ana where HESAP_NO=@HESAP_NO and fisno=@FISNO and tariH=@TARIH AND satirno=@SATIRNO
Might need some parenthesis somewhere.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
February 23, 2005 at 7:20 am
OK...You are exactly right...I have reorganized parantheses and problem solved...
Thank you very much..
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply