Adding different columns from different table

I have three tables.

Member(name, address, ID)

Loan(ID, startdate, amount)

Deposite(ID, startdate, amount)

I wanna create a report which look like this.

ID MembersName startdate address etc

Member can be either borrower or a depositor.

I'm thinking of using inner join. Can anyone help me to write the query?

Thanks

*If* ID links all three tables then the query would look like this:

This query is based on a member being *Either* a borrower OR a depositor.

Code Snippet

Select

m.ID,

M.Name,

Coalesce(L.StartDate, D.StartDate) as StartDate,

m.Address

From Member m (nolock)

left outer join Loan L (nolock)

on m.ID = L.ID

left outer join Deposit D (nolock)

on m.ID = D.ID

If a member can be a borrower AND/OR a depositor, the query would look like this:

Code Snippet

Select

m.ID,

S.AccountType,

m.Name,

S.StartDate,

m.Address

From

Member m (nolock),

inner join

(Select

ID,

StartDate,

'Loan' as AccountType

From

Loan l (nolock)

union

Select

ID,

StartDate,

'Deposit' as AccountType

From

Deposit d (nolock)

) S

on

m.ID = S.ID

Order by ID

You really cant use an inner join on all 3 tables since it would look for only members that had Deposit and Loan accounts.

HtH

BobP

|||

Thanks I will try with this..Also I wanna add another column now. There i want to show whether the member is a depositor or a borrower. we can get to know that from depositor or borrower table.

As a example,

If the start date of one perticular member is in borrower table , then he is a borrower.

Can you please help me to write this query too?I really appriciate

Thanks

|||

Actually, the 2nd query I have above will show that. I included a column for member type.

BobP

Adding different columns from different table

I have three tables.

Member(name, address, ID)

Loan(ID, startdate, amount)

Deposite(ID, startdate, amount)

I wanna create a report which look like this.

ID MembersName startdate address etc

Member can be either borrower or a depositor.

I'm thinking of using inner join. Can anyone help me to write the query?

Thanks

*If* ID links all three tables then the query would look like this:

This query is based on a member being *Either* a borrower OR a depositor.

Code Snippet

Select

m.ID,

M.Name,

Coalesce(L.StartDate, D.StartDate) as StartDate,

m.Address

From Member m (nolock)

left outer join Loan L (nolock)

on m.ID = L.ID

left outer join Deposit D (nolock)

on m.ID = D.ID

If a member can be a borrower AND/OR a depositor, the query would look like this:

Code Snippet

Select

m.ID,

S.AccountType,

m.Name,

S.StartDate,

m.Address

From

Member m (nolock),

inner join

(Select

ID,

StartDate,

'Loan' as AccountType

From

Loan l (nolock)

union

Select

ID,

StartDate,

'Deposit' as AccountType

From

Deposit d (nolock)

) S

on

m.ID = S.ID

Order by ID

You really cant use an inner join on all 3 tables since it would look for only members that had Deposit and Loan accounts.

HtH

BobP

|||

Thanks I will try with this..Also I wanna add another column now. There i want to show whether the member is a depositor or a borrower. we can get to know that from depositor or borrower table.

As a example,

If the start date of one perticular member is in borrower table , then he is a borrower.

Can you please help me to write this query too?I really appriciate

Thanks

|||

Actually, the 2nd query I have above will show that. I included a column for member type.

BobP

Adding a time column to a date column

I have two columns in a table:
StartDate DateTime and StartTime DateTime.
The StartDate column holds a value such as 07/16/2004
The StartTime column holds a value such as 3:00:00 PM

I want to be able to add them in a stored procedure.
When I use StartDate + StartTime I get a date two days earlier than expected.
For example, instead of 7/16/2004 3:00:00 PM StartDate + StartTime returns
7/14/2004 3:00:00 PM.

Can anyone point out wht I'm doing wrong with this one?

Thanks,
lqLauren,

It sounds like you are using Enterprise Manager. All datetime columns in SQL Server hold both a date and a time, and if you view the
data in Query Analyzer, you should find that your StartDate column holds something like 2004-07-16 12:00:00AM and your StartTime column
holds a value like 1899-12-30 03:00:00PM.

Tools that allows data input will attach a date when a time only is entered into a SQL Server database column, and unfortunately some
tools will attach 1900-01-01 and others will attach 1899-12-30. SQL Server stores the first of these as its zero date, but it stores the
second as -2 (plus whatever fraction of a date the time portion represents, in each case). Enterprise Manager thinks that 1899-12-30 is the
base date, and both attaches it when a bare time is entered and suppresses it when it appears in a datetime to be displayed.

You are doing nothing wrong, but to be safe, you can calculate the time portion explicitly before you add. One way to do this is

StartDate + (StartTime - datediff(day,0,StartTime))

Storing time-only values in SQL Server is tricky, since there is no appropriate type. You need to be careful, and you might want to
consider alternatives, such as storing only the StartDateTime in the database, in which case you can make StartDate and StartTime computed
columns, or calculate them on the fly when you need them.

Steve Kass
Drew University

Lauren Quantrell wrote:

> I have two columns in a table:
> StartDate DateTime and StartTime DateTime.
> The StartDate column holds a value such as 07/16/2004
> The StartTime column holds a value such as 3:00:00 PM
> I want to be able to add them in a stored procedure.
> When I use StartDate + StartTime I get a date two days earlier than expected.
> For example, instead of 7/16/2004 3:00:00 PM StartDate + StartTime returns
> 7/14/2004 3:00:00 PM.
> Can anyone point out wht I'm doing wrong with this one?
> Thanks,
> lq|||Steve,
Thanks a million for that. I was trying all manner of cast, convert
and datepart functions but yours is quick and simple. Thanks!
lq

Steve Kass <skass@.drew.edu> wrote in message news:<8hdLc.8319$mL5.4812@.newsread1.news.pas.earthlink.n et>...
> Lauren,
> It sounds like you are using Enterprise Manager. All datetime columns in SQL Server hold both a date and a time, and if you view the
> data in Query Analyzer, you should find that your StartDate column holds something like 2004-07-16 12:00:00AM and your StartTime column
> holds a value like 1899-12-30 03:00:00PM.
> Tools that allows data input will attach a date when a time only is entered into a SQL Server database column, and unfortunately some
> tools will attach 1900-01-01 and others will attach 1899-12-30. SQL Server stores the first of these as its zero date, but it stores the
> second as -2 (plus whatever fraction of a date the time portion represents, in each case). Enterprise Manager thinks that 1899-12-30 is the
> base date, and both attaches it when a bare time is entered and suppresses it when it appears in a datetime to be displayed.
> You are doing nothing wrong, but to be safe, you can calculate the time portion explicitly before you add. One way to do this is
> StartDate + (StartTime - datediff(day,0,StartTime))
> Storing time-only values in SQL Server is tricky, since there is no appropriate type. You need to be careful, and you might want to
> consider alternatives, such as storing only the StartDateTime in the database, in which case you can make StartDate and StartTime computed
> columns, or calculate them on the fly when you need them.
> Steve Kass
> Drew University
> Lauren Quantrell wrote:
> > I have two columns in a table:
> > StartDate DateTime and StartTime DateTime.
> > The StartDate column holds a value such as 07/16/2004
> > The StartTime column holds a value such as 3:00:00 PM
> > I want to be able to add them in a stored procedure.
> > When I use StartDate + StartTime I get a date two days earlier than expected.
> > For example, instead of 7/16/2004 3:00:00 PM StartDate + StartTime returns
> > 7/14/2004 3:00:00 PM.
> > Can anyone point out wht I'm doing wrong with this one?
> > Thanks,
> > lq