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

---

Spyware Protection: A Must for Any Home Computer

Now that spyware is the single most dangerous threat to... Read More

Hubris - Definition: Microsofts Passport

Before September of 1995, Microsoft ignored the Internet because their... Read More

What Stands Behind The Software Description?

With thousands of web pages added to the Net every... Read More

Microsoft CRM: Data Conversion ? Import from Act!

Best Software Act! is very popular CRM for small and... Read More

What is Groupware?

Vince Lombardi once said that, "The achievements of an organization... Read More

What is Fleet Maintenance Software?

Simply put, fleet maintenance allows companies to monitor and maintain... Read More

Three Steps To Windows Safety Heaven

Now there are Three Steps To Heaven Just listen and... Read More

Microsoft Update - 5 Reasons to Update Service Pack 2

Microsoft released Service Pack 2 (SP2) for the Windows XP... Read More

Software Piracy - Global Increase

Pirated software is on the increase and now accounts for... Read More

Easy Guide to RAID Recovery

What is RAID RECOVERY?RAID stands for Redundant Array of Inexpensive... Read More

Dreamweaver vs FrontPage

There are two major WYSIWYG(What You See Is What You... Read More

Selecting Microsoft Great Plains Partner/VAR/Reseller: ERP Implementation & Customization ? Overview

In the case when you represent mid-size or mid-size-to-large business,... Read More

Software Tools To Help Your Business Sell More

Sales are all about leverage, because there is only so... Read More

The Dirt on Screensavers

Remember back in the days where screensavers were the coolest... Read More

Microsoft Great Plains Chemicals & Paint Industry Implementation & Customization Notes

Microsoft Great Plains fits to majority of industries, in the... Read More

5 Time-Saving Tips in Microsoft Word

Whether you have used Microsoft Word for years, have just... Read More

Document Templates Give You The Perfect Framework For Your Documents

When it comes to running an office, the SOHO entrepreneur... Read More

Implementing ERP for Large Publicly Traded Corporation ? Microsoft Great Plains

We would like first emphasize the change in the paradigm.... Read More

Create ASP in Minutes

DBxtra goes ASPGetting to the information hidden within corporate databases... Read More

Microsoft Great Plains Payroll Module Customization Scenarios

It is now common thing when large corporation selects mid-market... Read More

Microsoft Great Plains: Dexterity vs. eConnect ? FAQ

Microsoft Business Solutions Great Plains, former Great Plains Software Dynamics/eEnterprise... Read More

Navision Customization: C/SIDE, C/ODBC, C/FRONT, XBRL ? Development Options

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

Microsoft CRM Implementation & Customization: MS CRM Fax Gateway

With this small article we are continuing Microsoft Business Solutions... Read More

Create A Flash Presentation For Free With Open Office

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

Five Steps to Rapid Development with TierDeveloper 4.0

Follow the steps below to quickly design, generate, and deploy... Read More

Helping Newbies Understand Professional Software

The Windows registry is a huge database that ensures normal... Read More

CROOK: A Methodology for the Refinement of Forward-Error Correction

Table of Contents1) Introduction 2) Related Work 3) Framework 4)... Read More

Huddle Up; Groupware on Three

It could just be me, but my experiences with document... Read More

Call Alert Notifications - Free Answering Machine Software for PCs

If you're online using a dialup Internet connection, you'll probably... Read More

Microsoft Great Plains Integration with Legacy Systems ? Overview For Developer

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

Microsoft Great Plains SOP: Sales Order Processing

Microsoft Business Solutions Great Plains is marketed for mid-size companies... Read More

Upgrading Great Plains Dexterity Customization ? switching to new technologies: SQL, Crystal, eConne

1. Replace Dexterity cursor with SQL Stored Procedure Dexterity... Read More

The Hidden Power of Online Manual

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