Hello,
I need help to make my query more simple
Depends how many rows for column Code (now I have 53 rows) then it will be 53 sub selects and 53 more columns.
How to make it simple to add variable in select and in sub select instead of writing 53 times as I did?
Thank you.
select a.acct Account_Code
, case when a100.Debit_Balance is null then a100.Credit_Balance else a100.Debit_Balance end [100]
, case when a102.Debit_Balance is null then a102.Credit_Balance else a102.Debit_Balance end [102]
, case when a109.Debit_Balance is null then a109.Credit_Balance else a109.Debit_Balance end [109]
, case when a110.Debit_Balance is null then a110.Credit_Balance else a110.Debit_Balance end [110]
, case when a111.Debit_Balance is null then a111.Credit_Balance else a111.Debit_Balance end [111]
, case when a112.Debit_Balance is null then a112.Credit_Balance else a112.Debit_Balance end [112]
, case when a115.Debit_Balance is null then a115.Credit_Balance else a115.Debit_Balance end [115]
-------------------------------------------------------------------------------------
from
(
SELECT [Account_Code] acct
From dbo.[2023_TB_Expenses_not_including_Salaries_FB_Run_10_15_24]
union
SELECT [Account_Code] acct
From dbo.[2023_Revenues_Run_10_18_24] ) a
left join --------------------------------------------------------------------------------
( select [Code], [Account_Code], [Account_Title], [Debit_Balance], [Credit_Balance] from (
select * from dbo.[2023_TB_Expenses_not_including_Salaries_FB_Run_10_15_24] union select * from dbo.[2023_Revenues_Run_10_18_24] ) a where a.code=100 ) a100
on a.acct=a100.[Account_Code]
left join
( select [Code], [Account_Code], [Account_Title], [Debit_Balance], [Credit_Balance] from (
select * from dbo.[2023_TB_Expenses_not_including_Salaries_FB_Run_10_15_24] union select * from dbo.[2023_Revenues_Run_10_18_24] ) a where a.code=102 ) a102
on a.acct=a102.[Account_Code]
left join
( select [Code], [Account_Code], [Account_Title], [Debit_Balance], [Credit_Balance] from (
select * from dbo.[2023_TB_Expenses_not_including_Salaries_FB_Run_10_15_24] union select * from dbo.[2023_Revenues_Run_10_18_24] ) a where a.code=109 ) a109
on a.acct=a109.[Account_Code]
left join
( select [Code], [Account_Code], [Account_Title], [Debit_Balance], [Credit_Balance] from (
select * from dbo.[2023_TB_Expenses_not_including_Salaries_FB_Run_10_15_24] union select * from dbo.[2023_Revenues_Run_10_18_24] ) a where a.code=110 ) a110
on a.acct=a110.[Account_Code]
left join
( select [Code], [Account_Code], [Account_Title], [Debit_Balance], [Credit_Balance] from (
select * from dbo.[2023_TB_Expenses_not_including_Salaries_FB_Run_10_15_24] union select * from dbo.[2023_Revenues_Run_10_18_24] ) a where a.code=111 ) a111
on a.acct=a111.[Account_Code]
left join
( select [Code], [Account_Code], [Account_Title], [Debit_Balance], [Credit_Balance] from (
select * from dbo.[2023_TB_Expenses_not_including_Salaries_FB_Run_10_15_24] union select * from dbo.[2023_Revenues_Run_10_18_24] ) a where a.code=112 ) a112
on a.acct=a112.[Account_Code]
left join
( select [Code], [Account_Code], [Account_Title], [Debit_Balance], [Credit_Balance] from (
select * from dbo.[2023_TB_Expenses_not_including_Salaries_FB_Run_10_15_24] union select * from dbo.[2023_Revenues_Run_10_18_24] ) a where a.code=115 ) a115
on a.acct=a115.[Account_Code]
order by 1