Post reply

Running Totals Query

  • September 28, 2010 at 12:49 pm

    #229854

    Hello Experts,

    I have a db table named InventoryLookup. I store inventory transaction summaries in this table.

    CREATE TABLE [dbo].[InventoryFact](

    [StyleCode] [varchar](23) NULL,

    [WarehouseID] [char](4) NULL,

    [DateID] [smalldatetime] NULL,

    [Quantity] [int] NULL

    ) ON [PRIMARY]

    GO

    INSERT INTO InventoryFact VALUES ('RW900505002493','1001','18/09/2009',23)

    INSERT INTO InventoryFact VALUES ('RW900505002493','1001','19/09/2009',-1)

    INSERT INTO InventoryFact VALUES ('RW900505002493','1001','22/09/2009',-1)

    INSERT INTO InventoryFact VALUES ('RW900505002493','1001','29/09/2009',-1)

    How can I apply a running total method to this table?

    I mean my desired resultset should be like below:

    'RW900505002493' 1001 18/09/2009 23

    'RW900505002493' 1001 19/09/2009 22

    'RW900505002493' 1001 22/09/2009 21

    'RW900505002493' 1001 29/09/2009 20

    Regards

  • September 28, 2010 at 12:52 pm

    #1228618

    Please read this article[/url] - just be sure to follow all the rules in using this form of the update statement - they must be followed or you can get erroneous results!

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • September 28, 2010 at 1:08 pm

    #1228623

    Thank you very much for your great comment.

    Is it really such a complex query issue to get running totals of a table?

    Regards

  • September 28, 2010 at 2:19 pm

    #1228654

    erdem1973 (9/28/2010)

    Thank you very much for your great comment.

    Is it really such a complex query issue to get running totals of a table?

    Regards

    You're welcome.

    Until Microsoft decides to fully implement the window functions, yes it is this complex.

    You might want to visit this editorial

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply