VBA ADODB to SQL problem

S

Shady42

New Member
Joined
Jun 11, 2009
Messages
11
Hoping someone can help me unravel this one...

I am trying to execute an SQL stored procedure from VBA and have got the script to work but only if the procedure i call does not contain any insert, update or delete statements. If i use any of those statements then i get the error below at the 'rec.Open cmd.Execute' line of code. If i butcher my code to remove all of these commands it runs.

Run-time error '3001':

Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another


The SQL statement updates a table with a snapshot of balances from multiple databases then returns a simple select statement on that table

I can run the procedure perfectly well from within SQL and i can select the resulting data manually from vba after i run it but i cannot seem to get vba to correctly execute the procedure.

Apologies for the large code dump and can anyone give me a glimmer of hope?

Many Thanks
Shady


VBA Code :
Sub Refresh_TB_Data()

Dim con As ADODB.Connection
Dim rec As ADODB.Recordset
Dim prm As ADODB.Parameter
Dim cmd As ADODB.Command
Dim Constr As String 'SQL Connection string
Dim Proc As String 'Stored Procedure name
Dim fldCount As Integer

'Declare variables for Stored Procedure
Dim whereclause As Variant



'Set ADODB requirements
Set con = New ADODB.Connection
Set rec = New ADODB.Recordset
Set cmd = New ADODB.Command

'Define database connection string
Constr = "Provider=SQLOLEDB.1;"
Constr = Constr + "Password=xxxxxxxxx;"
Constr = Constr + "Persist Security Info=True;"
Constr = Constr + "User ID=xxxxxxxxx;"
Constr = Constr + "Initial Catalog=eBOSenergyGB;Data Source=senabzepi001;"
Constr = Constr + "Use Procedure for Prepare=1;"
Constr = Constr + "Auto Translate=True;"
Constr = Constr + "Packet Size=4096;"
Constr = Constr + "Use Encryption for Data=False;"
Constr = Constr + "Tag with column collation when possible=False"

'Open database connection
con.ConnectionString = Constr
con.Open

whereclause = "'where 1=1'"

' Defines the stored procedure commands
Proc = "ss_refresh_tb_sp" 'Define name of Stored Procedure to execute.
cmd.CommandType = adCmdProcedure 'Define the ADODB command
cmd.ActiveConnection = con 'Set the command connection string
cmd.CommandText = Proc 'Define Stored Procedure to run


'set parameters to be executed
whereclause = ""
Set prm = cmd.CreateParameter("@whereclause", adVarChar, adParamInput, 1024, whereclause)
'Append parameters
With cmd
.Parameters.Append prm
End With

'Execute stored procedure and return to a recordset
rec.Open cmd.Execute

'insert field headings into sheet
fldCount = rst.Fields.Count
For iCol = 1 To fldCount
Sheets("sheet1").Cells(1, iCol).Value = rst.Fields(iCol - 1).Name
Next

'insert record set to sheet
Sheets("sheet1").Cells(2, 1).CopyFromRecordset rst

'Close database connection and clean up
If CBool(rec.State And adStateOpen) = True Then rec.Close
Set rec = Nothing
If CBool(con.State And adStateOpen) = True Then con.Close
Set con = Nothing

End Sub

SQL Code :
createPROC [dbo].[ss_refresh_tb_sp] @WhereClause varchar(1024)AS



declare
@date_period_end int,
@company_name varchar(40),
@db_name varchar(40)

/**** Whereclause simulation ****/
--,@whereclause varchar(1024)
--select @whereclause = "where date_period_end=734533"

ifcharindex('date_period_end',@whereclause)<>0
begin
select @date_period_end =min(period_end_date)from glprd where period_end_date >=cast(substring(@whereclause,(charindex('date_period_end',@whereclause))+16,6)asint)
end
else
select @date_period_end =max(period_end_date)from glco

createtable #bal (
company_name varchar(40),
account_code varchar(20),
date_period_end int,
period_movement float,
period_end_balance float
)
createtable #per (
account_code varchar(20),
period_movement float
)
createtable #end (
account_code varchar(20),
period_end_balance float
)


