Loop SQL Script

 

 

Create Procedure [dbo].[Loop]
as

Begin

--
-- Run Loop Statement for Expense Allocations
--

--Delete current month's information

Delete from allocation_detail
where yearmonth =
cast(year(getdate()- day(getdate()))as varchar) +
Case
When month(getdate()- day(getdate()))<10
Then '0' + cast(month(getdate() - day(getdate())) as varchar)
Else cast(month(getdate() - day(getdate())) as varchar)
End

--Add in Level zero for month
Insert into allocation_detail
(
YearMonth,
GL_Account,
Branch,
Alloc_Lvl,
Amount,
Prod_Grp,
Lvl,
GL_Account_Orig,
Branch_Orig,
Prod_Grp_Orig
)
Select
YearMonth,
GL_Account,
Branch,
Alloc_Lvl,
Amount_MTD,
Prod_Grp,
0 as Lvl,
GL_Account,
Branch,
Prod_Grp
From allocation_detail_0
where YearMonth =
cast(year(getdate()- day(getdate()))as varchar) +
Case
When month(getdate()- day(getdate()))<10
Then '0' + cast(month(getdate() - day(getdate())) as varchar)
Else cast(month(getdate() - day(getdate())) as varchar)
End

--Loop for all allocation levels

--Loop Function

--Declare variables for Loop

declare @alloc_lvl as int
set @alloc_lvl = (select min(l.alloc_lvl) from pcp_alloc_lvl l where l.lock_flag = 'n')
select @alloc_lvl

declare @yrmth as int
set @yrmth =
cast(year(getdate()- day(getdate()))as varchar) +
Case
When month(getdate()- day(getdate()))<10
Then '0' + cast(month(getdate() - day(getdate())) as varchar)
Else cast(month(getdate() - day(getdate())) as varchar)
End

select @yrmth
--Loop Function
While @alloc_lvl <= (select max(l.alloc_lvl) from pcp_alloc_lvl l)
Begin


-- CCtoCC Allocation

Create Table #CCtoCC
(
YearMonth int not null,
Branch_Orig int not null,
GL_Account nvarchar(10) not null,
Branch_New int not null,
Pct_Allocated decimal (8,6) not null,
Total decimal (12,2) not null,
Amount decimal (12,2) not null
)

Insert into #CCtoCC
(
YearMonth,
Branch_Orig,
GL_Account,
Branch_New,
Pct_Allocated,
Total,
Amount
)
Select
d.YearMonth,
d.Branch_Orig,
e.GL_Account,
d.Branch_New,
d.Pct_Allocated,
sum(e.Amount) as Total,
d.Pct_Allocated * sum(e.Amount) as Amount
from pcp_alloc_cctocc d inner join pcp_alloc_cc a on a.branch = d.branch_orig
left outer join allocation_detail e on e.branch = d.branch_orig
where d.yearmonth = @yrmth and
e.yearmonth = @yrmth and
a.alloc_lvl = @alloc_lvl and
e.Lvl = @alloc_lvl - 1
group by d.yearmonth, d.branch_orig, e.GL_Account, d.branch_new, d.pct_allocated

Insert into allocation_detail
(YearMonth,
GL_Account,
GL_Account_Orig,
Branch_Orig,
Branch,
Alloc_Lvl,
Pct_Allocated,
Amount,
Lvl,
Alloc_Method)
Select
c.YearMonth,
c.GL_Account,
c.GL_Account,
c.Branch_Orig,
c.Branch_New,
a.alloc_lvl,
c.Pct_Allocated,
c.Amount,
@alloc_lvl as Lvl,
'CCtoCC' as Alloc_Method
From #CCtoCC c inner join pcp_alloc_cc a on a.branch = c.branch_new

drop table #CCtoCC

-- CCtoProd Allocation

Create Table #CCtoProd
(
YearMonth int not null,
Branch_Orig int not null,
GL_Account nvarchar(10) not null,
Prod_Grp nvarchar(50) not null,
Pct_Allocated decimal (8,6) not null,
Total decimal (12,2) not null,
Amount decimal (12,2) not null
)

