Prev: SQL Server Express 2005 Redistributable Licence
Next: SQL Server 2008 developer RC0 missing setup.exe?
From: papa smurf on 26 Jun 2008 14:45
Hello all. IÂ’m having an issue with joining the contents of two
tables. The join options available to me seem to have limitations
that are keeping me from retrieving all data from both tables. I will
do my best to give the table details below, and try to give you an
idea of what I want as from the output of the query. Any help is much
appreciated.

Table A has the following:

vcDiv_Name
vcCard_Name
sumAmnt
dtChgbk_Date (this has been formatted to show Month and Year only)
dtCBDT

Table B has the following:

vcDiv_Name
vcCard_Name
sumWrite_OFF
dtChgbk_Date (this has been formatted to show Month and Year only)
dtCBDT

I want my result set to include the following from both Table A and B:
vcDiv_Name
vcCard_Name
sumAmnt
sumWrite_OFF
dtChgbk_Date
dtCBDT

I have tried to join on the dtChgbk_Date, vcDiv_Name, vcCard_Name
fields between both tables. In some cases there may be a value in
dtChgbk from table A that doesnÂ’t exist in table B. For this
instance, I would want 0.00 in either the sumWrite_OFF field or
sumAmnt field accordingly. IÂ’m really struggling through this, and
fear its something stupid that IÂ’m overlooking. So I apreciate your
patients with me if this is the case. Thanks again for your help!
From: Tom Moreau on 26 Jun 2008 14:54
What are the keys of the tables? Assuming:

vcDiv_Name
vcCard_Name
dtChgbk_Date

.... try:

select
*
from
TableA as a
full join
TableB as b on b.vcDiv_Name = a.vcDiv_Name
and b.vcCard_Name = a.vcCard_Name
and b.dtChgbk_Date = a.dtChgbk_Date


--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau

"papa smurf" <DSOldham(a)gmail.com> wrote in message
news:dd72a27e-c046-445a-a0e3-4b87e9f1349e(a)m44g2000hsc.googlegroups.com...
Hello all. I�m having an issue with joining the contents of two
tables. The join options available to me seem to have limitations
that are keeping me from retrieving all data from both tables. I will
do my best to give the table details below, and try to give you an
idea of what I want as from the output of the query. Any help is much
appreciated.

Table A has the following:

vcDiv_Name
vcCard_Name
sumAmnt
dtChgbk_Date (this has been formatted to show Month and Year only)
dtCBDT

Table B has the following:

vcDiv_Name
vcCard_Name
sumWrite_OFF
dtChgbk_Date (this has been formatted to show Month and Year only)
dtCBDT

I want my result set to include the following from both Table A and B:
vcDiv_Name
vcCard_Name
sumAmnt
sumWrite_OFF
dtChgbk_Date
dtCBDT

I have tried to join on the dtChgbk_Date, vcDiv_Name, vcCard_Name
fields between both tables. In some cases there may be a value in
dtChgbk from table A that doesn�t exist in table B. For this
instance, I would want 0.00 in either the sumWrite_OFF field or
sumAmnt field accordingly. I�m really struggling through this, and
fear its something stupid that I�m overlooking. So I apreciate your
patients with me if this is the case. Thanks again for your help!


From: Steve on 26 Jun 2008 15:05
Try (untested):

SELECT A.vcDiv_Name,
A.vcCard_Name,
isnull(A.sumAmnt,0) as SumAmnt,
isnull(B.sumWrite_OFF,0) as SumWrite,
A.dtChgbk_Date,
A.dtCBDT
FROM TableA A
FULL OUTER JOIN TableB B ON B.vcDiv_Name = A.vcDiv_Name
AND B.vcCard_Name = A.vcCard_Name
AND B.dtChgbk_Date = A.dtChgbk_Date






"papa smurf" wrote:

