SQL Statement Help

[jrhine]

I have the following SQL statement:

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:

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

[Si_the_geek]

You need to remove the union, mix your queries together, and perform the same joins etc as before. Just give one of the instances of the table a different alias so it is treated as a separate table.

Please note your syntax style is different to mine (sql92 instead of sql89), so I might have made an obvious mistake somewhere. I think this is about right:

SELECT
A.AcctID as 'AcctID',
(RTrim(Str(A.AcctNumber)) +' '+ LTrim(Right(A.AcctClusterKey,2))) as 'AcctNumber',
(A.SharesAsOf010199+Sum(T1.Shares)) AS 'Balance1',
(A.SharesAsOf010199+Sum(T2.Shares)) AS 'Balance2'

FROM (Accounts A
JOIN Transactions T1 ON A.AcctID = T1.AcctID)
JOIN Transactions T2 ON A.AcctID = T1.AcctID

WHERE
(T1.DateEffective Between '01/01/99' AND '08/01/2001') AND (T1.FNReversal = 0)

AND (T2.DateEffective Between '01/01/99' AND '08/15/2001') AND (T2.FNReversal = 0)

GROUP BY A.AcctID, A.SharesAsOf010199, A.AcctNumber, A.AcctClusterKey

[Mongo]

You know what you want, the trick is to refine an SQL statement
that has minimal impact on your server in producing results, i. e.,
don't duplicate conditions or force unnecessary server effort...

To that end, this is how I might do it:

SELECT 
   t.AcctID
 , AcctNumber
 , SUM( BegMoShares ) + SharesAsOf010199 as Balance1
 , SUM( MidMoShares ) + SharesAsOf010199 as Balance2
FROM 
  -- build smallest Transaction "table object" via a subquery
  (SELECT   
     AcctID
     -- preposition applicable beginning of month data
   , CASE 
       WHEN DateEffective < '8/2/01' THEN Shares 
       ELSE 0
     END 
   , Shares 
   FROM Transactions
   WHERE
     -- use SARG for "just the facts" limit
     FNReversal = 0
     -- where possible omit BETWEEN useage (every sperm counts! *Wink*)
     --   ideally, if this datetime field never includes time, 
     --   use GT EOM Dec, instead of GTE FOM Jan
     AND DateEffective >= '1/1/99'
     AND DateEffective < '8/16/01') 
   AS t( AcctID, BegMoShares, MidMoShares )
JOIN
  -- relate above to Account table object subquery
  (SELECT 
     AcctID
     -- concatenate once @ lowest data-slice level
   , RTRIM(CAST(AcctNumber as char(10))) + ' ' + LTRIM(RIGHT( AcctClusterKey, 2 ))
   , SharesAsOf010199 
   FROM Accounts) 
   AS a( AcctID, AcctNumber, SharesAsOf010199 )
ON a.AcctID = t.AcctID
-- knockout single aggregate sum
GROUP BY 
  t.AcctID
, AcctNumber
, SharesAsOf010199

I've assumed you do not permit null in either Tranactions.Shares or Accounts.SharesAsOf010199;
if you do, you'll definitely need to add ISNULL( fieldname, 0 ) at the appropriate SELECT point(s).

Additionally, indexes that include Transactions ( AcctID, FNReversal, DateEffective ) and
Accounts ( AcctID, AcctNumber, AcctClusterKey ) should be used.

Hopefully, this will help you out and give you an idea or two.

[jrhine]

Thanks! I'll give it a go

-jrhine