Subquery to calculate the YTD amount

**xraive** · Jul 28 '10, 01:55 PM

Hi I am trying to calculate the YTD amount and I keep getting the following error "You tried to execute a query that does note include the specified expression 'Fiscal Year' as part of an aggregate function". Can you offer any assistance?

Code:

SELECT GetFiscalYear([InvoiceDate]) AS [Fiscal Year], fFiscalPeriod([InvoiceDate]) AS Period, DatePart("q",[invoicedate]) AS Quarter, DatePart("m",[invoicedate]) AS [Month], MonthName(DatePart("m",[invoicedate])) AS [Month Name], DatePart("y",[invoicedate]) AS [Day of Year], DatePart("d",[invoicedate]) AS [Day], DatePart("w",[invoicedate]) AS Weekday, WeekdayName(DatePart("w",[invoicedate])) AS [Weekday Name], DatePart("ww",[invoicedate]) AS Week, InvoiceDetails.BudgetCodeID, Sum(InvoiceDetails.Amount) AS SumOfAmount,
(SELECT Sum(InvoiceDetails.amount) AS YTD                                
   FROM Invoices AS Inv INNER JOIN InvoiceDetails AS InvD ON Inv.InvoiceID = InvD.InvoiceID   
   WHERE Inv.InvoiceDate >= DateSerial(Year([Invoices].[InvoiceDate]),1,1)               
     AND Inv.InvoiceDate < DateSerial(Year([Invoices].[InvoiceDate]),                    
       Month([Invoices].[InvoiceDate]) + 1, 1)) AS YTDAmount
FROM Invoices INNER JOIN InvoiceDetails ON Invoices.InvoiceID = InvoiceDetails.InvoiceID
GROUP BY GetFiscalYear([InvoiceDate]), fFiscalPeriod([InvoiceDate]), DatePart("q",[invoicedate]), DatePart("m",[invoicedate]), MonthName(DatePart("m",[invoicedate])), DatePart("y",[invoicedate]), DatePart("d",[invoicedate]), DatePart("w",[invoicedate]), WeekdayName(DatePart("w",[invoicedate])), DatePart("ww",[invoicedate]), InvoiceDetails.BudgetCodeID;

Thank you,