question

Mob avatar image
Mob asked

Case to check if running balance in credit again

Hi, Please be so kind as to point me in the right direction with regards to my SQL case select query: My case statement currently reads as follow: When credit > running_balance then credit - running_balance Now this is where I am stuck... If the above rule is true but the running_balance are positive again, the first rule should be excluded and the last credit should be used as result: when.....more case statements above...... then x when Debit is not null and Debit <= 0 then 0 when Debit is not null and Debit > Running_Balance then Debit - Debit when abs(Running_Balance) >= 0 and Credit > abs(Running_Balance) then case when abs(Running_Balance) >= 0 and (abs(Running_Balance)*-1) < (abs(Running_Balance)*+1) then (Credit - abs(Running_Balance)) end Output: +----------------+---------+-----------------+-------------+----------+ | Debit | Credit | Running_Balance | Ledger_Date | Result | +----------------+---------+-----------------+-------------+----------+ | NULL | 3606.57 | -2418.01 | 2015/11/16 | 1 188.56 | | 3606.57 | NULL | 1195.56 | 2015/11/16 | - | | NULL | 5000 | 37475.48 | 2015/11/20 | 5 000.00 | +----------------+---------+-----------------+-------------+----------+ Desired Result: +----------------+---------+-----------------+-------------+----------+ | Debit | Credit | Running_Balance | Ledger_Date | Result | +----------------+---------+-----------------+-------------+----------+ | NULL | 3606.57 | -2418.01 | 2015/11/16 | - | | 3606.57 | NULL | 1195.56 | 2015/11/16 | - | | NULL | 5000 | 37475.48 | 2015/11/20 | 5 000.00 | +----------------+---------+-----------------+-------------+----------+ Thank you kindly Mob
sqlcase
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Dont clearly understand what is the purpose of the result column. Can you please elaborate.
0 Likes 0 ·
Hi Venkataraman, the purpose of the result column will become the measure field that will be used to display the answer on a pivot/cube or graph. It is the result of that record row after all the rules (case statements) have been applied. Hope this makes sense
0 Likes 0 ·

0 Answers

·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.