SELECT Sum(TotalPaidAmount) FROM
(SELECT 'FY' + y1 + '/' + y2 AS FiscalYear,QuarterNumber,(SELECT CASE WHEN Month(PaidDate)>9 THEN (Year(PaidDate) + 1) ELSE Year(PaidDate) END) as PaidYear,Month(PaidDate) as PaidMonth,SUM(PaidAmount) AS TotalPaidAmount,PaidDate FROM (SELECT RIGHT(DATEPART(YEAR, DATEADD(MONTH, -9, PaidDate)), 2) AS y1,RIGHT(DATEPART(YEAR, DATEADD(MONTH, +3, PaidDate)), 2) AS y2,'Q'+ Convert(varchar(10), (DATEPART(QUARTER, DATEADD(MONTH, 3, PaidDate)))) AS QuarterNumber,PaidAmount,PaidDate FROM Invoice WHERE Invoice.InvoiceStatusId =2 AND Invoice.InvoiceId IN (SELECT DISTINCT I.InvoiceId FROM Invoice I INNER JOIN InvoiceType IT ON I.InvoiceTypeId = IT.InvoiceTypeId INNER JOIN InvoiceProject IP ON I.InvoiceId = IP.InvoiceId
INNER JOIN Phase PP ON IP.tPhaseId = PP.PhaseId
INNER JOIN Project P ON P.ProjectId = PP.ProjectId
INNER JOIN tblMaster MR ON MR.MasterId = P.ProjectId
WHERE P.ProjectId='123-456-987')) AS d GROUP BY y1,y2,QuarterNumber,PaidDate) SomeTable 
WHERE PaidYear=2008 AND QuarterNumber='Q3'

quote:

---

Originally posted by rum23

I can't try Dinakar's solution because I'm trying to compute on the data generated on fly. The generated data is not saved in any table.

I want to stay way from creating temporary tables, if possible.

Please help. Thanks!

---