计算C＃和SQL Server存储过程中的运行总计 [英] Calculate running total in C# and SQL server stored procedure

问题描述

I have a table Laptops and with stored procedure I want calculate running total using INSERT AND UPDATE.
I'm trying to calculate running total on the following way. 

ALTER PROCEDURE [dbo].[spInsert_Inventory]
(
    @Brand Varchar(50),
    @Series Varchar(50),
    @Model Varchar(50),
    @Ram int,
    @HDD int,
    @Qty int,
    @Price int
)
AS
BEGIN
    IF NOT EXISTS (SELECT 1 FROM Laptops
        WHERE Brand=@Brand
            AND Series=@Series
            AND Model=@Model
            AND ram=@Ram 
            AND hdd=@HDD 
    )
    BEGIN
	DECLARE @totale int=0,@ID int
	SELECT ID_Lap=@ID FROM Laptops
	set @totale= (@Price)*(@Qty)
	INSERT INTO Laptops VALUES (@Brand,@Series,@Model,@Ram,@HDD,isnull(@Qty,0),(isnull(@Price,0)),@totale)
    END
ELSE
 BEGIN
SET @totale = (SELECT SUM(@Price)*SUM(@Qty)FROM Laptops WHERE ID_Lap=@ID)

        UPDATE Laptops set qty=isnull(@Qty,0)
     , Price = (isnull(@Price,0)),Total=@totale
        WHERE Brand=@Brand 
            AND Series=@Series
            AND Model=@Model 
            AND ram=@Ram 
            AND hdd=@HDD			
    END
END

Now, when I try insert new record and calculate running total my result is fine but when using update running total is not change column Total.  

我的尝试：

What I have tried:

Desired output:

Brand Series Model Ram HDD Qty Price Total
 AAA   SAS   DSS   200 25   3   3    9  
 BBB   GFG    KHH   50  65  5   20   100
 AAA   SAS   DSS   200 25  22   3    75 
 BBB   GFG    KHH   50  65  5   10   150
 BBB   GFG    KHH   50  65  2   4    158
 AAA   SAS   DSS   200 25   2   5    85

推荐答案

您的交易需要日期和时间。



然后，您可以使用排序和截止日期运行总计到目前为止的查询。



（您的ID和更新逻辑是有缺陷的，因为你似乎有重复的记录。）

You need a "date and time" on your "transactions".

You can then run a query for "totals to date" using a sort and a cutoff date.

(Your "ID" and update logic is flawed since you appear to have "duplicate" records).

这篇关于计算C＃和SQL Server存储过程中的运行总计的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！