Microsoft Great Plains: Interest Calculation Example ? Stored Procedure for Crystal Report

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 AST_Interest_Calculation

@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 @char39=char(39)

if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[AST_INTEREST_TABLE]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

CREATE TABLE [dbo].[AST_INTEREST_TABLE] (

[YEAR] [int] NULL ,

[MONTH] [int] NULL ,

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

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

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

[ENDDATE] [varchar] (19) COLLATE SQL_Latin1_General_CP1_CI_AS 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 AST_INTEREST_TABLE where [YEAR] = year("+ @char39 + @Datefrom + @char39 +") and [MONTH]=month("+ @char39 + @Datefrom + @char39 +")

insert into AST_INTEREST_TABLE

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 AST_INTEREST_TABLE

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

---

International Support - Microsoft Great Plains VAR/Partner Selection ? Overview for V.P. IT

Looks like Microsoft Great Plains becomes more and more popular,... Read More

Navision Sales Module & Reporting: Jet Reports, C/ODBC, XBRL, Business Analytics ? highlights

Microsoft bought Navision, Denmark based software development company, along with... Read More

Things You Can Do With Windows XP!

Did you ever give a thought to the number of... Read More

Bill of Lading ? Custom Reporting for Microsoft Great Plains ? Overview for Consultant

Bill of Lading is required report for Logistics and Freight... Read More

Should I Use Windows Update?

Should one use Windows Update?This topic has good and valid... Read More

Passwords Used In Microsoft Word Documents

You would like to protect your documents, wouldn't you? Reasons... Read More

Cisco Certification: Introduction To ISDN, Part V

The major reason I recommend getting your hands on real... Read More

Microsoft CRM Implementation & Remote Support

We would like to give you pluses and minuses of... Read More

Microsoft Office Selecting Secrets

Stop the Runaway MouseWhat's the "runaway mouse?" You've seen it...you... Read More

Microsoft CRM Customization ? Programming Closed Email Activity

Microsoft CRM is CRM answer from Microsoft and attempt to... Read More

Crystal Reports - Microsoft SQL Server

Microsoft SQL Server is the leader for inexpensive and middle... Read More

Create A Flash Presentation For Free With Open Office

The intentions of this short tutorial are not to teach... Read More

Corporate ERP: Microsoft Great Plains For The Plant In Brazil ? Overview For CIO

Microsoft Business Solutions Great Plains as new ERP for multinational... Read More

Free Preventive Maintenance Software

While several preventive maintenance software manufacturers offer free trials for... Read More

.NET :Solving the Multiple Inheritance Issue Under .NET Platform

.NET platform does not support multiple inheritance. Do not confuse... Read More

Running a Program on a Remote Server Using SSH

How do you run a program on a remote server... Read More

Microsoft CRM USA Nationwide Remote Support

Remember old good days when your company probably had Great... Read More

When is a Software Engineer Not a Software Engineer?

The title of "software engineer" has got to be among... Read More

The Hidden Power of Online Manual

Writing software manuals is boring, isn't it? We often think:... Read More

Preventive Maintenance Software Companies

Several software companies design programs for preventive maintenance. Most of... Read More

What To Do When Windows Wont Boot

When Windows fails to boot it is normally caused by... Read More

Making The Decision To Automate Your Software Testing

Not every software testing project can or should be automated.... Read More

Ukraine: a Prospective Player on the World Software Outsourcing Market

The destruction of the Soviet Union about 15 years ago,... Read More

Microsoft Great Plains Accounting/ERP Implementation ? Finance Industry Customization Example

Microsoft Business Solutions Great Plains is very generic accounting application... Read More

Crystal Reports - Geterogeneus databases: SQL Server, Oracle, Unidata, Btrieve ? Tips For Developer

Crystal Reports is the most flexible tool on the market... Read More

Is Your Computer Sick?

Viruses and spyware usually show up on your computer one... Read More

Microsoft Business Solutions Products Selection: ERP, CRM, Retail Management

Let's first look at your ERP system selection (without Retail... Read More

Microsoft Great Plains Integration Manager ? Working With Text File

Microsoft Business Solutions main middle market ERP application - Microsoft... Read More

Microsoft Great Plains 8.0 Brazilian Version ? Overview For International Consultant

Microsoft Great Plains has substantial mid-market share in the USA... Read More

Microsoft Great Plains Oil & Gas ? Implementation & Customization Highlights

Microsoft Great Plains serves the wide spectrum of horizontal markets.... Read More

Introducing GRML

Creating a new markup language.Introduction.General Reuse Markup Langauge, or GRML,... Read More

20 Extra Hours Per Week: What Would You Do?

While I was in college, if you would have asked... Read More

FreeDOS

Before September 1995, Microsoft Windows was an MS-DOS program. DOS... Read More