Highest Balance

[JSHoltsIT]

I have a report that gives me Customer Number, Name, Credit Limit, Sales Rep, Current Year Sales, Current Year High Balance, and then sales and high balance for last year.

The problem is, my high balance that I currently have is the largest invoice amount. I need to work this query

--- start high balance query
WITH [CustTrans ORDERED BY ROWID] AS
(
SELECT ROW_NUMBER() OVER (ORDER BY TransDate ASC) AS ROWID
, AccountNum
, TransDate
, AmountMST

FROM CustTrans
WHERE AccountNum = '21321546'
AND DataAreaID = 'etl'
AND (TransDate BETWEEN '11/1/2009' AND '12/1/2009')
)


,[CustTrans Running Total] AS
(
SELECT ROWID
, AccountNum
, TransDate
, AmountMST
, (select sum(AmountMST) from [CustTrans ORDERED BY ROWID]
where ROWID <= CTobr.ROWID
) AS RunningTotal
FROM [CustTrans ORDERED BY ROWID] CTobr

)

SELECT MAX(RunningTotal), AccountNum FROM [CustTrans Running Total]
GROUP BY AccountNum
--- end high balance query

into the Current Year High Balance INNER JOIN of the dataset below. Natively, the CustTrans table does not have a reliable "unique" record ID (null enabled, software assigns id).

The High Balance query works fine in a query window, but SSRS does not like the introduction of my query.

Thanks in advance.

Jason


-- begin primarydataset
DECLARE @FirstDayOfYear DateTime, @FirstDayPreviousYear DateTime, @LastDayPreviousYear DateTime
SET @FirstDayOfYear = DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)
SET @FirstDayPreviousYear = DATEADD(yy,-1,@FirstDayOfYear)
SET @LastDayPreviousYear = DATEADD(dd,-1,@FirstDayOfYear)

SELECT
CustTable.AccountNum
,CustTable.Name
,CustTable.CreditMax
,dimensions.Description
,expCurrentYearSales.ApproxSales
,expCurrentYearHighBalance.SalesHighBalance
,expPreviousYearSales.SalesPreviousYear
,expPreviousYearHighBalance.SalesHighBalancePreviousYear

FROM CustTable
INNER JOIN dimensions ON dimensions.DataAreaID = @DataAreaID
AND dimensions.DimensionCode = 0
AND dimensions.num = CustTable.dimension

INNER JOIN (
-- Current Year Sales
SELECT SUM(CustInvoiceJour.InvoiceAmount) AS ApproxSales, CustInvoiceJour.InvoiceAccount, CustInvoiceJour.DataAreaID FROM CustInvoiceJour
WHERE CustInvoiceJour.InvoiceDate BETWEEN @FirstDayOfYear AND getdate()
AND CustInvoiceJour.DataAreaID = @DataAreaID
GROUP BY CustInvoiceJour.InvoiceAccount, CustInvoiceJour.DataAreaID

) as expCurrentYearSales ON expCurrentYearSales.InvoiceAccount = CustTable.AccountNum

INNER JOIN (
-- Current Year High Balance
SELECT MAX(CustTrans.AmountCur) AS SalesHighBalance, CustTrans.AccountNum, CustTrans.DataAreaID FROM CustTrans
WHERE CustTrans.TransDate BETWEEN @FirstDayOfYear AND getdate()
AND CustTrans.DataAreaID = @DataAreaID
GROUP BY CustTrans.AccountNum, CustTrans.DataAreaID

) as expCurrentYearHighBalance ON expCurrentYearHighBalance.AccountNum = CustTable.AccountNum

LEFT OUTER JOIN (
-- Previous Year Sales
SELECT SUM(CustTrans.AmountCur) AS SalesPreviousYear, CustTrans.AccountNum, CustTrans.DataAreaID FROM CustTrans
WHERE CustTrans.TransDate BETWEEN @FirstDayPreviousYear AND @LastDayPreviousYear
AND CustTrans.DataAreaID = @DataAreaID
GROUP BY CustTrans.AccountNum, CustTrans.DataAreaID

) as expPreviousYearSales ON expPreviousYearSales.AccountNum = CustTable.AccountNum

LEFT OUTER JOIN (
-- Previous Year High Balance
SELECT MAX(CustTrans.AmountCur) AS SalesHighBalancePreviousYear, CustTrans.AccountNum, CustTrans.DataAreaID FROM CustTrans
WHERE CustTrans.TransDate BETWEEN @FirstDayPreviousYear AND @LastDayPreviousYear
AND CustTrans.DataAreaID = @DataAreaID
GROUP BY CustTrans.AccountNum, CustTrans.DataAreaID

) as expPreviousYearHighBalance ON expPreviousYearHighBalance.AccountNum = CustTable.AccountNum

WHERE

(
(CustTable.AccountNum >= @CustomerAccountRangeBegin OR @CustomerAccountRangeBegin = '')
AND
(CustTable.AccountNum <= @CustomerAccountRangeEnd OR @CustomerAccountRangeEnd = '')
)

AND CustTable.DataAreaID = @DataAreaID
-- end primarydataset