barkome
asked on
SQL CASE ANOTHER COLUMN OF ANOTHER ROW
Dears
Im trying to case a column to use the sum of M_DR + M_CR as the CLS_BAL. E,g if the ID is 192000801 then it should have the sum of M_DR and M_CR of ID 1920008 as its CLS_BAL as I have made in bold below.
Attach is a sample of the data I am working with.
SHEET CAT H_ID ID CHRT_ID OPN_BAL M_DR M_CR CLS_BAL
BALANCE ASSETS 10122 1920008 1920008 0.00 3000.00 0.00 0.00
BALANCE ASSETS 10125 192000801 192000801 0.00 0.00 0.00 3000.00
BALANCE ASSETS 10117 1930002 1930002 20847369.45 0.00 403436.43 21250805.88
BALANCE ASSETS 10125 193000201 193000201 0.00 0.00 0.00 0.00
EE-SAMPLE-0427.xlsx
Im trying to case a column to use the sum of M_DR + M_CR as the CLS_BAL. E,g if the ID is 192000801 then it should have the sum of M_DR and M_CR of ID 1920008 as its CLS_BAL as I have made in bold below.
Attach is a sample of the data I am working with.
SHEET CAT H_ID ID CHRT_ID OPN_BAL M_DR M_CR CLS_BAL
BALANCE ASSETS 10122 1920008 1920008 0.00 3000.00 0.00 0.00
BALANCE ASSETS 10125 192000801 192000801 0.00 0.00 0.00 3000.00
BALANCE ASSETS 10117 1930002 1930002 20847369.45 0.00 403436.43 21250805.88
BALANCE ASSETS 10125 193000201 193000201 0.00 0.00 0.00 0.00
EE-SAMPLE-0427.xlsx
ASKER
Yes, but I want ID 192000801 to have (M_DR + M_CR) of ID 1920008
This relies on having only one matching row for each (shortened) ID:
Select SHEET, CAT, H_ID, a.ID, CHRT_ID, OPN_BAL, M_DR, M_CR, n_CLS_BAL
from tbl a
left join (select ID, M_DR + M_CR as n_CLS_BAL from tbl) b
on left(a.ID, 7) = b.ID
ASKER
I was looking forward for something similar to this(syntax not correct though):
CASE
WHEN CHRT_ID ='193000201' THEN (RP_M_DR + RP_M_CR WHERE CHRT_ID ='1930002')
CASE
WHEN CHRT_ID ='193000201' THEN (RP_M_DR + RP_M_CR WHERE CHRT_ID ='1930002')
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks Qlemo, I had this in mind, but had difficulty referencing 'tbl' because it my tbl is a derived table from a subquery.
The ID's I also want to have fixed are just 3 in number, and wont change.
The ID's I also want to have fixed are just 3 in number, and wont change.
To avoid line wrap, embed your data inside the Code brackets, it will be far easier to read:
SHEET CAT H_ID ID CHRT_ID OPN_BAL M_DR M_CR CLS_BAL
BALANCE ASSETS 10122 1920008 1920008 0.00 3000.00 0.00 0.00
BALANCE ASSETS 10125 192000801 192000801 0.00 0.00 0.00 3000.00
BALANCE ASSETS 10117 1930002 1930002 20847369.45 0.00 403436.43 21250805.88
BALANCE ASSETS 10125 193000201 193000201 0.00 0.00 0.00 0.00
If you generate your subquery data as in my first example, or with a CTE, you should have no difficulties to access the results as in my second example.
try this
Select SHEET, CAT, H_ID, ID, CHRT_ID, OPN_BAL, M_DR, M_CR, (M_DR + M_CR) as n_CLS_BAL
from tablename