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

---

40/sec to 500/sec

IntroductionSurprised, by the title? well, this is a tour of... Read More

Dig Out That Worm

Internet worms. Is your PC infected?If your computer has become... Read More

Destination: Desktop for Google

First we had the original Google search that evolved into... Read More

Microsoft Great Plains Beverage Production & Distribution ? Implementation & Customization Highlight

Microsoft Business Solutions Great Plains has many years of successful... Read More

An Introduction to .NET Framework

.Net Framework is a platform or development environment to seamlessly... Read More

Microsoft CRM Integration with Lotus Notes Domino: Messaging Connector ? Future Directions

IBM Lotus Notes Domino and Microsoft CRM (Client Relation Management)... Read More

Microsoft Great Plains: Dexterity vs. eConnect ? FAQ

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

Vlans

In order to implement VLANs in a network environment, you'll... Read More

Performance Tuning of a Daffodil DB / One$DB -JDBC Application

This article illustrates the best practices to improve the performance... Read More

HelpDesk & Asset Management Software - Choosing The Right One

If you are to buy a HelpDesk & Asset Management... Read More

The Religion And Philosophy Of Small Internet Business

I have always had a tendency to focus on the... Read More

About Microsoft Sysprep (System Preparation) Utility (Windows 2000)

The Microsoft system preparation utility for Windows 2000 is a... Read More

Healthcare Preventive Maintenance Software

Healthcare facilities such as clinics, hospitals, and biomedical laboratories can... Read More

How to Build a Basic CSS Layout

Designing without tables by using CSS layouts is fast becoming... Read More

Microsoft CRM Customization Secrets ? Second Edition

This article is for advanced Microsoft CRM SDK C# developers.... Read More

Hubris - Definition: Microsofts Passport

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

Can You Calculate Complex Financial Calculations?

Are you a whiz at calculating financial information? Not the... Read More

MySQL for Beginners ? How to Create a MySQL Database

Whether you are an experienced web programmer or a complete... Read More

Microsoft CRM Customization: Integration with Third Party SQL Application/Database

Microsoft CRM ? Client Relationship Management package from Microsoft Business... Read More

Microsoft Great Plains: carpet, textile, fabric, felt distributor ? implementation overview

In this small article we will show you the possible... Read More

Windows Screensavers Explained

In this article you will find some background information about... Read More

Great Plains Dexterity Programming ? Overview For Developer

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

10 Things You Could be Using Photoshop For, But Probably Arent

Most people don't use Photoshop to its fullest capabilities. Here... Read More

The Secret of the Layer Styles Dialogue

When you double-click a layer in the Layer Palette, you... Read More

Microsoft Great Plains: Annual Enhancement Program ? How To Be Re-Enrolled

Microsoft Business Solutions Great Plains is mid and even corporate... Read More

Software Upgrades Arent Always the Best Move

When my daughter was getting into AOL instant messaging (AIM)... Read More

Cisco Certification: Introduction To ISDN, Part IV

In part III of this ISDN primer, we learned that... Read More

Dreamweaver vs FrontPage

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

Reloading Windows XP

If you have been running Windows XP for a couple... Read More

Microsoft Great Plains Furniture & Fixtures ? Implementation & Customization Highlights

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

Why Stick With Email Clients Like Outlook?

Trying to figure out a stream in banning one email... Read More

Beware of Spyware

One day, you suddenly realize that your computer started to... Read More

Crystal Reports For Microsoft Navision - Overview For Programmer/IT Specialist

Let us give you - developer some hints in the... Read More