Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2011
    Posts
    4

    Unanswered: Help to get sum of previous rows' column values

    Hi,

    could someone please help me to get out of this problem......

    i m writing a select query in which in each row i need to calculate interest value that is based sum of all previous rows' column value.
    let me explain scnerio first -

    I have two tables
    EPF_mst_month (contains twelve rows for twelve months)
    left join with
    EPF_user_data ( contains user's monthly contribution data)

    query i wrote is working fine if there are twelve rows in user_data table
    but if there is lesser(that might happen) rows then the result returning is not correct.

    my written query -

    select
    isnull(EPF_MST_MONTH.serial,0) [cnt],
    isnull (EPF_MST_MONTH.monName,'') as [desc],
    ROUND(isnull(memcont,0),0) as memcont,
    case
    when EPF_MST_MONTH.serial = 1
    then round(isnull(@obmem*8.5/1200,0),0)
    when empcode Is null -- if no monthly contribution then (select round(isnull((sum(memcont)+@obmem)*8.5/1200,0), 0)
    from EPF_MST_MONTH M LEFT outer JOIN epf_user_data b
    on EPF_MST_MONTH.cmonth=b.cmonth
    and b.EMPcode='ABC'
    andT b.finyear='2010-2011' and b.cid='DEMO'
    where m.serial <= EPF_MST_MONTH.serial AND
    b.EMPcode='ABC' and b.finyear='2010-2011'
    and b.cid=@cid)
    else (select round(isnull((sum(memcont)+@obmem)*8.5/1200,0),0)
    from EPF_USER_DATA where tdate < a.tdate
    AND EMPcode='ABC'
    and finyear='2010-2011' and cid='DEMO' )
    end as 'memint'

    from EPF_MST_MONTH left outer join EPF_USER_DATA a
    on EPF_MST_MONTH.cmonth= a.cmonth
    and a.EMPcode='ABC' and a.finyear='2010-2011'
    and a.cid='DEMO' order by cnt

    any help on above is greately appreciated....

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    The SQL script doesn't compile. Can you submit a corrected version and place it between [CODE] - [/ CODE] tags for better readability?
    Msg 156, Level 15, State 1, Line 11
    Incorrect syntax near the keyword 'from'.
    Msg 156, Level 15, State 1, Line 23
    Incorrect syntax near the keyword 'as'.
    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

Tags for this Thread

Posting Permissions

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