Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Joshrinn
Posting Yak Master
118 Posts |
 Posted - 2012-07-21 : 15:41:10
|
| Desired result set is belowhttp://i46.tinypic.com/2ufwzdc.pngQuestion: On the column STAT_AMT, row number 15 on excel sheet attached,5135-4324 is subtracted and sent 811 to the next row, but that 4324 also needs to display as PRDID 1 to make the SUM(STAT_AMT)=30000 even and when that 4324 displays on STAT_AMT, the balance column has a 0 balance. Same will be on PRDID 2 and on the following PRDIDs. I have highlighted it on the excel sheet. Once the PRDID is 7, then the next PRDID will be all folled by PRDID=0000 and there will be 0 on the balance field as well once it is over PRDID 7.An outer query and case statement could help me update PRDID to 0 when it was over PRDID 7 but I couldn't do it. Your help will be appreciated. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-07-21 : 16:19:36
|
| sorry your rules are not clear. can you explain how you got those values in STAT_AMT and BALANCE------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
Joshrinn
Posting Yak Master
118 Posts |
Posted - 2012-07-21 : 16:26:19
|
| Thankyou in Advance.I have created a temp table. The values for STAT_AMT are given. The first row of the Balance column get populated by subtracting Total_amt-STAT_amt. After that from the next row the remaining balance on the balance column subtracts the stat_amt until the balance column has a value of 0. A recursive CTE is used to subtract balance-STAT_AMT. This continues for each PRDID until the PRDID is 7. After that the total amount will be 0 and so will the balance column and prdid will be 0000. The stat_amt has a value given in the table so that is fine. Hope this explanation helps. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-07-21 : 16:51:47
|
| ok..so is your attempt to generate yellow rows in between in excel?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
Joshrinn
Posting Yak Master
118 Posts |
Posted - 2012-07-21 : 16:55:23
|
| Yes. |
|
|
|
|
|
|
CTE related