I have the following SQL statement:
Code:
SELECT
T1.AcctID as 'AcctID',
(RTrim(Str(A.AcctNumber)) +' '+ LTrim(Right(A.AcctClusterKey,2))) as 'AcctNumber',
(A.SharesAsOf010199+Sum(T1.Shares)) AS 'Balance1',
Null AS 'Balance2'
FROM Transactions T1
JOIN Accounts A ON A.AcctID = T1.AcctID
WHERE
(T1.DateEffective Between '01/01/99' AND '08/01/2001') AND
(T1.FNReversal = 0)
GROUP BY T1.AcctID, A.SharesAsOf010199, A.AcctNumber, A.AcctClusterKey
UNION ALL
SELECT
T1.AcctID as 'AcctID',
(RTrim(Str(A.AcctNumber)) +' '+ LTrim(Right(A.AcctClusterKey,2))) as 'AcctNumber',
Null AS 'Balance1',
(A.SharesAsOf010199+Sum(T1.Shares)) AS 'Balance2'
FROM Transactions T1
JOIN Accounts A ON A.AcctID = T1.AcctID
WHERE
(T1.DateEffective Between '01/01/99' AND '08/15/2001') AND
(T1.FNReversal = 0)
GROUP BY T1.AcctID, A.SharesAsOf010199, A.AcctNumber, A.AcctClusterKey
ORDER BY T1.AcctID
It returns something like this:
Code:
AcctID      AcctNumber Balance1                                 Balance2                                 
----------- ---------- ---------------------------------------- ---------------------------------------- 
1            74 08     2788553.553                              NULL
1            74 08     NULL                                     2788553.553
2            74 09     3187852.946                              NULL
2            74 09     NULL                                     3196780.551
3            74 17     2218929.008                              NULL
3            74 17     NULL                                     2215792.237
4            74 10     3155425.334                              NULL
4            74 10     NULL                                     3155425.334
5            74 11     1832098.272                              NULL
5            74 11     NULL                                     1834508.149
6            74 18     3495348.912                              NULL
6            74 18     NULL                                     3481238.023
7            74 12     3071498.511                              NULL
7            74 12     NULL                                     3071498.511
8            74 13     750442.243                               NULL
8            74 13     NULL                                     751045.820
9            74 19     1154542.022                              NULL
9            74 19     NULL                                     1154234.380
Is there any way to get each Balance (1 and 2) on the same line? I'd like to have ONE
recordset with both balances on a single line, 1 line per record (Account).

Thanks in advance!
-jrhine