Click here to Skip to main content
15,123,118 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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. 

SQL
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
Posted
Updated 31-Jul-18 15:53pm
v2
Comments
CHill60 30-Jul-18 18:33pm
   
Give some sample data for the table Laptops - it's not clear what you are trying to do
Santosh kumar Pithani 31-Jul-18 3:05am
   
your update query is fine but check your datatypes and use OUTPUT INSERTED.qty,INSERTED.totale in your update query to checking purpose.
Mike V Baker 31-Jul-18 11:37am
   
I'm not sure I get this.. "SELECT SUM(@Price)*SUM(@Qty)FROM Laptops WHERE ID_Lap=@ID" isn't selecting anything from Laptops. It's calculating the passed in params, no? What this appears to be doing is setting the totale to the sum of those values based on the number of those items found in the DB regardless of what price and qty might be in those records.
If you do change it so that it does select from the Laptops records then you should remember that SUM(price)*SUM(qty) does not equal SUM(price * qty). For example if you have two records, qty 5 price $100 and qty 6 price $1, then 5 * 100 + 6 * 1 <> 11 * 101
The table of desired output appears to be gathering data based on Brand+Series+Model (and perhaps Ram+HDD) but the WHERE clause is using ID_Lap. Is the ID_Lap a composite key of those items? If not then you're only getting one record rather than all records with those features.

1 solution

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).
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900