SELECT @db_name =min(db_name)
FROM eBOSenergyCTRL..ewcomp


WHILE(@db_name !=''
ANDEXISTS(SELECT 1 from eBOSenergyCTRL..ewcomp c INNERJOIN eBOSenergyCTRL..sminst i ON c.company_id = i.company_id
WHERE app_id = 6000 AND c.db_name= @db_name ))

BEGIN

SELECT @company_name =description
FROM eBOSenergyCTRL..ewcomp
WHEREdb_name= @db_name


exec("insert into #bal select '"+@company_name+"',account_code,"+@date_period_end+",0,0 from "+@db_name+"..glchart")

deletefrom #per
deletefrom #end

exec("insert into #end
select account_code, period_end_balance=sum(current_balance_oper)
from "+@db_name+"..glbal
where balance_date <="+@date_period_end+" and balance_until >="+@date_period_end +"
group by account_code")

exec("insert into #per
select account_code, period_movement=sum(net_change_oper)
from "+@db_name+"..glbal t1
join "+@db_name+"..glprd t2 on t1.balance_date<=t2.period_end_date and t1.balance_date>=t2.period_start_date
where t2.period_end_date = "+@date_period_end+"
group by account_code")

update #bal set #bal.period_movement = #per.period_movement from #per where #bal.account_code = #per.account_code and #bal.company_name = @company_name

update #bal set #bal.period_end_balance = #end.period_end_balance from #end where #bal.account_code = #end.account_code and #bal.company_name = @company_name

SELECT @db_name =min(db_name)
FROM eBOSenergyCTRL..ewcomp
WHEREdb_name> @db_name

END


deletefrom ss_glbal_mcomp

insert ss_glbal_mcomp
select
company_name,
account_code,
seg1=left(account_code,4),
seg2=substring(account_code,5,3),
seg3=substring(account_code,8,3),
seg4=right(account_code,5),
nominal=cast(right(account_code,5)asint),
division_num=left(account_code,1),
region_num=substring(account_code,2,1),
country_num=substring(account_code,3,2),
t2.division_name,
t3.region_name,
t4.country_name,
t5.description,
t6.description,
t7.description,
t10.type,
t10.description,
t1.date_period_end-693594,
period_opening_balance=t1.period_end_balance-t1.period_movement,
t1.period_movement,
t1.period_end_balance
from #bal t1
leftjoin ss_seg1_division t2 onleft(t1.account_code,1)= t2.division_code
leftjoin ss_seg1_region t3 onsubstring(t1.account_code,2,1)= t3.region_code
leftjoin ss_seg1_country t4 onsubstring(t1.account_code,3,2)= t4.country_code
join glseg2 t5 onsubstring(t1.account_code,5,3)= t5.seg_code
join glseg3 t6 onsubstring(t1.account_code,8,3)= t6.seg_code
join glseg4 t7 onright(t1.account_code,5)= t7.seg_code
leftjoin(select t8.nominal,t8.type,t9.description from ss_nominal_class t8 join ss_nominal_class_desc t9 on t8.type = t9.type) t10 onright(t1.account_code,5)= t10.nominal



select*from ss_glbal_mcomp


droptable #per
droptable #end
droptable #bal
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
Sort by date Sort by votes
Old post and maybe not much help.
I had the same problem, tried for days to resolve it, and found out that it was the temp table or table variable of the SP that was causing the error. Converted the SP to a function that returns a table and the VBA worked on the first try.
 
Upvote 0
rather strange timing as I just solved this one myself only in the last two weeks with two changes to my code.

Add "set nocount on" to the top of the SQL proc - this suppresses the messages generated by SQL like "5 row(s) affected" which is what was causing no rows to be returned as it was trying to return the first message generated by SQL

I also found that a more consistant VBA coding for retrieving the recordset was the syntax "set rec = cmd.execute" instead of "rec.open cmd.execute"

This removes the need for dumping to a permanent table - see adjusted code below

One final piece of useful info i found was that dumping the recordset to a table in excel allows a spreadsheet to reference the updated information without any complicated adjustment of references and no need to set the column headers every time either


Hope that helps with your own problem

Shady

VBA Code :
Code: 
Sub Refresh_TB_Data()

Dim con As ADODB.Connection
Dim rec As ADODB.Recordset
Dim prm As ADODB.Parameter
Dim cmd As ADODB.Command
Dim Constr As String 'SQL Connection string
Dim Proc As String 'Stored Procedure name
Dim fldCount As Integer

'Declare variables for Stored Procedure
Dim whereclause As Variant



'Set ADODB requirements
Set con = New ADODB.Connection
Set rec = New ADODB.Recordset
Set cmd = New ADODB.Command

'Define database connection string
Constr = "Provider=SQLOLEDB.1;"
Constr = Constr + "Password=xxxxxxxxx;"
Constr = Constr + "Persist Security Info=True;"
Constr = Constr + "User ID=xxxxxxxxx;"
Constr = Constr + "Initial Catalog=eBOSenergyGB;Data Source=senabzepi001;"
Constr = Constr + "Use Procedure for Prepare=1;"
Constr = Constr + "Auto Translate=True;"
Constr = Constr + "Packet Size=4096;"
Constr = Constr + "Use Encryption for Data=False;"
Constr = Constr + "Tag with column collation when possible=False"

'Open database connection
con.ConnectionString = Constr
con.Open

whereclause = "'where 1=1'"

' Defines the stored procedure commands
Proc = "ss_refresh_tb_sp" 'Define name of Stored Procedure to execute.
cmd.CommandType = adCmdProcedure 'Define the ADODB command
cmd.ActiveConnection = con 'Set the command connection string
cmd.CommandText = Proc 'Define Stored Procedure to run


'set parameters to be executed
whereclause = ""
Set prm = cmd.CreateParameter("@whereclause", adVarChar, adParamInput, 1024, whereclause)
'Append parameters
With cmd
.Parameters.Append prm
End With

'Execute stored procedure and return to a recordset
'rec.Open cmd.Execute
set rec = cmd.execute


'insert record set to sheet
Sheets("sheet1").Range("Table1").CopyFromRecordset rec

'Close database connection and clean up
If CBool(rec.State And adStateOpen) = True Then rec.Close
Set rec = Nothing
If CBool(con.State And adStateOpen) = True Then con.Close
Set con = Nothing

End Sub

SQL Code :
Code: 
[SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]create[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]PROC[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] [dbo][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][ss_refresh_tb_sp] @WhereClause [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]varchar[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]([/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]1024[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080])[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]AS[/COLOR][/SIZE]

set nocount on

[SIZE=2][COLOR=#0000ff]declare[/COLOR][/SIZE][/COLOR][/SIZE]
[SIZE=2]@date_period_end [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]int[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080],[/COLOR][/SIZE]
[/COLOR][/SIZE][SIZE=2]@company_name [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]varchar[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]([/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]40[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]),[/COLOR][/SIZE]
[/COLOR][/SIZE][SIZE=2]@db_name [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]varchar[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]([/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]40[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080])[/COLOR][/SIZE]

[/COLOR][/SIZE][SIZE=2][COLOR=#008000][SIZE=2][COLOR=#008000]/**** Whereclause simulation ****/[/COLOR][/SIZE]
[SIZE=2][COLOR=#008000]--,@whereclause varchar(1024)[/COLOR][/SIZE]
[SIZE=2][COLOR=#008000]--select @whereclause = "where date_period_end=734533"[/COLOR][/SIZE]

[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]if[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#ff00ff][SIZE=2][COLOR=#ff00ff]charindex[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]([/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#ff0000][SIZE=2][COLOR=#ff0000]'date_period_end'[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080],[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]@whereclause[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080])[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]<>[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]0 [/SIZE]
[SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]begin[/COLOR][/SIZE][/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]select[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] @date_period_end [/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]=[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#ff00ff][SIZE=2][COLOR=#ff00ff]min[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]([/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]period_end_date[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080])[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]from[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] glprd [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]where[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] period_end_date [/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]>=[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#ff00ff][SIZE=2][COLOR=#ff00ff]cast[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]([/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#ff00ff][SIZE=2][COLOR=#ff00ff]substring[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]([/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]@whereclause[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080],([/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#ff00ff][SIZE=2][COLOR=#ff00ff]charindex[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]([/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#ff0000][SIZE=2][COLOR=#ff0000]'date_period_end'[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080],[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]@whereclause[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]))+[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]16[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080],[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]6[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080])[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]as[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]int[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080])[/COLOR][/SIZE]
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]end[/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff]else[/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff]select[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] @date_period_end [/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]=[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#ff00ff][SIZE=2][COLOR=#ff00ff]max[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]([/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]period_end_date[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080])[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]from[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] glco[/SIZE]

[SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]create[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]table[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] #bal [/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]([/COLOR][/SIZE]
[/COLOR][/SIZE][SIZE=2]company_name [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]varchar[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]([/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]40[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]),[/COLOR][/SIZE]
[/COLOR][/SIZE][SIZE=2]account_code [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]varchar[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]([/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]20[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]),[/COLOR][/SIZE]
[/COLOR][/SIZE][SIZE=2]date_period_end [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]int[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080],[/COLOR][/SIZE]
[/COLOR][/SIZE][SIZE=2]period_movement [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]float[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080],[/COLOR][/SIZE]
[/COLOR][/SIZE][SIZE=2]period_end_balance [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]float[/COLOR][/SIZE]
[/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080])[/COLOR][/SIZE]
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]create[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]table[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] #per [/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]([/COLOR][/SIZE]
[/COLOR][/SIZE][SIZE=2]account_code [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]varchar[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]([/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]20[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]),[/COLOR][/SIZE]
[/COLOR][/SIZE][SIZE=2]period_movement [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]float[/COLOR][/SIZE]
[/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080])[/COLOR][/SIZE]
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]create[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]table[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] #end [/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]([/COLOR][/SIZE]
[/COLOR][/SIZE][SIZE=2]account_code [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]varchar[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]([/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]20[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]),[/COLOR][/SIZE]
[/COLOR][/SIZE][SIZE=2]period_end_balance [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]float[/COLOR][/SIZE]
[/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080])[/COLOR][/SIZE]


[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]SELECT[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] @db_name [/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]=[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#ff00ff][SIZE=2][COLOR=#ff00ff]min[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]([/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#ff00ff][SIZE=2][COLOR=#ff00ff]db_name[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080])[/COLOR][/SIZE][/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]FROM[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] eBOSenergyCTRL[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]..[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]ewcomp[/SIZE]


[SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]WHILE[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]([/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]@db_name [/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]!=[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#ff0000][SIZE=2][COLOR=#ff0000]''[/COLOR][/SIZE][/COLOR][/SIZE]
[SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]AND[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]EXISTS([/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]SELECT[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] 1 [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]from[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] eBOSenergyCTRL[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]..[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]ewcomp c [/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]INNER[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]JOIN[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] eBOSenergyCTRL[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]..[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]sminst i [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]ON[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] c[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]company_id [/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]=[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] i[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]company_id [/SIZE]
[SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]WHERE[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] app_id [/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]=[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] 6000 [/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]AND[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] c[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#ff00ff][SIZE=2][COLOR=#ff00ff]db_name[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]=[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] @db_name [/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]))[/COLOR][/SIZE][/COLOR][/SIZE]
[SIZE=2][COLOR=#808080]
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]BEGIN[/COLOR][/SIZE]

[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]SELECT[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] @company_name [/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]=[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]description[/COLOR][/SIZE]
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]FROM[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] eBOSenergyCTRL[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]..[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]ewcomp[/SIZE]
[SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]WHERE[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#ff00ff][SIZE=2][COLOR=#ff00ff]db_name[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]=[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] @db_name[/SIZE]


[SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]exec[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]([/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]"insert into #bal select '"[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]+[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]@company_name[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]+[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]"',account_code,"[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]+[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]@date_period_end[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]+[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]",0,0 from "[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]+[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]@db_name[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]+[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]"..glchart"[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080])[/COLOR][/SIZE][/COLOR][/SIZE]
[SIZE=2][COLOR=#808080]
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]delete[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]from[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] #per[/SIZE]
[SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]delete[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]from[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] #end[/SIZE]

[SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]exec[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]([/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]"insert into #end [/SIZE]
[SIZE=2]select account_code, period_end_balance=sum(current_balance_oper) [/SIZE]
[SIZE=2]from "[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]+[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]@db_name[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]+[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]"..glbal [/SIZE]
[SIZE=2]where balance_date <="[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]+[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]@date_period_end[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]+[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]" and balance_until >="[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]+[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]@date_period_end [/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]+[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]"[/SIZE]
[SIZE=2]group by account_code"[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080])[/COLOR][/SIZE][/COLOR][/SIZE]
[SIZE=2][COLOR=#808080]
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]exec[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]([/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]"insert into #per[/SIZE]
[SIZE=2]select account_code, period_movement=sum(net_change_oper) [/SIZE]
[SIZE=2]from "[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]+[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]@db_name[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]+[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]"..glbal t1 [/SIZE]
[SIZE=2]join "[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]+[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]@db_name[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]+[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]"..glprd t2 on t1.balance_date<=t2.period_end_date and t1.balance_date>=t2.period_start_date [/SIZE]
[SIZE=2]where t2.period_end_date = "[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]+[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]@date_period_end[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]+[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]" [/SIZE]
[SIZE=2]group by account_code"[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080])[/COLOR][/SIZE][/COLOR][/SIZE]
[SIZE=2][COLOR=#808080]
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]update[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] #bal [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]set[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] #bal[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]period_movement [/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]=[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] #per[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]period_movement [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]from[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] #per [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]where[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] #bal[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]account_code [/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]=[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] #per[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]account_code [/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]and[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] #bal[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]company_name [/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]=[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] @company_name[/SIZE]

[SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]update[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] #bal [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]set[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] #bal[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]period_end_balance [/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]=[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] #end[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]period_end_balance [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]from[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] #end [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]where[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] #bal[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]account_code [/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]=[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] #end[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]account_code [/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]and[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] #bal[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]company_name [/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]=[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] @company_name[/SIZE]

[SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]SELECT[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] @db_name [/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]=[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#ff00ff][SIZE=2][COLOR=#ff00ff]min[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]([/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#ff00ff][SIZE=2][COLOR=#ff00ff]db_name[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080])[/COLOR][/SIZE][/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]FROM[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] eBOSenergyCTRL[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]..[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]ewcomp[/SIZE]
[SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]WHERE[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#ff00ff][SIZE=2][COLOR=#ff00ff]db_name[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]>[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] @db_name [/SIZE]

[SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]END[/COLOR][/SIZE]


[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]select[/COLOR][/SIZE][/COLOR][/SIZE]
[SIZE=2]company_name[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080],[/COLOR][/SIZE]
[/COLOR][/SIZE][SIZE=2]account_code[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080],[/COLOR][/SIZE]
[/COLOR][/SIZE][SIZE=2]seg1[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]=left([/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]account_code[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080],[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]4[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]),[/COLOR][/SIZE]
[/COLOR][/SIZE][SIZE=2]seg2[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]=[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#ff00ff][SIZE=2][COLOR=#ff00ff]substring[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]([/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]account_code[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080],[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]5[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080],[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]3[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]),[/COLOR][/SIZE]
[/COLOR][/SIZE][SIZE=2]seg3[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]=[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#ff00ff][SIZE=2][COLOR=#ff00ff]substring[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]([/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]account_code[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080],[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]8[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080],[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]3[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]),[/COLOR][/SIZE]
[/COLOR][/SIZE][SIZE=2]seg4[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]=right([/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]account_code[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080],[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]5[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]),[/COLOR][/SIZE]
[/COLOR][/SIZE][SIZE=2]nominal[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]=[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#ff00ff][SIZE=2][COLOR=#ff00ff]cast[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080](right([/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]account_code[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080],[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]5[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080])[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]as[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]int[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]),[/COLOR][/SIZE]
[/COLOR][/SIZE][SIZE=2]division_num[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]=left([/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]account_code[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080],[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]1[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]),[/COLOR][/SIZE]
[/COLOR][/SIZE][SIZE=2]region_num[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]=[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#ff00ff][SIZE=2][COLOR=#ff00ff]substring[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]([/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]account_code[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080],[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]2[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080],[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]1[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]),[/COLOR][/SIZE]
[/COLOR][/SIZE][SIZE=2]country_num[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]=[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#ff00ff][SIZE=2][COLOR=#ff00ff]substring[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]([/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]account_code[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080],[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]3[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080],[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]2[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]),[/COLOR][/SIZE]
[/COLOR][/SIZE][SIZE=2]t2[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]division_name[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080],[/COLOR][/SIZE]
[/COLOR][/SIZE][SIZE=2]t3[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]region_name[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080],[/COLOR][/SIZE]
[/COLOR][/SIZE][SIZE=2]t4[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]country_name[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080],[/COLOR][/SIZE]
[/COLOR][/SIZE][SIZE=2]t5[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]description[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080],[/COLOR][/SIZE]
[/COLOR][/SIZE][SIZE=2]t6[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]description[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080],[/COLOR][/SIZE]
[/COLOR][/SIZE][SIZE=2]t7[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]description[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080],[/COLOR][/SIZE]
[/COLOR][/SIZE][SIZE=2]t10[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]type[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080],[/COLOR][/SIZE]
[/COLOR][/SIZE][SIZE=2]t10[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]description[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080],[/COLOR][/SIZE]
[/COLOR][/SIZE][SIZE=2]t1[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]date_period_end[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]-[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]693594[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080],[/COLOR][/SIZE]
[/COLOR][/SIZE][SIZE=2]period_opening_balance[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]=[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]t1[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]period_end_balance[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]-[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]t1[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]period_movement[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080],[/COLOR][/SIZE]
[/COLOR][/SIZE][SIZE=2]t1[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]period_movement[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080],[/COLOR][/SIZE]
[/COLOR][/SIZE][SIZE=2]t1[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]period_end_balance[/SIZE]
[SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]from[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] #bal t1[/SIZE]
[SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]left[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]join[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] ss_seg1_division t2 [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]on[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]left([/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]t1[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]account_code[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080],[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]1[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080])[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]=[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] t2[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]division_code[/SIZE]
[SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]left[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]join[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] ss_seg1_region t3 [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]on[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#ff00ff][SIZE=2][COLOR=#ff00ff]substring[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]([/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]t1[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]account_code[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080],[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]2[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080],[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]1[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080])[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]=[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] t3[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]region_code[/SIZE]
[SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]left[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]join[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] ss_seg1_country t4 [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]on[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#ff00ff][SIZE=2][COLOR=#ff00ff]substring[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]([/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]t1[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]account_code[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080],[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]3[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080],[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]2[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080])[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]=[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] t4[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]country_code[/SIZE]
[SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]join[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] glseg2 t5 [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]on[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#ff00ff][SIZE=2][COLOR=#ff00ff]substring[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]([/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]t1[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]account_code[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080],[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]5[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080],[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]3[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080])[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]=[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] t5[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]seg_code[/SIZE]
[SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]join[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] glseg3 t6 [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]on[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#ff00ff][SIZE=2][COLOR=#ff00ff]substring[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]([/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]t1[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]account_code[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080],[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]8[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080],[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]3[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080])[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]=[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] t6[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]seg_code[/SIZE]
[SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]join[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] glseg4 t7 [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]on[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]right([/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]t1[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]account_code[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080],[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]5[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080])[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]=[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] t7[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]seg_code[/SIZE]
[SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]left[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]join[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]([/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]select[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] t8[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]nominal[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080],[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]t8[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]type[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080],[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]t9[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]description [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]from[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] ss_nominal_class t8 [/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]join[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] ss_nominal_class_desc t9 [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]on[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] t8[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]type [/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]=[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] t9[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]type[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080])[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] t10 [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]on[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]right([/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]t1[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]account_code[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080],[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]5[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080])[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]=[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] t10[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]nominal[/SIZE]


[SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]drop[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]table[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] #per[/SIZE]
[SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]drop[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]table[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] #end[/SIZE]
[SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]drop[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]table[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] #bal[/SIZE]
 
Last edited:
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,223,243
Messages
6,170,964
Members
452,371
Latest member
Frana

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top