YTD and MTD

[ria.arora]

Hi

I am calculating YTD and MTD amount. I am using sub query

SELECT "VolatileRevenue" AS Comment, TB.[Private Banker], Sum(TB.RevenueInUSD) AS SumOfRevenueInUSD, tbl_Oneoff_Adjust.[Volatile Adjust], tbl_Revenue_Target.[2012 FY Trade Revenue Target], 
(SELECT Sum(TBI.RevenueInUSD) AS YTDSumOfRevenueInUSD
FROM (tbl_TradeBook AS TBI INNER JOIN tbl_Weekly_Calendar AS tbl_Weekly_Calendar1 ON TBI.ValBookDate = tbl_Weekly_Calendar1.[Calendar Date])
WHERE TBI.BU="Asia" AND tbl_Weekly_Calendar1.[Week Number] <= 23  AND TBI.[Private Banker] = TB.[Private Banker]) AS YTDAmt
FROM (((tbl_TradeBook AS TB LEFT JOIN tbl_BPReport ON TB.[Private Banker] = tbl_BPReport.[Private Banker]) LEFT JOIN tbl_Oneoff_Adjust ON TB.[Private Banker] = tbl_Oneoff_Adjust.[Private Banker]) LEFT JOIN tbl_Revenue_Target ON TB.[Private Banker] = tbl_Revenue_Target.[Private Banker]) INNER JOIN tbl_Weekly_Calendar ON TB.ValBookDate = tbl_Weekly_Calendar.[Calendar Date]
WHERE (((TB.BU)="Asia") AND ((Year([ValBookDate]))='2012') AND ((tbl_Weekly_Calendar.[Week Number])=23))
GROUP BY TB.[Private Banker], tbl_Oneoff_Adjust.[Volatile Adjust], tbl_Revenue_Target.[2012 FY Trade Revenue Target];

Issue is above query is taking lot of time because tbl_TradeBook has more than 100K records and output will return around 100 records and sub query also will return around 90 records because there are few bankers who have left the bank will not have MTD number but will have YTD numbers only.

Please let me know how to tuneup the SQL I am going run this SQL in VBA to insert the records in another table. So I run two differnt SQLs as well 1st SQL to insert YTD number in the table and second SQL to update MTD numbers. Pls let me know what is the best option?

 

[spikepl]

Without any deeper comprehension of your SQL:

1. Have you put index on all fields appearing in WHERE and GROUP BY? If not then do so.
2. Year([ValBookDate]))='2012' is a poor way of expressing it, because you force a calculation for each record, instead of letting the optimizer do its job.
   ValBookDate BETWEEN #01/01/2012# AND #12/31/2012# is faster