I've got a SQL query in an Excel spreadsheet.

Unfortunately, three lines are time dependent and need to be updated on a
monthly basis.

The query follows:

/*************/

select Sub.AdviserDisplay,

sum(Sub.YTDExistingClients) as YTDExistingClients,

sum(Sub.YTDNewClients) as YTDNewClients,

sum(Sub.YTDAllClients) as YTDAllClients,

sum(Sub.PYTDExistingClients) as PYTDExistingClients,

sum(Sub.PYTDNewClients) as PYTDNewClients,

sum(Sub.PYTDAllClients) as PYTDAllClients

from

(

--Existing Clients

select AdviserDisplay,

count(distinct Assured1Id) as YTDExistingClients,

0 as YTDNewClients,

0 as YTDAllClients,

0 as PYTDExistingClients,

0 as PYTDNewClients,

0 as PYTDAllClients

from fct_Policy f

inner join vdw_TimeCalendar t

on f.DateTransactionId = t.TimeId

inner join vdw_Adviser a

on f.AdviserId = a.AdviserId

where t.timemonthkey between 200701 and 200702

and f.ExistingClient in (1,2)

group by AdviserDisplay



union all



--New Clients

select AdviserDisplay,

0 as YTDExistingClients,

count(distinct Assured1Id) as YTDNewClients,

0 as YTDAllClients,

0 as PYTDExistingClients,

0 as PYTDNewClients,

0 as PYTDAllClients

from fct_Policy f

inner join vdw_TimeCalendar t

on f.DateTransactionId = t.TimeId

inner join vdw_Adviser a

on f.AdviserId = a.AdviserId

where t.timemonthkey between 200701 and 200702

and f.ExistingClient = 0

group by AdviserDisplay



union all



--All Clients

select AdviserDisplay,

0 as YTDExistingClients,

0 as YTDNewClients,

count(distinct Assured1Id) as YTDAllClients,

0 as PYTDExistingClients,

0 as PYTDNewClients,

0 as PYTDAllClients

from fct_Policy f

inner join vdw_TimeCalendar t

on f.DateTransactionId = t.TimeId

inner join vdw_Adviser a

on f.AdviserId = a.AdviserId

where t.timemonthkey between 200701 and 200702

group by AdviserDisplay



union all



--Existing Clients Previous Year

select AdviserDisplay,

0 as YTDExistingClients,

0 as YTDNewClients,

0 as YTDAllClients,

count(distinct Assured1Id) as PYTDExistingClients,

0 as PYTDNewClients,

0 as PYTDAllClients

from fct_Policy f

inner join vdw_TimeCalendar t

on f.DateTransactionId = t.TimeId

inner join vdw_Adviser a

on f.AdviserId = a.AdviserId

where t.timemonthkey between 200601 and 200602

and f.ExistingClient in (1,2)

group by AdviserDisplay



union all



--New Clients Previous Year

select AdviserDisplay,

0 as YTDExistingClients,

0 as YTDNewClients,

0 as YTDAllClients,

0 as PYTDExistingClients,

count(distinct Assured1Id) as PYTDNewClients,

0 as PYTDAllClients

from fct_Policy f

inner join vdw_TimeCalendar t

on f.DateTransactionId = t.TimeId

inner join vdw_Adviser a

on f.AdviserId = a.AdviserId

where t.timemonthkey between 200601 and 200602

and f.ExistingClient = 0

group by AdviserDisplay



union all



--All Clients Previous Year

select AdviserDisplay,

0 as YTDExistingClients,

0 as YTDNewClients,

0 as YTDAllClients,

0 as PYTDExistingClients,

0 as PYTDNewClients,

count(distinct Assured1Id) as PYTDAllClients

from fct_Policy f

inner join vdw_TimeCalendar t

on f.DateTransactionId = t.TimeId

inner join vdw_Adviser a

on f.AdviserId = a.AdviserId

where t.timemonthkey between 200601 and 200602

group by AdviserDisplay



) sub

group by Sub.AdviserDisplay

order by Sub.AdviserDisplay

/***************/

Date entries such as 200701 (Jan 2007) would need to be adjusted.

As there are a lot of these and it would be easy to get wrong, I'd like to
automate update of these.

Can I use VBA or a similar tool for this purpose.

Let's say the dates required are currently sat in cells somewhere in the
workbook - for the sake of the example, Master!A1, B1 etc.

Thanks for any guidance or suggestions you can provide.

Cheers,

Tom.