-4

i have a table report like this

YYYYMM   pipno demand principle interest balance=(demand-principle)
201010   101     5000   500       100      4500
201010   102     1000   750       100       250
201011   103     2000   1500      100       500
201011   102     1000   750       100       250
201011   104     1500   1000      100       500
201011   101     5000   1500      100       3500

// Here in demand field i want to add demand with old balance ie., 4500+5000=9500 where pipno=101, because pipno 101 exists two times and also same if any pipno exists more than once we want to add the demand with existing balance. Plaese reply me as quick

sample o/p

pipno demand principle interest balance=(demand-principle)
101     5000   500       100      4500
102     1000   750       100       250
103     2000   1500      100       500
104     1500   1000      100       500
101     9500   1500      100       8000
102     1250   750       100       500

in demand field i want to bring the balance plus old demand if pipno exists more than once here 101 & 102 exists twice

Rajkumar
  • 3
  • 2
  • 6
  • 2
    This has to be the worst question ever, and Ive asked some bad ones. – Jeremy Oct 17 '11 at 05:55
  • Balance doesn't seems to be demand - principle. I see the last row of the first table 5000-1500=3500 not 4000, and in the second table 9500-1500=8000 not 4000. And there is no 4500 in the demand column – xanatos Oct 17 '11 at 05:57
  • use more detailed explanation and check if all data are correct. – Bryan Oct 17 '11 at 06:03
  • @JeremyChild Your questions are all >= 0, so they aren't too much bad :-) – xanatos Oct 17 '11 at 06:11
  • In a certain way, it's probably a duplicate of http://stackoverflow.com/questions/860966/calculate-a-running-total-in-sqlserver – xanatos Oct 17 '11 at 06:20

1 Answers1

0
SELECT MT.pipno, 
    MT.YYYYMM,
    MT.demand + ISNULL((SELECT SUM(balance) FROM MyTable MT2 WHERE MT.pipno=MT2.pipno AND MT2.YYYYMM < MT.YYYYMM), 0) AS demand, 
    MT.principle,
    MT.interest,
    MT.balance
FROM MyTable MT

Is this what you wanted?

xanatos
  • 109,618
  • 12
  • 197
  • 280
  • i want to add the balance into demand when pipno=101 if pipno 101 exists more than once – Rajkumar Oct 17 '11 at 06:06
  • @Rajkumar The problem is that in SQL the ordering doesn't exist "naturally". What you see as an ordered table (the one you gave) isn't really ordered in SQL. Unless there is another column to give an order, like a date, or a "master" id or something similar (otherwise I could I know which of the 101 is the "second" one? – xanatos Oct 17 '11 at 06:09
  • @Rajkumar Changed. If you have a date (or something similar) you can use this versions. – xanatos Oct 17 '11 at 06:17
  • hello it is working for the pipno 101 alone if any other pipno exists more than once it is not working for exampl 102 exists more than once it is not working – Rajkumar Oct 17 '11 at 06:27