Professional Web Applications Themes

Incrementing a value on a rolling basis - Microsoft SQL / MS SQL Server

Hi, I'm thinking this should be easy but I can't figure out a way to do this without using DTS, SPROC or trigger. I have a table that contains 365 records each one with an amount of meters produced for that day (a record for each day of the year). I would like to create a view that displays each record with a rolling total to date. i.e week 1 (record 1) might have 1000 meters, week 2 (record 2) might be 1500 meters so the rolling total would be 2500 meters, week 3 (record 3) might be 1250 meters ...

  1. #1

    Default Incrementing a value on a rolling basis

    Hi,

    I'm thinking this should be easy but I can't figure out a way to do
    this without using DTS, SPROC or trigger. I have a table that contains
    365 records each one with an amount of meters produced for that day (a
    record for each day of the year).

    I would like to create a view that displays each record with a rolling
    total to date. i.e week 1 (record 1) might have 1000 meters, week 2
    (record 2) might be 1500 meters so the rolling total would be 2500
    meters, week 3 (record 3) might be 1250 meters so the rolling total
    for record 3 would be 3750.

    Any help would be greatly appreciated.

    Jim
    Jim Guest

  2. #2

    Default Re: Incrementing a value on a rolling basis

    CREATE TABLE MeterProduction (proddate DATETIME PRIMARY KEY, metercount
    INTEGER NOT NULL)

    INSERT INTO MeterProduction VALUES ('20030102',1000)
    INSERT INTO MeterProduction VALUES ('20030103',500)
    INSERT INTO MeterProduction VALUES ('20030110',999)
    INSERT INTO MeterProduction VALUES ('20030117',1234)

    SELECT CEILING(DATEDIFF(DAY,'20030101',proddate)/7.0) AS week_number,
    MIN(proddate) AS from_date,
    MAX(proddate) AS to_date,
    SUM(metercount) AS weekly_total,
    (SELECT SUM(metercount)
    FROM MeterProduction AS M2
    WHERE M2.proddate
    BETWEEN '20030101' AND MAX(M1.proddate))
    AS running_total
    FROM MeterProduction AS M1
    GROUP BY CEILING(DATEDIFF(DAY,'20030101',proddate)/7.0)
    ORDER BY from_date

    You can set the base date for the weeks (2003-01-01 in this example) to
    whatever you require.

    --
    David Portas
    ------------
    Please reply only to the newsgroup
    --

    "Jim" <jim.holmesdevro-casings.com> wrote in message
    news:68dfae14.0307100558.577ba86eposting.google.c om...
    > Hi,
    >
    > I'm thinking this should be easy but I can't figure out a way to do
    > this without using DTS, SPROC or trigger. I have a table that contains
    > 365 records each one with an amount of meters produced for that day (a
    > record for each day of the year).
    >
    > I would like to create a view that displays each record with a rolling
    > total to date. i.e week 1 (record 1) might have 1000 meters, week 2
    > (record 2) might be 1500 meters so the rolling total would be 2500
    > meters, week 3 (record 3) might be 1250 meters so the rolling total
    > for record 3 would be 3750.
    >
    > Any help would be greatly appreciated.
    >
    > Jim

    David Portas Guest

  3. #3

    Default Re: Incrementing a value on a rolling basis

    Jim

    Take at a look at this. I patched it together for a quick something-to-do
    :-)

    Robert

    if exists (select * from sysobjects where id = object_id(N'[dbo].[stock]')
    and OBJECTPROPERTY(id, N'IsUserTable') = 1)
    drop table [dbo].[stock]
    GO

    CREATE TABLE [dbo].[stock] (
    [rundate] [smalldatetime] NOT NULL ,
    [number] [smallint] NOT NULL
    )
    GO

    ALTER TABLE [dbo].[stock] WITH NOCHECK ADD
    CONSTRAINT [PK_stock] PRIMARY KEY CLUSTERED
    (
    [rundate]
    ) WITH FILLFACTOR = 80 ON [PRIMARY]
    GO

    ALTER TABLE [dbo].[stock] WITH NOCHECK ADD
    CONSTRAINT [DF_stock_number] DEFAULT (0) FOR [number]
    GO
    SET NOCOUNT ON
    GO

    INSERT stock VALUES ( '2003-01-01' , 1 )
    INSERT stock VALUES ( '2003-01-02' , 2 )
    INSERT stock VALUES ( '2003-01-03' , 3 )
    INSERT stock VALUES ( '2003-01-04' , 4 )
    INSERT stock VALUES ( '2003-01-05' , 5 )
    INSERT stock VALUES ( '2003-01-06' , 6 )
    INSERT stock VALUES ( '2003-01-07' , 7 )
    INSERT stock VALUES ( '2003-01-08' , 8 )
    INSERT stock VALUES ( '2003-01-09' , 9 )
    INSERT stock VALUES ( '2003-01-10' , 10 )
    INSERT stock VALUES ( '2003-01-11' , 11 )
    GO

    SET NOCOUNT OFF
    GO

    SELECT * FROM stock ORDER BY 1
    GO
    if exists (select * from sysobjects where id =
    object_id(N'[dbo].[RunningTotalView]') and OBJECTPROPERTY(id, N'IsView') =
    1)
    drop view [dbo].[RunningTotalView]
    GO

    SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON
    GO

    CREATE VIEW RunningTotalView
    AS
    SELECT s.*, RunningTotal = ( SELECT SUM(ss.number) FROM stock ss WHERE
    ss.rundate <= s.rundate )
    FROM stock s

    GO
    SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON
    GO

    SELECT * FROM RunningTotalView
    GO






    "Jim" <jim.holmesdevro-casings.com> wrote in message
    news:68dfae14.0307100558.577ba86eposting.google.c om...
    > Hi,
    >
    > I'm thinking this should be easy but I can't figure out a way to do
    > this without using DTS, SPROC or trigger. I have a table that contains
    > 365 records each one with an amount of meters produced for that day (a
    > record for each day of the year).
    >
    > I would like to create a view that displays each record with a rolling
    > total to date. i.e week 1 (record 1) might have 1000 meters, week 2
    > (record 2) might be 1500 meters so the rolling total would be 2500
    > meters, week 3 (record 3) might be 1250 meters so the rolling total
    > for record 3 would be 3750.
    >
    > Any help would be greatly appreciated.
    >
    > Jim

    Robert Ellis Guest

  4. #4

    Default Re: Incrementing a value on a rolling basis

    Here is another solution. You might notice that I didn't hadle situtation
    where you have one week that ends
    at the and of the year.

    Try commenting out part of code (selects that insert data, and part of where
    clause). How do you want to handle those situations?

    Think this approach is better than the first one (Portas) because this one
    doesn't use correlated queries. Did not have time to take a look at the
    second one (Ellis).

    create table test(
    day datetime
    , meters int
    , constraint PK_test PRIMARY KEY(day)
    )

    insert into test (day, meters)
    /*select '2002-12-25', 100
    union all
    select '2002-12-26', 100
    union all
    select '2002-12-27', 100
    union all
    select '2002-12-28', 100
    union all
    select '2002-12-29', 100
    union all
    select '2002-12-30', 100
    union all
    select '2002-12-31', 100
    union all
    */
    select '2003-01-01', 100
    union all
    select '2003-01-02', 100
    union all
    select '2003-01-03', 100
    union all
    select '2003-01-04', 100
    union all
    select '2003-01-05', 100
    union all
    select '2003-01-06', 100
    union all
    select '2003-01-07', 100
    union all
    select '2003-01-08', 100
    union all
    select '2003-01-09', 100
    union all
    select '2003-01-10', 100
    union all
    select '2003-01-11', 100
    union all
    select '2003-01-12', 100
    union all
    select '2003-01-13', 100
    union all
    select '2003-01-14', 100
    union all
    select '2003-01-15', 100
    union all
    select '2003-01-16', 100
    union all
    select '2003-01-17', 100
    union all
    select '2003-01-18', 100
    union all
    select '2003-01-19', 100
    union all
    select '2003-01-20', 100

    select
    t1.year, t1.week, max(t1.meterssum), sum(t2.meterssum)
    from
    (select
    year = datepart(year, t.day)
    , week = datepart(week, t.day)
    , meterssum = sum(t.meters)
    from test t
    group by datepart(year, t.day), datepart(week, t.day)) t1
    join
    (select
    year = datepart(year, t.day)
    , week = datepart(week, t.day)
    , meterssum = sum(t.meters)
    from test t
    group by datepart(year, t.day), datepart(week, t.day)) t2
    on (t1.year = t2.year and t1.week >= t2.week)
    -- or (t1.year = t2.year - 1 and t1.week = 1 and t2.week = 52)
    group by t1.year, t1.week
    order by t1.year, t1.week

    drop table test

    --
    Dean Savovic
    [url]www.teched.hr[/url]


    "Jim" <jim.holmesdevro-casings.com> wrote in message
    news:68dfae14.0307100558.577ba86eposting.google.c om...
    > Hi,
    >
    > I'm thinking this should be easy but I can't figure out a way to do
    > this without using DTS, SPROC or trigger. I have a table that contains
    > 365 records each one with an amount of meters produced for that day (a
    > record for each day of the year).
    >
    > I would like to create a view that displays each record with a rolling
    > total to date. i.e week 1 (record 1) might have 1000 meters, week 2
    > (record 2) might be 1500 meters so the rolling total would be 2500
    > meters, week 3 (record 3) might be 1250 meters so the rolling total
    > for record 3 would be 3750.
    >
    > Any help would be greatly appreciated.
    >
    > Jim

    deeeoo Guest

Similar Threads

  1. Coldfusion MX 5 log rolling
    By Dineker in forum Coldfusion Server Administration
    Replies: 0
    Last Post: October 8th, 09:10 AM
  2. Rolling back Hot Fix 2/CFMXE7
    By LL@Work in forum Coldfusion Server Administration
    Replies: 2
    Last Post: May 31st, 05:56 PM
  3. underline when rolling over link
    By JJohnstone67 in forum Macromedia Dynamic HTML
    Replies: 1
    Last Post: May 16th, 10:59 AM
  4. CFTRANSACTION not rolling back
    By RayBees in forum Coldfusion - Advanced Techniques
    Replies: 1
    Last Post: May 12th, 11:25 PM
  5. rolling your own AOP
    By Volkmann, Mark in forum Ruby
    Replies: 0
    Last Post: August 6th, 07:00 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