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!

combine separate date & time fields into one datetime field?

Good morning.

I am importing an XLS file into one of my tables. The fields are:

Date Id Time IO

12/22/2006

2

12:48:45 PM

9

12/22/2006

16

5:40:55 AM

1

12/22/2006

16

12:03:59 PM

2

When I do the import, I get the following:

Date Id Time IO
12/22/2006 12:00:00AM 2 12/30/1899 12:48:45 PM 2
12/22/2006 12:00:00AM 16 12/30/1899 5:40:55 AM 1
12/22/2006 12:00:00AM 16 12/30/1899 12:03:59 PM 2

Here are my doubts:

1. Is it be better to combine the Date & Time fields into one column? Advantages/Disadvantages?
2. If I don't combine them, should I use varchar or datetime data type?
2. What issues or problems might I have when I program SQL reports, if I leave the fields as they are?

Any comments or suggestions will be very much welcomed.

Cheers mates.I was suggested to try this out:

UPDATE tbl
SET Date = Date + convert(char(8), Time, 108)

I'll run it after I use DTwizard to export the data into my table. I should also mention I have no PKs defined, just a FK that references Id from a table called Employees. I'm thinking it's best to define ID and Date and Time as PKs.|||

As far as SQL Server is concerned, it will be far easier over time to work with and deal with date/time data if it is stored as one column. I recommend combining the two.

One primary reason is that the combined column will allow easier datetime comparisions and searches.

combine separate date & time fields into one datetime field?

Good morning.

I am importing an XLS file into one of my tables. The fields are:

Date Id Time IO
12/22/2006 2 12:48:45 PM 9
12/22/2006 16 5:40:55 AM 1
12/22/2006 16 12:03:59 PM 2

When I do the import, I get the following:

Date Id Time IO
12/22/2006 12:00:00AM 2 12/30/1899 12:48:45 PM 2
12/22/2006 12:00:00AM 16 12/30/1899 5:40:55 AM 1
12/22/2006 12:00:00AM 16 12/30/1899 12:03:59 PM 2

Here are my doubts:

1. Would it be better to combine the Date & Time fields into one
column? If so, how?
2. What issues or problems might I have when I program SQL reports, if
I leave the fields as they are?

Any comments or suggestions will be very much welcomed.

Cheers mates.drurjen (jfontecha@.gmail.com) writes:

Quote:

Originally Posted by

Good morning.
>
I am importing an XLS file into one of my tables. The fields are:
>
Date Id Time IO
12/22/2006 2 12:48:45 PM 9
12/22/2006 16 5:40:55 AM 1
12/22/2006 16 12:03:59 PM 2
>
When I do the import, I get the following:
>
Date Id Time IO
12/22/2006 12:00:00AM 2 12/30/1899 12:48:45 PM 2
12/22/2006 12:00:00AM 16 12/30/1899 5:40:55 AM 1
12/22/2006 12:00:00AM 16 12/30/1899 12:03:59 PM 2
>
Here are my doubts:
>
1. Would it be better to combine the Date & Time fields into one
column? If so, how?

Most probably. (In the end it depends on business needs, which I don't
anything about.)

A way to merge the columns would be:

UPDATE tbl
SET Date = Date + convert(char(8), Time, 108)

Quote:

Originally Posted by

2. What issues or problems might I have when I program SQL reports, if
I leave the fields as they are?

That you get 1899-12-30 printed all over the place, which you probably
don't want to. So you will need a lot of code to filter the date away.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland,

Thank you for replying. Basically the DB is for employee time
attendance records. I start out with a flat txt file and run that
through an Excel macro that:

a) eliminates repeat entries in a time lapse of 5min
b) erases null entries.

I then take the XLS file and use DTWizard to export it into a table
with the same fields as before: Date, Id, Time, IO. I have no primary
keys defined in this table, just a FK (Id). I believe the primary keys
should be ID, Date & Time.

I'll try your suggestion. Thx again, and sorry for the repeat post.