Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2011
    Posts
    3

    Unanswered: Running Total Stored Procedure

    Hey! 8)

    I'm trying to make a stored procedure that updates a table in my database with no luck at all. I can run a select & pull the data I need with no issues but I can't get it to update on it's own.
    I'm using SQL 2008 R2. This all goes into a custom form for another piece of software, not unlike VB, so there's a parent table as well.

    Here's my table(s):

    /*PARENT TABLE*/
    create table dbo.USR_WH_CNT
    (REF_NO T_COMMNT not null,
    LST_MAINT_DT T_DT null,
    LST_MAINT_USR_ID T_USR_ID null,
    ROW_TS timestamp null,
    COUNTDAT T_DT null,
    constraint PK_USR_WH_CNT primary key (REF_NO)
    )

    /*CHILD TABLE*/
    create table dbo.USR_WH_CNT_LINES
    (
    REF_NO T_COMMNT not null,
    CNT T_INT not null,
    LST_MAINT_DAT T_DT null,
    LST_MAINT_USR_ID T_USR_ID null,
    ROW_TS timestamp null,
    ITEM_NO T_COMMNT null,
    TOTALQTY T_QTY null,
    SEQ_NO T_SEQ_NO not null,
    constraint PK_USR_WH_CNT_LINES primary key (REF_NO, SEQ_NO),
    constraint FK_USR_WH_CNT_LINES_WH_CNT foreign key (REF_NO) references dbo.USR_WH_CNT (REF_NO)
    )

    USE [test2]
    GO
    /****** Object: StoredProcedure [dbo].[USR_UPDATE_TOTAL] Script Date: 11/30/2011 15:09:07 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    ALTER procedure [dbo].[USR_UPDATE_TOTAL]
    @REF_NO varchar(25),
    @qty varchar(15),
    @totalqty varchar(25)
    AS
    SELECT REF_NO,
    sum(cNT) as totalqty
    FROM USR_WH_CNT_LINES
    where REF_NO = REF_NO
    AND CNT = CNT
    group by REF_NO

    It's supposed to update the TOTALQTY column under the child table of USR_WH_CNT_LINES every time the CNT column under that same table is updated. The TOTALQTY needs to just be a running total based on (or grouped by) the REF_NO.

    I'm super stumped on this so any assistance would be great!

    Thanks

    -Mike-

  2. #2
    Join Date
    Nov 2011
    Posts
    8
    1) Why is TOTALQTY in the child table; isn't this a characteristic of the parent?
    2) Your where clause is a tautology.
    3) Why are you passing in parameters to your sproc and then never using them?

    UPDATE c
    SET TOTALQTY = a.SumCnt
    FROM dbo.USR_WH_CNT_LINES c
    inner join
    (
    SELECT sum(CNT) SumCnt
    FROM dbo.USR_WH_CNT_LINES
    WHERE REF_NO = @REF_NO
    ) a
    ON c.REF_NO = @REF_NO

  3. #3
    Join Date
    Nov 2011
    Posts
    3

    Stored Procedure

    Ah. That makes a lot of sense. I'm really new to stored procs & triggers so I'm kinda fumbling through this first one I'm working on. I think it actually needs to be a trigger so it'll fire on it's own whenever there's an update to the CNT field & still group it by the same REF_NO.
    So what you're saying is I should move the TOTALQTY to the parent table & throw in an UPDATE as I can see what you're getting at in not sending params but not doing anything with them.
    I'll try to make that change to the tables & see if it works. I had a view that was doing it with no issues but I couldn't pass an aggregate function to the interfacing software I'm using so setting it up with a SUM(CNT) was failing. I'll repost if it works or not.

    Thanks!

    -Mike-

  4. #4
    Join Date
    Nov 2011
    Posts
    8
    It does sound like a trigger is the direction to go. The basic logic to perform the work would be the same but you might be adding some surronding logic to determine if the desired column was updated.

  5. #5
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    This is one way to implement the trigger. It will (re)calculate the TotalQty of the parent record each time at least one of it's children has changed, either by an INSERT, an UPDATE or a DELETE.

    The TotalQty will be recalculated after each and every UPDATE, even when Child.Qty didn't change. You would need to add extra code to exclude those events.
    Code:
    DROP table dbo.Child
    create table dbo.Child(
    	ParentId	INT	not null,
    	SEQ_NO		INT	not null,
    	aColumn		INT,
    	Qty		INT	not null,
    	constraint PK_Child primary key (ParentId, SEQ_NO)
    )
    
    /*PARENT TABLE*/
    DROP table dbo.Parent
    create table dbo.Parent (
    	ParentId	INT	not null,
    	TotalQty	INT	NOT null	DEFAULT 0,
    	constraint PK_Parent primary key (ParentId)
    )
    
    ALTER table dbo.Child
    	ADD constraint FK_child_parent foreign key (ParentId) 
    		references dbo.Parent (ParentId)
    GO
    
    CREATE TRIGGER ChildTrigger
    ON Child
    FOR INSERT, UPDATE, DELETE
    AS
    BEGIN
    	WITH Changed AS	-- get all DISTINCT ParentId's whose Child records 
    			-- have changed somehow (either by an INSERT, UPDATE 
    			-- or DELETE)
    	(SELECT inserted.ParentId FROM inserted
    		UNION
    	SELECT deleted.ParentId FROM deleted
    	),
    	Summary AS	-- recalculate TotalQty of all Parents whose Children 
    			-- have changed
    	(SELECT C.ParentId, SUM(C.Qty) AS SumQty
    	FROM Changed 
    		INNER JOIN dbo.Child as C ON
    			Changed.ParentId = C.ParentId
    	GROUP BY C.ParentId
    	)
    	UPDATE U
    	SET U.TotalQty = COALESCE(Summary.SumQty, 0)
    	FROM dbo.Parent AS U
    		INNER JOIN Changed ON
    			U.ParentId = Changed.ParentId	
    		LEFT OUTER JOIN Summary ON
    			U.ParentId = Summary.ParentId
    
    END
    GO
    
    INSERT INTO dbo.Parent (ParentId) VALUES
    (1) , (2), (3), (4), (5), (6)
    
    select * from Parent
    select * from Child
    
    INSERT INTO dbo.Child (ParentId, SEQ_NO, Qty) VALUES
    (1, 1, 0),
    (1, 2, 10),
    (1, 3, 20),
    (1, 4, 30),
    (1, 5, 40),
    
    (2, 1, 0),
    (2, 2, 1),
    (2, 3, 2),
    (2, 4, 3),
    (2, 5, 4)
    
    select * from Parent
    select * from Child
    
    DELETE FROM dbo.Child WHERE ParentId = 1 AND SEQ_NO < 3
    
    select * from Parent
    select * from Child
    
    UPDATE dbo.Child
    SET aColumn = 10,
    	Qty = Qty * 20
    WHERE ParentId <= 2 AND SEQ_NO <= 3
    
    select * from Parent
    select * from Child
    Last edited by Wim; 12-02-11 at 09:29. Reason: changed "INNER JOIN Summary ON" to "LEFT OUTER JOIN Summary ON"
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  6. #6
    Join Date
    Nov 2011
    Posts
    3

    Stored Proc

    Wow Wim! That trigger works great. Thanks a ton! I've been struggling with this one for a week. For some reason Joins don't click in my brain right so I'm still wrapping my head them. One day that light bulb will go off heh.
    I threw that trigger into my custom form & rearranged the title names then tested it out & voila!! Works awesome!
    Thanks again!


    -Mike-

  7. #7
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    For some reason Joins don't click in my brain right so I'm still wrapping my head them.
    Don't feel bad about that. You should see my first attempt to implement this trigger. It was harder than I first thought it would be.

    When you write a trigger, you must make sure it will not only work for 1 INSERT, UPDATE or DELETE, but also when many records get changed with one command.

    Thanks for the beer! That makes 11 earned beers so far
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •