G
Guest
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.
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.