Microsoft Great Plains: Interest Calculation Example – stored procedure for Crystal Report


Microsoft Great Plains: Interest Calculation Example – stored procedure for Crystal Report

 by: Andrew Karasev

This is intermediate level SQL scripting article for DB Administrator, Programmer, IT Specialist

Our and Microsoft Business Solutions goal here is to educate database administrator, programmer, software developer to enable them support Microsoft Great Plains for their companies. In our opinion self support is the goal of Microsoft to facilitate implementation of its products: Great Plains, Navision, Solomon, Microsoft CRM. You can do it for your company, appealing to Microsoft Business Solutions Techknowledge database. This will allow you to avoid expensive consultant visits onsite. You only need the help from professional when you plan on complex customization, interface or integration, then you can appeal to somebody who specializes in these tasks and can do inexpensive nation-wide remote support for you.

Let's look at interest calculation techniques.

Imagine that you are financing institution and have multiple customers in two companies, where you need to predict interest. The following procedure will do the job:

CREATE PROCEDURE ASTInterestCalculation

@Company1 varchar(10), Great Plains SQL database ID

@Company2 varchar(10),

@Accountfrom varchar(60),

@Accountto varchar(60),

@Datefrom datetime,

@Dateto datetime,

as

declare @char39 char for single quote mark

declare @SDatefrom as varchar(50)

declare @SDateto as varchar(50)

select @SDatefrom cast(@Datefrom as varchar(50))

select @SDateto cast(@Dateto as varchar(50))

select @char39char(39)

if not exists (select from dbo.sysobjects where id objectid(N'[dbo].[ASTINTERESTTABLE]') and OBJECTPROPERTY(id, N'IsUserTable') 1)

CREATE TABLE [dbo].[ASTINTERESTTABLE] (

[YEAR] [int] NULL ,

[MONTH] [int] NULL ,

[COMPANYID] [varchar] (4) COLLATE SQLLatin1GeneralCP1CIAS NOT NULL ,

[ACTNUMST] [char] (129) COLLATE SQLLatin1GeneralCP1CIAS NOT NULL ,

[BEGINDATE] [varchar] (19) COLLATE SQLLatin1GeneralCP1CIAS NOT NULL ,

[ENDDATE] [varchar] (19) COLLATE SQLLatin1GeneralCP1CIAS NOT NULL ,

[YEARDEGBALANCE] [numeric](19, 5) NULL ,

[BEGBALANCE] [numeric](38, 5) NULL ,

[ENDBALANCE] [numeric](38, 5) NULL ,

[INTERESTONBALANCE] [numeric](38, 6) NULL ,

[INTERESONTRANSACTIONS] [numeric](38, 8) NULL ,

[INTEREST] [numeric](38, 6) NULL

) ON [PRIMARY]

exec("

delete ASTINTERESTTABLE where [YEAR] year("+ @char39 + @Datefrom + @char39 +") and [MONTH]month("+ @char39 + @Datefrom + @char39 +")

insert into ASTINTERESTTABLE

select

year(X.BEGINDATE) as [YEAR],

month(X.BEGINDATE) as [MONTH],

X.COMPANYID,

X.ACTNUMST,

X.BEGINDATE as BEGINDATE,

X.ENDDATE as ENDDATE,

X.YEARBEGBALANCE as YEARDEGBALANCE,

X.YEARBEGBALANCE+X.BEGBALANCE as BEGBALANCE,

X.YEARBEGBALANCE+X.ENDBALANCE as ENDBALANCE,

X.INTERESTONBALANCE as INTERESTONBALANCE,

X.INTERESTONTRANSACTIONS as INTERESONTRANSACTIONS,

X.INTERESTONBALANCE+X.INTERESTONTRANSACTIONS as INTEREST

into ASTINTERESTTABLE

from

(

select

"+ @char39+ @Company1 + @char39+" as COMPANYID,

a.ACTNUMST,

"+ @char39 + @Datefrom + @char39 +" as BEGINDATE,

"+ @char39 + @Dateto + @char39 +" as ENDDATE,

case when

b.PERDBLNC is null then 0

else b.PERDBLNC

end as YEARBEGBALANCE,

sum

(

case

when (c.DEBITAMT-c.CRDTAMNT is not null and c.TRXDATE "+ @char39 + @SDatefrom + @char39 +" and c.TRXDATE year("+ @char39 + @Datefrom + @char39 +")

where

a.ACTNUMST>"+@char39+@Accountfrom+@char39 +"

and a.ACTNUMST"+ @char39 + @SDatefrom + @char39 +" and c.TRXDATE year("+ @char39 + @Datefrom + @char39 +")

where

a.ACTNUMST>"+@char39+@Accountfrom+@char39 +"

and a.ACTNUMST<"+@char39+@Accountto+@char39 +"

group by

a.ACTNUMST,

case when

b.PERDBLNC is null then 0

else b.PERDBLNC

end,

d.USERDEF1

) X

")

Happy querying and customizing! if you want us to help you - give us a call 1-866-528-0577! help@albaspectrum.com