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 |
ssk.content
Starting Member
1 Post |
Posted - 2013-01-15 : 06:33:19
|
Hi Friends,In my tables have below records.I want, If any Negative value comes in the table it should automatically subtract from above row and should not show negative value..and always it should match with H and T Record amount,For ex... T value=150 and -31 it should show 119 only.it should not show 150,-31H|5796|01/03/2013|16:12:23|S||QES|170000004|IN|3001|1||Test|1||5|119.00||N|4.15D|5796|01/03/2013|16:12:23|S||QES|170000004|IN|RS|1|1800223|Banana Mix |90|1801|1|20.00|20.00|N|4.15D|5796|01/03/2013|16:12:23|S||QES|170000004|IN|RS|2|1800226|Mango Booster |90|1801|1|20.00|20.00|N|4.15D|5796|01/03/2013|16:12:23|S||QES|170000004|IN|RS|3|1800396|Chicken Skewer |90|1801|1|34.00|34.00|N|4.15D|5796|01/03/2013|16:12:23|S||QES|170000004|IN|RS|4|1800018|Risotto |90|1801|1|33.00|33.00|N|4.15D|5796|01/03/2013|16:12:23|S||QES|170000004|IN|RS|5|1800196|Cappuccino |90|1801|1|12.00|12.00|N|4.15T|5796|01/03/2013|16:12:23|S||QES|170000004|IN||1|1||90||0|150.00||N|4.15T|5796|01/03/2013|16:12:23|S||QES|170000004|IN||2|1||90||0|-31.00||N|4.15I need output like this....H|5796|01/03/2013|16:12:23|S||QES|170000004|IN|3001|1||Test|1||5|119.00||N|4.15D|5796|01/03/2013|16:12:23|S||QES|170000004|IN|RS|1|1800223|Banana Mix |90|1801|1|20.00|20.00|N|4.15D|5796|01/03/2013|16:12:23|S||QES|170000004|IN|RS|2|1800226|Mango Booster |90|1801|1|20.00|20.00|N|4.15D|5796|01/03/2013|16:12:23|S||QES|170000004|IN|RS|3|1800396|Chicken Skewer |90|1801|1|34.00|34.00|N|4.15D|5796|01/03/2013|16:12:23|S||QES|170000004|IN|RS|4|1800018|Risotto |90|1801|1|33.00|33.00|N|4.15D|5796|01/03/2013|16:12:23|S||QES|170000004|IN|RS|5|1800196|Cappuccino |90|1801|1|12.00|12.00|N|4.15T|5796|01/03/2013|16:12:23|S||QES|170000004|IN||1|1||90||0|119.00||N|4.15 please help me....Thanks n advance... |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-01-15 : 06:42:25
|
[code]SELECT Column1,Column2,...,All columns except negative valued column,NegativeValuedColumn - COALESCE(NegativeSUM,0)FROM YourTable tOUTER APPLY (SELECT SUM(ABS(NegativeValuedColumn)) AS NegativeSUM FROM table WHERE NegativeValuedColumn <0 AND type= t.Type) t1WHERE t.NegativeValuedColumn >0[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|