Professional Web Applications Themes

Homework on tough Running Total, this one needs an expert - Microsoft SQL / MS SQL Server

Dear T-SQL gurus, I can build a working running total for a table with one or more row columns (thanks a great Advanced T-SQL book I have), however, the running total will not work if the record structure is not fixed. If I have a record yesterday for Column A=1 and Column B =2 with value 10 in column C but no record today, there is a problem. It would work if I create a fake record with 0 but it would not if there is no record at all. Creating faked records is not really an option, I pull ...

  1. #1

    Default Homework on tough Running Total, this one needs an expert

    Dear T-SQL gurus,
    I can build a working running total for a table with one or more row columns
    (thanks a great Advanced T-SQL book I have), however, the running total will
    not work if the record structure is not fixed.
    If I have a record yesterday for Column A=1 and Column B =2 with value 10 in
    column C but no record today, there is a problem.
    It would work if I create a fake record with 0 but it would not if there is
    no record at all.
    Creating faked records is not really an option,
    I pull data from Oracle to SQL Server 2000 and the query will return only
    existing records (lot of).
    The problem is that the running total should show the same total at any date
    than the sum of the source column up to this date and for this combination
    of labels.
    I have an experimental query bellow to illustrate the problem. It will creat
    the table, populate it and query it.
    Note that I tried to use ysis Services and got it to work with just a
    minor intermittent bug, however, this is for the OLAP ng and this is not the
    solution I need.
    I also tried by curiosity to build this at the Oracle query level with a
    Rollup operator, but I have the exact same problem....
    Thanks in advance to take the time to review this short sample.
    Philippe

    -- Create table test in selected database
    if exists
    (select * from dbo.sysobjects
    where id = object_id(N'[dbo].[Tough_RT]')
    and OBJECTPROPERTY(id, N'IsUserTable') = 1)

    drop table [dbo].[Tough_RT]
    GO

    CREATE TABLE [dbo].[Tough_RT] (
    [DAY] [datetime] NULL ,
    [CLASS] [varchar] (7) NULL ,
    [REGION] [varchar] (3) NULL ,
    [Part] [varchar] (2) NULL ,
    [BILL] [money] NULL
    ) ON [PRIMARY]
    GO

    INSERT INTO Tough_RT
    VALUES ('6/29/2003', 'Dist', 'AMR', 'P1', 10)
    INSERT INTO Tough_RT
    VALUES ('6/29/2003', 'CEM', 'AMR', 'P1', 10)
    INSERT INTO Tough_RT
    VALUES ('6/29/2003', 'CEM', 'AMR', 'P2', 10)
    INSERT INTO Tough_RT
    VALUES ('6/29/2003', 'CEM', 'AMR', 'P3', 60)
    INSERT INTO Tough_RT
    VALUES ('6/29/2003', 'CEM', 'EUR', 'P4', 100)
    INSERT INTO Tough_RT
    VALUES ('6/30/2003', 'Dist', 'AMR', 'P1', 10000)
    INSERT INTO Tough_RT
    VALUES ('6/30/2003', 'Dist', 'AMR', 'P2', 10)
    INSERT INTO Tough_RT
    VALUES ('6/30/2003', 'CEM', 'AMR', 'P1', 10)
    INSERT INTO Tough_RT
    VALUES ('6/30/2003', 'CEM', 'AMR', 'P2', 10)
    INSERT INTO Tough_RT
    VALUES ('7/2/2003', 'CEM', 'AMR', 'P3', 600)

    go

    SELECT [DAY], Class, Region, Part, SUM(BILL) AS Bill,
    (
    SELECT SUM(Bill)
    FROM tough_RT b
    WHERE b.Day <= a.Day
    AND b.Class + b.Region + b.part
    = a.Class + a.Region + a.part
    )
    AS R_Total
    FROM Tough_RT a
    GROUP BY [DAY], Class, Region, Part
    ORDER BY [DAY], part + Class + Region + part
    go





    Philippe Cand Guest

  2. #2

    Default Re: Homework on tough Running Total, this one needs an expert

    Hello,
    I could not find better than the bellow code to solve the running total
    problem for specific combinations of columns values not present in all
    distinct occurences of the column against which one compute the running
    total.
    I repost the edited code. It is for Query yser. In the real life, the
    key part would be in a SP. See comment.
    It Works, but I find this very brutal and I believe many developers have to
    solve similar issues. I saw many posts about running totals however no
    silver bullet so far.
    As always, it is not because one think that it should come in standard with
    the software because one think it is obvious that the software will come
    with this feature.

    Here is the working code. Please let me know if you have a more elegant way
    to do this. I would be happy to have a more standard and simple way to
    compute complex Running total.
    Have fun
    Philippe
    ---------
    -- Create table test in selected database
    if exists
    (select * from dbo.sysobjects
    where id = object_id(N'[dbo].[Tough_RT]')
    and OBJECTPROPERTY(id, N'IsUserTable') = 1)

    drop table [dbo].[Tough_RT]
    GO

    CREATE TABLE [dbo].[Tough_RT] (
    [DAY] [datetime] NULL ,
    [CLASS] [varchar] (7) NULL ,
    [REGION] [varchar] (3) NULL ,
    [Part] [varchar] (2) NULL ,
    [ul_Key] [varchar] (250) NULL,
    [BILL] [money] NULL
    ) ON [PRIMARY]
    GO

    -- insert a few values like they could come in real life
    INSERT INTO Tough_RT
    VALUES ('6/29/2003', 'Dist', 'AMR', 'P1', 'DistAMRP1', 10)
    INSERT INTO Tough_RT
    VALUES ('6/29/2003', 'CEM', 'AMR', 'P1', 'CEMAMRP1', 10)
    INSERT INTO Tough_RT
    VALUES ('6/29/2003', 'CEM', 'AMR', 'P2', 'CEMAMRP2', 10)
    INSERT INTO Tough_RT
    VALUES ('6/29/2003', 'CEM', 'AMR', 'P3', 'CEMAMRP3', 60)
    INSERT INTO Tough_RT
    VALUES ('6/29/2003', 'CEM', 'EUR', 'P4', 'CEMEURP4', 100)
    INSERT INTO Tough_RT
    VALUES ('6/30/2003', 'Dist', 'AMR', 'P1', 'DistAMRP1', 10000)
    INSERT INTO Tough_RT
    VALUES ('6/30/2003', 'Dist', 'AMR', 'P2', 'DistAMRP2', 10)
    INSERT INTO Tough_RT
    VALUES ('6/30/2003', 'CEM', 'AMR', 'P1','CEMAMRP1', 10)
    INSERT INTO Tough_RT
    VALUES ('6/30/2003', 'CEM', 'AMR', 'P2','CEMAMRP2', 10)
    INSERT INTO Tough_RT
    VALUES ('7/2/2003', 'CEM', 'AMR', 'P3', 'CEMAMRP3', 600)
    go

    -- now rebuild the missing records with 0, can be a stored proc.
    -- CREATE PROCEDURE [dbo].[Daily_Rebuild_Missing_Items] AS
    set nocount on
    declare Minday as datetime
    declare maxDay as datetime
    declare CurDay as datetime

    set Minday = (select min([DAY] ) from Tough_RT )
    set Maxday = (select max([DAY] ) from Tough_RT )
    set CurDay = Minday

    SELECT DISTINCT Curday as [DAY], CLASS, REGION, Part, 0 as Value, CLASS
    + REGION + Part as ul_Key
    into #temp
    FROM Tough_RT

    UPDATE #temp
    SET [DAY] = CurDay

    while Curday <= Maxday
    begin
    UPDATE #temp
    SET [DAY] = CurDay

    INSERT INTO Tough_RT
    ([DAY], CLASS, REGION, Part, ul_Key, Bill)
    SELECT [DAY], CLASS, REGION, Part, ul_Key, Value
    FROM #temp

    -- loop next
    set CurDay = (select dateadd(day, 1, Curday))
    end
    drop table #temp
    -- end section which place is better in a stored procedure.
    GO



    --- now the query
    SELECT [DAY], Class, Region, Part, SUM(BILL) AS Bill,
    (
    SELECT SUM(b.Bill)
    FROM Tough_RT b
    WHERE b.[Day] <= Tough_RT.[Day]
    AND b.ul_Key = Tough_RT.ul_Key
    )
    AS R_Total
    FROM Tough_RT
    GROUP BY [DAY], Class, Region, Part, ul_Key
    ORDER BY [DAY], Class, Region, Part, ul_Key
    go



    Philippe Cand Guest

Similar Threads

  1. Hm this is gonna be a tough one..
    By Pelleyoo in forum Macromedia Director 3D
    Replies: 8
    Last Post: August 25th, 05:54 PM
  2. tough one IXmlSerializable
    By Random in forum ASP.NET Web Services
    Replies: 0
    Last Post: May 6th, 07:49 PM
  3. Running total in portal
    By Dade in forum FileMaker
    Replies: 3
    Last Post: September 10th, 07:30 AM
  4. Replies: 0
    Last Post: August 9th, 12:04 AM
  5. Replies: 0
    Last Post: August 8th, 06:17 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139