Insert into #CCtoProd
(
YearMonth,
Branch_Orig,
GL_Account,
Prod_Grp,
Pct_Allocated,
Total,
Amount
)
Select
d.YearMonth,
d.Branch_Orig,
e.GL_Account,
d.Prod_Grp,
d.Pct_Allocated,
sum(e.Amount) as Total,
d.Pct_Allocated * sum(e.Amount) as Amount
from pcp_alloc_cctoprod d inner join pcp_alloc_cc a on a.branch = d.branch_orig
left outer join allocation_detail e on e.branch = d.branch_orig
where d.yearmonth = @yrmth and
e.yearmonth = @yrmth and
a.alloc_lvl = @alloc_lvl and
e.lvl = @alloc_lvl - 1
group by d.yearmonth, d.branch_orig, e.GL_Account, d.prod_grp, d.pct_allocated

Insert into allocation_detail
(YearMonth,
Branch,
GL_Account,
GL_Account_Orig,
Alloc_Lvl,
Amount,
Prod_Grp,
Lvl,
Alloc_Method,
Branch_Orig,
Pct_Allocated)
Select
c.YearMonth,
c.Branch_Orig,
c.GL_Account,
c.GL_Account,
a.alloc_lvl,
c.Amount,
c.Prod_Grp,
@alloc_lvl as Lvl,
'CCtoProd' as Alloc_Method,
c.Branch_Orig,
c.Pct_Allocated
From #CCtoProd c inner join pcp_alloc_cc a on a.branch = c.branch_orig

drop table #CCtoProd

--HCtoProd Allocation

Create Table #HCtoProd
(
YearMonth int not null,
Branch_Orig int not null,
GL_Account nvarchar(10) not null,
FName nvarchar(50) not null,
--MName nvarchar(50) null,
LName nvarchar(50) not null,
Prod_Grp nvarchar(50) not null,
Pct_Allocated decimal (8,6) not null,
Total decimal (12,2) not null,
Amount decimal (12,2) not null
)

Insert into #HCtoProd
(
YearMonth,
Branch_Orig,
GL_Account,
FName,
--MName,
LName,
Prod_Grp,
Pct_Allocated,
Total,
Amount
)
Select
d.YearMonth,
e.Branch,
e.GL_Account,
d.FName,
--d.MName,
d.LName,
d.Prod_Grp,
d.Pct_Allocated as Pct_Allocated,
sum(e.Amount) as Total,
d.Pct_Allocated * sum(e.Amount) * s.Salary_Pct as Amount
from pcp_alloc_hctoprod d inner join pcp_alloc_hc_salary s on (s.fname = d.fname and s.lname = d.lname and s.yearmonth = d.yearmonth)
inner join gbl_hc h on (h.fname = d.fname and h.lname = d.lname and h.yearmonth = d.yearmonth)
left outer join allocation_detail e on (e.branch = h.cc and e.yearmonth = d.yearmonth)
inner join pcp_alloc_cc a on a.branch = h.cc
where d.yearmonth = @yrmth and
s.yearmonth = @yrmth and
e.yearmonth = @yrmth and
a.alloc_lvl = @alloc_lvl and
e.Lvl = @alloc_lvl - 1
group by d.yearmonth, e.Branch, e.GL_account, d.FName,
--d.MName,
d.LName, d.prod_grp, d.pct_allocated, s.salary_pct

Insert into allocation_detail
(YearMonth,
Branch,
GL_Account,
GL_Account_Orig,
Alloc_Lvl,
Amount,
Prod_Grp,
Lvl,
Alloc_Method,
Branch_Orig,
Pct_Allocated)
Select
c.YearMonth,
c.Branch_Orig,
c.GL_Account,
c.GL_Account,
a.alloc_lvl,
c.Amount,
c.Prod_Grp,
@alloc_lvl as Lvl,
'HCtoProd' as Alloc_Method,
c.Branch_Orig,
c.Pct_Allocated
From #HCtoProd c inner join pcp_alloc_cc a on a.branch = c.branch_orig

drop table #HCtoProd

--CCtoAlloc_Lvl Allocation

--Create HC by Alloc Temp Table
Create Table #Alloc_Lvl_HC
(
YearMonth int not null,
CC int not null,
Alloc_Lvl int not null,
HC int not null
)

