------SAVE LAST YEAR's DATA -----------
select * into newbudget2016 from newbudget
----DELETE DATA IN LAST YEAR TABLES ---------
delete from newbudget
delete from newbudget2
--------COPY AND PASTE FROM NEW EXEL SPREADSHEET INTO NEWBUDGET--------
-------ARE ALL ACCOUNTS VALID IN G/L ??-------------------
select a.account_no from newbudget as a
left join
p21_view_chart_of_accts as b
on a.account_no=b.account_no
where b.account_no is null
group by a.account_no
---------------------STEP 1 ---ZERO OUT EXISTING BUDGET ENTRIES---------------
--5 records
update balances set budget_1=0, cumulative_budget_1=0, date_budget_changed=GETDATE(), date_last_modified=GETDATE() from balances
inner join
(select top 100 percent a.* from balances as a
where budget_1<>0 and year_for_period=2017
and company_no='cpc'
) as b
on balances.company_no=b.company_no and balances.account_no=b.account_no and balances.period=b.period
and balances.year_for_period=b.year_for_period
select * from balances where year_for_period=2017 and (budget_1<>0 or cumulative_budget_1<>0)
------------DATA CLEANUP ------------
update newbudget set Period1=0 where CHARINDEX('-',period1)>0
update newbudget set Period2=0 where CHARINDEX('-',period2)>0
update newbudget set Period3=0 where CHARINDEX('-',period3)>0
update newbudget set Period4=0 where CHARINDEX('-',period4)>0
update newbudget set Period5=0 where CHARINDEX('-',period5)>0
update newbudget set Period6=0 where CHARINDEX('-',period6)>0
update newbudget set Period7=0 where CHARINDEX('-',period7)>0
update newbudget set Period8=0 where CHARINDEX('-',period8)>0
update newbudget set Period9=0 where CHARINDEX('-',period9)>0
update newbudget set Period10=0 where CHARINDEX('-',period10)>0
update newbudget set Period11=0 where CHARINDEX('-',period11)>0
update newbudget set Period12=0 where CHARINDEX('-',period12)>0
update newbudget set Period1 = REPLACE(Period1,',','')
update newbudget set Period1 = REPLACE(Period1,'(','-')
update newbudget set Period1 = REPLACE(Period1,')','')
update newbudget set period2 = REPLACE(period2,',','')
update newbudget set period2 = REPLACE(period2,'(','-')
update newbudget set period2 = REPLACE(period2,')','')
update newbudget set period3 = REPLACE(period3,',','')
update newbudget set period3 = REPLACE(period3,'(','-')
update newbudget set period3 = REPLACE(period3,')','')
update newbudget set period4 = REPLACE(period4,',','')
update newbudget set period4 = REPLACE(period4,'(','-')
update newbudget set period4 = REPLACE(period4,')','')
update newbudget set period5 = REPLACE(period5,',','')
update newbudget set period5 = REPLACE(period5,'(','-')
update newbudget set period5 = REPLACE(period5,')','')
update newbudget set period6 = REPLACE(period6,',','')
update newbudget set period6 = REPLACE(period6,'(','-')
update newbudget set period6 = REPLACE(period6,')','')
update newbudget set period7 = REPLACE(period7,',','')
update newbudget set period7 = REPLACE(period7,'(','-')
update newbudget set period7 = REPLACE(period7,')','')
update newbudget set period8 = REPLACE(period8,',','')
update newbudget set period8 = REPLACE(period8,'(','-')
update newbudget set period8 = REPLACE(period8,')','')
update newbudget set period9 = REPLACE(period9,',','')
update newbudget set period9 = REPLACE(period9,'(','-')
update newbudget set period9 = REPLACE(period9,')','')
update newbudget set period10 = REPLACE(period10,',','')
update newbudget set period10 = REPLACE(period10,'(','-')
update newbudget set period10 = REPLACE(period10,')','')
update newbudget set period11 = REPLACE(period11,',','')
update newbudget set period11 = REPLACE(period11,'(','-')
update newbudget set period11 = REPLACE(period11,')','')
update newbudget set period12 = REPLACE(period12,',','')
update newbudget set period12 = REPLACE(period12,'(','-')
update newbudget set period12 = REPLACE(period12,')','')
-----------STEP 2 --POPULATE-NEW BUDGET-----------------
insert into newbudget2
select account_no, 1 as period, period1
from newbudget
insert into newbudget2
select account_no, 2 as period, period2 as amount
from newbudget
--next
insert into newbudget2
select account_no, 3 as period, period3 as amount
from newbudget
insert into newbudget2
select account_no, 4 as period, period4 as amount
from newbudget
insert into newbudget2
select account_no, 5 as period, period5 as amount
from newbudget
insert into newbudget2
select account_no, 6 as period, period6 as amount
from newbudget
insert into newbudget2
select account_no, 7 as period, period7 as amount
from newbudget
insert into newbudget2
select account_no, 8 as period, period8 as amount
from newbudget
insert into newbudget2
select account_no, 9 as period, period9 as amount
from newbudget
insert into newbudget2
select account_no, 10 as period, period10 as amount
from newbudget
insert into newbudget2
select account_no, 11 as period, period11 as amount
from newbudget
insert into newbudget2
select account_no, 12 as period, period12 as amount
from newbudget
--check totals
select account_no, sum(amount) from newbudget2 group by account_no order by account_no
---CREATE CUMULATIVE BALANCE
select a.* from balances as a
inner join
(SELECT top 100 percent a.account_no, a.period, a.amount,
(select sum(b.amount) from newbudget2 as b where b.period<=a.period and b.account_no=a.account_no) as cumulativebudget
from newbudget2 as a
order by a.account_no, a.period) as b
on a.account_no=b.account_no and a.period=b.period
and a.company_no='CPC' and a.year_for_period=2017
select a.* from balances as a
where a.company_no='CPC' and a.year_for_period=2016
--------ARE ALL BALANCES ALREADY IN BALANCES TABLE?
--21792 records not in existing balances table
--828 records in balances table already
select top 100 percent a.* from
(SELECT top 100 percent a.account_no, a.period, a.amount,
(select sum(b.amount) from newbudget2 as b where b.period<=a.period and b.account_no=a.account_no) as cumulativebudget
from newbudget2 as a
order by a.account_no, a.period) as a
left join
(select a.* from balances as a
where a.company_no='CPC' and a.year_for_period=2017) as b
on a.account_no=b.account_no and a.period=b.period
where b.account_no is not null
order by a.account_no, a.period
------------UPDATE BUDGET WHEN RECORD FOR GL ACCOUNT ALREADY EXISTS IN BALANCES TABLE----------------
--828 rows
update balances set budget_1=a.newbudget, cumulative_budget_1=a.cumulativebudget, date_budget_changed=GETDATE(), date_last_modified=GETDATE() from balances
inner join
(select a.company_no, a.account_no,a.period, a.year_for_period, b.newbudget, b.cumulativebudget from balances as a
inner join
(SELECT top 100 percent a.account_no, a.period, a.amount as newbudget,
(select sum(b.amount) from newbudget2 as b where b.period<=a.period and b.account_no=a.account_no) as cumulativebudget
from newbudget2 as a
order by a.account_no, a.period) as b
on a.account_no=b.account_no and a.period=b.period
and a.company_no='CPC' and a.year_for_period=2017) as a
on balances.company_no=a.company_no and balances.year_for_period=a.year_for_period and balances.period=a.period and balances.account_no=a.account_no
------------------INSERT NEW RECORDS ------------------------------------
--21792
insert into balances (company_no,delete_flag,currency_id, cumulative_balance,period_balance, budget_2,budget_3, account_no,period, year_for_period, budget_1, date_created, date_last_modified, last_maintained_by, date_budget_changed,cumulative_budget_1)
select top 100 percent 'CPC','N',1,0,0,0,0,a.account_no, a.period, 2017, a.amount, GETDATE(),GETDATE(),'djohnson',GETDATE(),isnull(a.cumulativebudget,0) from
(SELECT top 100 percent a.account_no, a.period, a.amount,
(select sum(b.amount) from newbudget2 as b where b.period<=a.period and b.account_no=a.account_no) as cumulativebudget
from newbudget2 as a
order by a.account_no, a.period) as a
left join
(select a.* from balances as a
where a.company_no='XXX' and a.year_for_period=2017) as b
on a.account_no=b.account_no and a.period=b.period
where b.account_no is null
order by a.account_no, a.period
select distinct currency_id from balances
------------------TIE OUT FINAL BALANCE ----------------------------------------
select sum(budget_1)
from balances as a
inner join
newbudget2 as b
on a.account_no=b.account_no
and a.period=b.period
and a.year_for_period=2017 and a.company_no='CPC'
select sum(budget_1) from balances where year_for_period=2017 and period=11
select sum(amount) from newbudget2 where period=11
select * from balances where last_maintained_by='djohnson'