Combining Columns of Same Name

I have 4 tables all with an accountingDate [DateTime] and an amount
[money]. I also have an AccountRegister that acts as a Ledger and has
the invoice items.
I am trying to create a query with a single amount column as a result,
right now my joins create 4 "Amount" columns, is there a way to combine
them into one?
SELECT AR.accountEntryID, AR.clientID, AR.accountingDate,
AR.accountEntryID,
AR.hostingInvoiceID, AR.secondgearInvoiceID,
AR.consultingInvoiceID,
AR.projectInvoiceID, CS.amount AS Amount, HO.amount AS
Amount,
SG.amount AS Amount, PR.amount AS Amount, PY.amount AS
Amount
FROM acct_AccountRegister AR LEFT OUTER JOIN
acct_ConsultingInvoices CS ON
AR.consultingInvoiceID = CS.consultingInvoiceID LEFT OUTER JOIN
acct_HostingInvoices HO ON AR.hostingInvoiceID =
HO.hostingInvoiceID LEFT OUTER JOIN
acct_ProjectInvoices PR ON AR.projectInvoiceID =
PR.projectInvoiceID LEFT OUTER JOIN
acct_SecondGearInvoices SG ON
AR.secondgearInvoiceID = SG.secondgearInvoiceID LEFT OUTER JOIN
acct_Payments PY ON AR.accountPaymentID = PY.accountPaymentID
ORDER BY AR.accountingDatedepends - how is the one determined?
is it a sum of the amount columns?
is it the first non-null amount?
or something else?
jasdeep jaitla wrote:
> I have 4 tables all with an accountingDate [DateTime] and an amount
> [money]. I also have an AccountRegister that acts as a Ledger and has
> the invoice items.
> I am trying to create a query with a single amount column as a result,
> right now my joins create 4 "Amount" columns, is there a way to combine
> them into one?
>
> SELECT AR.accountEntryID, AR.clientID, AR.accountingDate,
> AR.accountEntryID,
> AR.hostingInvoiceID, AR.secondgearInvoiceID,
> AR.consultingInvoiceID,
> AR.projectInvoiceID, CS.amount AS Amount, HO.amount AS
> Amount,
> SG.amount AS Amount, PR.amount AS Amount, PY.amount AS
> Amount
> FROM acct_AccountRegister AR LEFT OUTER JOIN
> acct_ConsultingInvoices CS ON
> AR.consultingInvoiceID = CS.consultingInvoiceID LEFT OUTER JOIN
> acct_HostingInvoices HO ON AR.hostingInvoiceID =
> HO.hostingInvoiceID LEFT OUTER JOIN
> acct_ProjectInvoices PR ON AR.projectInvoiceID =
> PR.projectInvoiceID LEFT OUTER JOIN
> acct_SecondGearInvoices SG ON
> AR.secondgearInvoiceID = SG.secondgearInvoiceID LEFT OUTER JOIN
> acct_Payments PY ON AR.accountPaymentID = PY.accountPaymentID
> ORDER BY AR.accountingDate
>|||the 4 tables are different types of invoices, but they share some
common column names/types: accountingDate, amount, type, invoiceNumber
the amount is a line item (not a sum), but each of the 4 tables has an
amount for each row. If I join the ledger table with each of the 4
tables I end up with 4 amount columns, and for each row 3 are null and
one has a value depending on the table it came from. I want only one
amount column without having to duplicate that information in the
ledger table. The ledger is basically a one-many table that references
the invoices for every client, so I can pull all invoices for a
particular client.
What I decided to do was create a temporary aggregate table and insert
the values from each of the four tables into the temporary table, that
worked fine. If there is a way to do the same result with a join select
query, I'm still interested in that information.|||so you want the non-null value out of the 4? (i counted 5 in the
original post, so don't know which 4 of the 5 to use - in this example,
i've used all 5, but you get the the idea...)
see COALESCE() in BOL.
e.g. instead of just
..., CS.amount, HO.amount, SG.amount, PR.amount, PY.amount, ...
use
..., coalesce(CS.amount, HO.amount, SG.amount, PR.amount, PY.amount) as
amount, ...
this will give the first non-null value out of the list.
jasdeep jaitla wrote:
> the 4 tables are different types of invoices, but they share some
> common column names/types: accountingDate, amount, type, invoiceNumber
> the amount is a line item (not a sum), but each of the 4 tables has an
> amount for each row. If I join the ledger table with each of the 4
> tables I end up with 4 amount columns, and for each row 3 are null and
> one has a value depending on the table it came from. I want only one
> amount column without having to duplicate that information in the
> ledger table. The ledger is basically a one-many table that references
> the invoices for every client, so I can pull all invoices for a
> particular client.
> What I decided to do was create a temporary aggregate table and insert
> the values from each of the four tables into the temporary table, that
> worked fine. If there is a way to do the same result with a join select
> query, I'm still interested in that information.
>|||Thank you so much, that is exactly what I was trying to find!|||Thank you so much, that is exactly what I was trying to find!