Insert into #Alloc_Lvl_HC
(YearMonth, Alloc_Lvl, CC, HC)
select
h.YearMonth, c.Alloc_Lvl, h.cc, count(h.FName)
from gbl_HC h inner join pcp_alloc_cc c on c.branch = h.cc
group by h.yearmonth, c.alloc_lvl, h.cc

Create Table #CCtoAlloc_Lvl
(
YearMonth int not null,
Branch_Orig int not null,
GL_Account nvarchar(10) not null,
Alloc_Lvl int not null,
Branch_New int not null,
Pct_Allocated decimal (8,6) not null,
Total decimal (12,2) not null,
Amount decimal (12,2) not null
)

Insert into #CCtoAlloc_Lvl
(
YearMonth,
Branch_Orig,
GL_Account,
Alloc_Lvl,
Branch_New,
Pct_Allocated,
Total,
Amount
)
Select
d.YearMonth,
d.Branch_Orig,
e.GL_Account,
d.Alloc_Lvl,
a2.cc,
d.Pct_Allocated,
sum(e.Amount) as Total,
d.Pct_Allocated * sum(e.Amount) *
(Select sum(hc) from #alloc_lvl_hc where cc = a2.cc and yearmonth = @yrmth)*1.00 /
(Select sum(hc) from #alloc_lvl_hc where alloc_lvl = d.alloc_lvl and yearmonth = @yrmth)*1.00
as Amount
from pcp_alloc_cctoalloc_lvl d inner join pcp_alloc_cc a on a.branch = d.branch_orig
left outer join allocation_detail e on e.branch = d.branch_orig
inner join #alloc_lvl_hc a2 on (a2.alloc_lvl = d.alloc_lvl and a2.yearmonth = d.yearmonth)
where d.yearmonth = @yrmth and
e.yearmonth = @yrmth and
a.alloc_lvl = @alloc_lvl and
e.Lvl = @alloc_lvl - 1
group by d.yearmonth, d.branch_orig, e.GL_Account, d.alloc_lvl, d.pct_allocated, a2.cc

Insert into allocation_detail
(YearMonth,
Branch,
GL_Account,
GL_Account_Orig,
Alloc_Lvl,
Amount,
Lvl,
Alloc_Method,
Branch_Orig,
Pct_Allocated)
Select
c.YearMonth,
c.Branch_New,
c.GL_Account,
c.GL_Account,
a.alloc_lvl,
c.Amount,
@alloc_lvl as Lvl,
'CCtoAlloc_Lvl' as Alloc_Method,
c.Branch_Orig,
c.Pct_Allocated
From #CCtoAlloc_Lvl c inner join pcp_alloc_cc a on a.branch = c.branch_new

drop table #CCtoAlloc_Lvl

drop table #alloc_lvl_hc

--GLCCtoProd Allocation

Create Table #GLCCtoProd
(
YearMonth int not null,
GL_Account nvarchar(10) not null,
GL_Account_Orig nvarchar(10) not null,
Branch_Orig int not null,
Prod_Grp nvarchar(50) not null,
Pct_Allocated decimal (8,6) not null,
Total decimal (12,2) not null,
Amount decimal (12,2) not null
)

Insert into #GLCCtoProd
(
YearMonth,
GL_Account,
GL_Account_Orig,
Branch_Orig,
Prod_Grp,
Pct_Allocated,
Total,
Amount
)
Select
d.YearMonth,
d.GL_Account,
e.GL_Account,
d.Branch_Orig,
d.Prod_Grp,
d.Pct_Allocated,
(select sum(Amount) from allocation_detail where gl_account = d.gl_account and branch = d.branch_orig
and Lvl = @alloc_lvl - 1) as Total,
d.Pct_Allocated * sum(e.Amount) as Amount
from pcp_alloc_glcctoprod d inner join pcp_alloc_cc a on a.branch = d.branch_orig
left outer join allocation_detail e on (e.branch = d.branch_orig and e.gl_account = d.gl_account)
where d.yearmonth = @yrmth and
e.yearmonth = @yrmth and
a.alloc_lvl = @alloc_lvl and
e.Lvl = @alloc_lvl - 1
group by d.yearmonth, d.gl_account, e.gl_account, d.branch_orig, d.prod_grp, d.pct_allocated

Insert into allocation_detail
(YearMonth,
GL_Account,
GL_Name,
Branch,
Alloc_Lvl,
Amount,
Prod_Grp,
Lvl,
Alloc_Method,
GL_Account_Orig,
Branch_Orig,
Pct_Allocated)
Select
c.YearMonth,
c.GL_Account,
g.GL_Name,
c.Branch_Orig,
a.alloc_lvl,
c.Amount,
c.Prod_Grp,
@alloc_lvl as Lvl,
'GLCCtoProd' as Alloc_Method,
c.GL_Account_Orig,
c.Branch_Orig,
c.Pct_Allocated
From #GLCCtoProd c inner join gbl_gl g on c.gl_account = g.gl_account
inner join pcp_alloc_cc a on a.branch = c.branch_orig

drop table #GLCCtoProd

--CCtoHCRem Allocation (Take all cost center expenses and allocate to all remaining cost centers on headcount)

--Create HC by Alloc Temp Table
Create Table #Alloc_Lvl_HC2
(
YearMonth int not null,
CC int not null,
Alloc_Lvl int not null,
HC int not null
)

Insert into #Alloc_Lvl_HC2
(YearMonth, Alloc_Lvl, CC, HC)
select
h.YearMonth, c.Alloc_Lvl, h.cc, count(h.FName)
from gbl_HC h inner join pcp_alloc_cc c on c.branch = h.cc
where c.alloc_lvl > @alloc_lvl
and h.yearmonth = @yrmth
group by h.yearmonth, c.alloc_lvl, h.cc

Create Table #CCtoHCRem
(
YearMonth int not null,
Branch_Orig int not null,
GL_Account nvarchar(10) not null,
Branch_New int not null,
Pct_Allocated decimal (8,6) not null,
Total decimal (12,2) not null,
Amount decimal (12,2) not null
)

Insert into #CCtoHCRem
(
YearMonth,
Branch_Orig,
GL_Account,
Branch_New,
Pct_Allocated,
Total,
Amount
)
Select
d.YearMonth,
d.Branch_Orig,
e.GL_Account,
a2.cc,
d.Pct_Allocated,
sum(e.Amount) as Total,
d.Pct_Allocated * sum(e.Amount) *
(Select sum(hc) from #alloc_lvl_hc2 where cc = a2.cc and yearmonth = @yrmth) /
(Select sum(hc) from #alloc_lvl_hc2 where yearmonth = @yrmth)
as Amount
from pcp_alloc_cctohcrem d inner join pcp_alloc_cc a on a.branch = d.branch_orig
left outer join allocation_detail e on e.branch = d.branch_orig
inner join #alloc_lvl_hc2 a2 on (a2.yearmonth = d.yearmonth)
where d.yearmonth = @yrmth and
e.yearmonth = @yrmth and
a.alloc_lvl = @alloc_lvl and
e.Lvl = @alloc_lvl - 1
group by d.yearmonth, d.branch_orig, e.gl_account, a2.cc, d.pct_allocated

Insert into allocation_detail
(YearMonth,
Branch,
GL_Account,
GL_Account_Orig,
Alloc_Lvl,
Amount,
Lvl,
Alloc_Method,
Branch_Orig,
Pct_Allocated)
Select
c.YearMonth,
c.Branch_New,
c.GL_Account,
c.GL_Account,
a.alloc_lvl,
c.Amount,
@alloc_lvl as Lvl,
'CCtoHCRem' as Alloc_Method,
c.Branch_Orig,
c.Pct_Allocated
From #CCtoHCRem c inner join pcp_alloc_cc a on a.branch = c.branch_new

drop table #CCtoHCRem

drop table #alloc_lvl_hc2

--CCtoBldgHC Allocation (Allocate cost center to building group; assumes each cost center to one building)

--Create HC by Alloc Temp Table
Create Table #BldgHC
(
YearMonth int not null,
CC int not null,
Alloc_Lvl int not null,
HC int not null,
Loc nvarchar(50) not null
)

Insert into #BldgHC
(YearMonth, Alloc_Lvl, CC, HC, Loc)
select
h.YearMonth, c.Alloc_Lvl, h.cc, count(h.FName), g.Loc
from gbl_HC h inner join pcp_alloc_cc c on c.branch = h.cc
inner join gbl_cc g on g.cc = h.cc
where c.alloc_lvl > @alloc_lvl and g.loc is not null
and h.yearmonth = @yrmth
group by h.yearmonth, c.alloc_lvl, h.cc, g.loc

Create Table #CCtoBldgHC
(
YearMonth int not null,
Branch_Orig int not null,
GL_Account nvarchar(10) not null,
Branch_New int not null,
Loc nvarchar(50) not null,
Total decimal (12,2) not null,
Amount decimal (12,2) not null
)

Insert into #CCtoBldgHC
(
YearMonth,
Branch_Orig,
GL_Account,
Branch_New,
Loc,
Total,
Amount
)
Select
d.YearMonth,
d.Branch_Orig,
e.GL_Account,
a2.cc,
a2.loc,
sum(e.Amount) as Total,
sum(e.Amount) *
(Select sum(hc) from #bldgHC where cc = a2.cc and yearmonth = @yrmth) /
(Select sum(hc) from #bldgHC where loc = a2.loc and yearmonth = @yrmth)
as Amount
from pcp_alloc_cctoloc d inner join pcp_alloc_cc a on a.branch = d.branch_orig
left outer join allocation_detail e on e.branch = d.branch_orig
inner join #bldgHC a2 on (a2.yearmonth = d.yearmonth and a2.loc = d.loc_new)
where d.yearmonth = @yrmth and
e.yearmonth = @yrmth and
a.alloc_lvl = @alloc_lvl and
e.Lvl = @alloc_lvl - 1
group by d.yearmonth, d.branch_orig, e.GL_Account, a2.cc, a2.loc

Insert into allocation_detail
(YearMonth,
Branch,
GL_Account,
GL_Account_Orig,
Alloc_Lvl,
Amount,
Lvl,
Alloc_Method,
Branch_Orig)
Select
c.YearMonth,
c.Branch_New,
c.GL_Account,
c.GL_Account,
a.alloc_lvl,
c.Amount,
@alloc_lvl as Lvl,
'CCtoBldgHC' as Alloc_Method,
c.Branch_Orig
From #CCtoBldgHC c inner join pcp_alloc_cc a on a.branch = c.branch_new

drop table #CCtoBldgHC

drop table #bldgHC

-- Add in unallocated portion of current allocation level

Insert into allocation_detail
(
YearMonth,
GL_Account,
Branch,
Alloc_Lvl,
Amount,
Prod_Grp,
Lvl,
Alloc_Method,
GL_Account_Orig,
Branch_Orig,
Prod_Grp_Orig,
Pct_Allocated
)
Select
YearMonth,
GL_Account,
l.Branch,
l.Alloc_Lvl,
Amount,
Prod_Grp,
@alloc_lvl as Lvl,
Alloc_method,
GL_Account_Orig,
Branch_Orig,
Prod_Grp_Orig,
Pct_Allocated
From allocation_detail l inner join pcp_alloc_cc c on c.branch = l.branch
where YearMonth =
cast(year(getdate()- day(getdate()))as varchar) +
Case
When month(getdate()- day(getdate()))<10
Then '0' + cast(month(getdate() - day(getdate())) as varchar)
Else cast(month(getdate() - day(getdate())) as varchar)
End

and c.alloc_lvl <> @alloc_lvl
and lvl = @alloc_lvl - 1
-- Loop Function continued
Update pcp_alloc_lvl
set lock_flag = 'Y' where alloc_lvl = @alloc_lvl
set @alloc_lvl = @alloc_lvl + 1
End

-- Reset Allocation Levels to beginning positions
Update pcp_alloc_lvl
set lock_flag = 'N'

--End Statement

--
-- Checks
--

-- Overhead Allocation Check

Select
Lvl,
sum(amount) as Total
from allocation_detail
where yearmonth =
cast(year(getdate()- day(getdate()))as varchar) +
Case
When month(getdate()- day(getdate()))<10
Then '0' + cast(month(getdate() - day(getdate())) as varchar)
Else cast(month(getdate() - day(getdate())) as varchar)
End
group by lvl
order by lvl asc

End


Back to Macros

Back to Resume

Back to About Me