> Hello all. I'm having an issue with joining the contents of two
> tables. The join options available to me seem to have limitations
> that are keeping me from retrieving all data from both tables. I will
> do my best to give the table details below, and try to give you an
> idea of what I want as from the output of the query. Any help is much
> appreciated.
>
> Table A has the following:
>
> vcDiv_Name
> vcCard_Name
> sumAmnt
> dtChgbk_Date (this has been formatted to show Month and Year only)
> dtCBDT
>
> Table B has the following:
>
> vcDiv_Name
> vcCard_Name
> sumWrite_OFF
> dtChgbk_Date (this has been formatted to show Month and Year only)
> dtCBDT
>
> I want my result set to include the following from both Table A and B:
> vcDiv_Name
> vcCard_Name
> sumAmnt
> sumWrite_OFF
> dtChgbk_Date
> dtCBDT
>
> I have tried to join on the dtChgbk_Date, vcDiv_Name, vcCard_Name
> fields between both tables. In some cases there may be a value in
> dtChgbk from table A that doesn't exist in table B. For this
> instance, I would want 0.00 in either the sumWrite_OFF field or
> sumAmnt field accordingly. I'm really struggling through this, and
> fear its something stupid that I'm overlooking. So I apreciate your
> patients with me if this is the case. Thanks again for your help!
>
From: papa smurf on 26 Jun 2008 16:21
On Jun 26, 3:05 pm, Steve <St...(a)discussions.microsoft.com> wrote:
> Try (untested):
>
> SELECT A.vcDiv_Name,
>             A.vcCard_Name,
>             isnull(A.sumAmnt,0) as SumAmnt,
>             isnull(B.sumWrite_OFF,0) as SumWrite,
>             A.dtChgbk_Date,
>             A.dtCBDT
> FROM TableA A
>   FULL OUTER JOIN TableB B ON B.vcDiv_Name = A.vcDiv_Name
>      AND B.vcCard_Name = A.vcCard_Name
>      AND B.dtChgbk_Date = A.dtChgbk_Date
>
>
>
> "papa smurf" wrote:
> > Hello all.  I’m having an issue with joining the contents of two
> > tables.  The join options available to me seem to have limitations
> > that are keeping me from retrieving all data from both tables.  I will
> > do my best to give the table details below, and try to give you an
> > idea of what I want as from the output of the query.  Any help is much
> > appreciated.
>
> > Table A has the following:
>
> > vcDiv_Name
> > vcCard_Name
> > sumAmnt
> > dtChgbk_Date (this has been formatted to show Month and Year only)
> > dtCBDT
>
> > Table B has the following:
>
> > vcDiv_Name
> > vcCard_Name
> > sumWrite_OFF
> > dtChgbk_Date (this has been formatted to show Month and Year only)
> > dtCBDT
>
> > I want my result set to include the following from both Table A and B:
> > vcDiv_Name
> > vcCard_Name
> > sumAmnt
> > sumWrite_OFF
> > dtChgbk_Date
> > dtCBDT
>
> > I have tried to join on the dtChgbk_Date, vcDiv_Name, vcCard_Name
> > fields between both tables.  In some cases there may be a value in
> > dtChgbk from table A that doesn’t exist in table B.  For this
> > instance, I would want 0.00 in either the sumWrite_OFF field or
> > sumAmnt field accordingly.  I’m really struggling through this, and
> > fear its something stupid that I’m overlooking.  So I apreciate your
> > patients with me if this is the case.  Thanks again for your help!- Hide quoted text -
>
> - Show quoted text -

Thank you for your responses. In reading about a Full Outer Join it
would seem this should work for me. However, the result set count is
far greater than the two tables combined, and for some reason it is
duplicating values (I really can't find a good reason as to why it's
doing this).

The two tables I am pulling data from are temp tables. I will leave
my sql code below that produces these tables. Perhaps it may
influence an alternate option. Thanks again for the assistance.

Declare @SD DateTime
Declare @ED DateTime
Declare @DT DateTime


Set @SD = '01/31/2008'
Set @ED = '03/01/2008'
Set @DT = '01/01/1990'


