Post reply

Adding data

  • March 21, 2012 at 3:17 am

    #258096

    create table #val

    (

    day int,

    data int

    )

    insert into #val values (1,200)

    insert into #val values(2,400)

    insert into #val values(3,600)

    select * from #val

    daydata

    1200

    2400

    3600

    I want to get the output,

    when day = 1 then 200

    When day = 2 then (200+400)

    When day = 3 then (200+400+600)

    Is anyone please help me on this how to get this output using query.

    Thanks,

    tony

  • March 21, 2012 at 3:27 am

    #1462059

    select val.day,SUM(V.data) from val V join val Val on V.day<=val.day group by val.day,val.day

    Regards

    Guru

  • March 21, 2012 at 3:29 am

    #1462060

    Tony

    Search this site (or the internet) for "running totals", then have a try at writing something. Post back if there's anything you don't understand.

    John

  • March 21, 2012 at 3:34 am

    #1462062

    GuruGPrasad (3/21/2012)

    select val.day,SUM(V.data) from val V join val Val on V.day<=val.day group by val.day,val.day

    That'll work well for three rows, but will get more and more inefficient the more rows you have in your table. Please read this article [/url]for more information.

    John

  • March 21, 2012 at 3:39 am

    #1462063

    Thanks a lot , this works fine.!!

  • March 21, 2012 at 9:51 am

    #1462254

    John Mitchell-245523 (3/21/2012)

    GuruGPrasad (3/21/2012)

    select val.day,SUM(V.data) from val V join val Val on V.day<=val.day group by val.day,val.day

    That'll work well for three rows, but will get more and more inefficient the more rows you have in your table. Please read this article [/url]for more information.

    John

    Tony

    Consider using another method. Here's another great article by Jeff Moden http://www.sqlservercentral.com/articles/T-SQL/68467/[/url]

  • March 21, 2012 at 4:12 pm

    #1462460

    The links already given above will work a great deal better than what I'm about to show, but...

    My wife says I love complicated things, and I'm sure that will show in my script. This is a live database with 1,000 rows of sample data (I only altered customer sensitive information). It gives a running total by state by day of an outstanding balance (dbo.AD = approved amount, dbo.PM = payments made). If you want, you can pick this apart for a working version of a running total.

    I'm sure there will be SEVERAL responses stating that this is an inefficient way to accomplish this, but it does work. It ran in 5 seconds on my machine.

    See attached for the script.

    [font="Arial"]“Any fool can know. The point is to understand.”
    - Albert Einstein

    "DOH!"
    - Homer Simpson[/font]

Viewing 7 posts - 1 through 6 (of 6 total)

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