Need help with self-join

14 Sep 2006 9:50 PM

Terri

I am using a self-join to add amounts with different dates in the same
table.

CREATE TABLE #Testdata
(
Account char(1),
AmountDate datetime,
Amount int
)

INSERT INTO #Testdata (Account, AmountDate, Amount) VALUES ('A',
'20060801',5)
INSERT INTO #Testdata (Account, AmountDate, Amount) VALUES ('A',
'20060901',3)
INSERT INTO #Testdata (Account, AmountDate, Amount) VALUES ('B',
'20060801',10)
INSERT INTO #Testdata (Account, AmountDate, Amount) VALUES ('B',
'20060901',4)
INSERT INTO #Testdata (Account, AmountDate, Amount) VALUES ('C',
'20060901',20)

SELECT
TD1.Account,
TD1.Amount + TD2.Amount
FROM #Testdata TD1 INNER JOIN #Testdata TD2 ON TD1.Account = TD2.Account
WHERE TD1.AmountDate = '20060801' AND TD2.AmountDate = '20060901'

DROP TABLE #Testdata

My problem is with account C where there is not a record for '20060801'. If
there is not a previous record I want to assume 0 so my expected results for
account C would 20.

Thanks as always...

14 Sep 2006 10:14 PM

Simon Sabin

Hello terri,

You need to use a OUTER JOIN and handle the missing data using the ISNULL
function i.e

SELECT
TD1.Account,
TD1.Amount + ISNULL(TD2.Amount,0)
FROM #Testdata TD1
LEFT JOIN #Testdata TD2 ON TD1.Account = TD2.Account AND TD2.AmountDate =
'20060801'
WHERE TD1.AmountDate = '20060901'

You will also note I have flipped your aliases around so TD1 now refers to
the records from 20060801. In addition the filter on TD2 needs to be done
in the ON clause. This is how you get to return the records from TD1 even
when there is no match for TD2 for the specified date

Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons

Show quote

> I am using a self-join to add amounts with different dates in the same
> table.
>
> CREATE TABLE #Testdata
> (
> Account char(1),
> AmountDate datetime,
> Amount int
> )
> INSERT INTO #Testdata (Account, AmountDate, Amount) VALUES ('A',
> '20060801',5)
> INSERT INTO #Testdata (Account, AmountDate, Amount) VALUES ('A',
> '20060901',3)
> INSERT INTO #Testdata (Account, AmountDate, Amount) VALUES ('B',
> '20060801',10)
> INSERT INTO #Testdata (Account, AmountDate, Amount) VALUES ('B',
> '20060901',4)
> INSERT INTO #Testdata (Account, AmountDate, Amount) VALUES ('C',
> '20060901',20)
> SELECT
> TD1.Account,
> TD1.Amount + TD2.Amount
> FROM #Testdata TD1 INNER JOIN #Testdata TD2 ON TD1.Account =
> TD2.Account
> WHERE TD1.AmountDate = '20060801' AND TD2.AmountDate = '20060901'
> DROP TABLE #Testdata
>
> My problem is with account C where there is not a record for
> '20060801'. If there is not a previous record I want to assume 0 so my
> expected results for account C would 20.
>
> Thanks as always...
>

14 Sep 2006 10:15 PM

Q

You can use the GROUP BY:

SELECT TD1.Account, sum(TD1.Amount)
FROM #Testdata TD1
WHERE TD1.AmountDate in ('20060801', '20060901')
GROUP BY TD1.Account

Q

Show quote

"Terri" wrote:

> I am using a self-join to add amounts with different dates in the same
> table.
>
> CREATE TABLE #Testdata
> (
> Account char(1),
> AmountDate datetime,
> Amount int
> )
>
> INSERT INTO #Testdata (Account, AmountDate, Amount) VALUES ('A',
> '20060801',5)
> INSERT INTO #Testdata (Account, AmountDate, Amount) VALUES ('A',
> '20060901',3)
> INSERT INTO #Testdata (Account, AmountDate, Amount) VALUES ('B',
> '20060801',10)
> INSERT INTO #Testdata (Account, AmountDate, Amount) VALUES ('B',
> '20060901',4)
> INSERT INTO #Testdata (Account, AmountDate, Amount) VALUES ('C',
> '20060901',20)
>
> SELECT
> TD1.Account,
> TD1.Amount + TD2.Amount
> FROM #Testdata TD1 INNER JOIN #Testdata TD2 ON TD1.Account = TD2.Account
> WHERE TD1.AmountDate = '20060801' AND TD2.AmountDate = '20060901'
>
> DROP TABLE #Testdata
>
> My problem is with account C where there is not a record for '20060801'. If
> there is not a previous record I want to assume 0 so my expected results for
> account C would 20.
>
> Thanks as always...
>
>
>