SELECT D.vcDiv_Name, CT.vcCard_Name, Sum(T.moAmount) sumAmnt,
DateName(Month,T.dtChgbk_Date) + ' - ' +
DateName(Year,T.dtChgbk_Date) AS dtChgbk_Date, T.dtChgbk_Date as CBDT
Into #OpenCB
FROM dbo.Transactions T
Join dbo.Division D
On T.iDiv_Num = D.iDiv_Num
Join Card_Type CT
On T.iCard_Type = CT.iCard_Type
Where Convert(VarChar(50),T.dtResolve_Date,101) = @DT
And uidRecord_Num <> 0
and T.iDiv_Num <> 0
and T.bDeleted <> 1
Or Convert(VarChar(50),T.dtResolve_Date,101) >= @ED
And Convert(VarChar(50),T.dtEntrd_Date,101) <= @ED
And Convert(VarChar(50),T.dtChgbk_Date,101) <> @DT
Group By D.vcDiv_Name, CT.vcCard_Name, DateName(Month,T.dtChgbk_Date)
+ ' - ' + DateName(Year,T.dtChgbk_Date),
T.dtChgbk_Date,Convert(VarChar(50),T.dtResolve_Date,101)

SELECT D.vcDiv_Name, CT.vcCard_Name, Sum(T.moWrite_Off) sumWriteOff,
DateName(Month,T.dtChgbk_Date) + ' - ' +
DateName(Year,T.dtChgbk_Date) AS dtChgbk_Date, T.dtChgbk_Date as CBDT
Into #CurrentWO
FROM dbo.Transactions T
Join dbo.Division D
On T.iDiv_Num = D.iDiv_Num
Join dbo.Card_Type CT
On T.iCard_Type = CT.iCard_Type
Where Convert(VarChar(50),T.dtResolve_Date,101) >= @SD
And Convert(VarChar(50),T.dtResolve_Date,101) <= @ED
And uidRecord_Num <> 0
and T.iDiv_Num <> 0
and T.bDeleted <> 1
Group By D.vcDiv_Name, CT.vcCard_Name, DateName(Month,T.dtChgbk_Date)
+ ' - ' + DateName(Year,T.dtChgbk_Date), T.dtChgbk_Date
Order By vcDiv_Name, vcCard_Name, CBDT
From: Sha Anand on 27 Jun 2008 05:27
SELECT
vcDiv_Name,vcCard_Name,SUM(sumAmnt) as SumAmt,SUM(sumWrite_OFF) as
sumWrite_OFF,dtChgbk_Date,dtCBDT
FROM
(
SELECT vcDiv_Name,vcCard_Name,sumAmnt,0 as sumWrite_OFF,dtChgbk_Date,dtCBDT
FROM TableA
UNION ALL
SELECT vcDiv_Name,vcCard_Name,0 as sumAmnt,sumWrite_OFF,dtChgbk_Date,dtCBDT
FROM TableB
)
GROUP BY
vcDiv_Name,vcCard_Name,dtChgbk_Date,dtCBDT

- Sha Anand

"papa smurf" wrote:

> Hello all. I'm having an issue with joining the contents of two
> tables. The join options available to me seem to have limitations
> that are keeping me from retrieving all data from both tables. I will
> do my best to give the table details below, and try to give you an
> idea of what I want as from the output of the query. Any help is much
> appreciated.
>
> Table A has the following:
>
> vcDiv_Name
> vcCard_Name
> sumAmnt
> dtChgbk_Date (this has been formatted to show Month and Year only)
> dtCBDT
>
> Table B has the following:
>
> vcDiv_Name
> vcCard_Name
> sumWrite_OFF
> dtChgbk_Date (this has been formatted to show Month and Year only)
> dtCBDT
>
> I want my result set to include the following from both Table A and B:
> vcDiv_Name
> vcCard_Name
> sumAmnt
> sumWrite_OFF
> dtChgbk_Date
> dtCBDT
>
> I have tried to join on the dtChgbk_Date, vcDiv_Name, vcCard_Name
> fields between both tables. In some cases there may be a value in
> dtChgbk from table A that doesn't exist in table B. For this
> instance, I would want 0.00 in either the sumWrite_OFF field or
> sumAmnt field accordingly. I'm really struggling through this, and
> fear its something stupid that I'm overlooking. So I apreciate your
> patients with me if this is the case. Thanks again for your help!
>
 |  Next  |  Last
Pages: 1 2
Prev: SQL Server Express 2005 Redistributable Licence
Next: SQL Server 2008 developer RC0 missing setup.exe?