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

---

Story Development Software: Good or Evil?

In the early days of the personal computer, we're talking... Read More

What You Should Know About Installing Screensavers

Do you remember that frustrating feeling when you find an... Read More

Manufacturing Outsourcing: Microsoft Great Plains Implementation, Customization & Reporting

Manufacturing in the USA is far away down from mid... Read More

Microsoft CRM Customization - Processing In/Out-Going Email Messages

We would like to give you several situations, when you... Read More

Computer Based Language Development and Spell-checking

Language development computer: Computer-based method for aiding language development seems... Read More

Lotus Notes Domino and Microsoft CRM Integration

Well, even if the combination might look very unusual, we... Read More

SSH (SCP) Send Files from Windows to Your Linux Box

Not all of us have the luxury of working both... Read More

Change Your Word Docs in Record Time!

Anyone who has ever used Microsoft Word knows that it... Read More

Software Development in 2005 - Back to the Future

2005 ? Back to the Future.What does the future hold?... Read More

Microsoft Small Business Manager Customization Options - Overview

Microsoft Business Solutions Small Business Manager is Great Plains Dexterity... Read More

Microsoft Great Plains Integrations - Tips for Developer

In this short FAQ style article we would like to... Read More

SpyBot - Search and Destroy versus Microsoft AntiSpyware (Beta).... My personal experiences...

Ok... Where to start?Well, I guess I will start at... Read More

Microsoft Great Plains Reporting ? Overview for Developer

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

Linux Secrets

The first thing that you will notice about Linux Red... Read More

.Net Charts and Graphs Interact with Businesses and Customers

Bar charts, bar graphs, and any other chart or graph... Read More

MicroWorld Releases New Version of MailScan Ver. 4.5 - Antivirus and Content Security Software

MicroWorld Technologies, Inc. the leading solutions provider in the area... Read More

Inherent Dangers Of File Sharing Via The Internet.

Cyberspace has opened up a new frontier with exciting possibilities... Read More

10 Ways to Learn a Software

Following tips help you to learn a software in lesser... Read More

20 Extra Hours Per Week: What Would You Do?

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

Integrating Microsoft Great Plains Accounting/ERP: RMS, CRM, eCommerce, Lotus Domino ? overview

Microsoft Business Solutions Great Plains has substantial market share among... Read More

Microsoft Great Plains: Data Conversion & Migration Scenarios ? Overview for Consultant

Microsoft Business Solutions Great Plains serves multiple industries in the... Read More

Google Brings the Earth to Your Desktop

Google Inc. has launched a new software package that allows... Read More

Microsoft Business Solutions Customization Options - Overview for Programmer

Several years ago Microsoft purchased Great Plains Software, then Navision... Read More

Groupware and Version History: Collaboration Series #1

This article is the first of a series of articles... Read More

Most Dangerous Types of Spyware are on the Rise: How to Choose the Weapon

Bad News - the Threat is Bigger than it SeemedHow... Read More

Why Stick With Email Clients Like Outlook?

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

Recovering Microsoft Great Plains Customization ? Tips for IT Director

Remember nice and prosperous Clinton era? When you implemented innovative... Read More

How the Firefox: How to... Manual Helped Me

A few months back I really got sick of my... Read More

Choose your Java Wisely

Java has come along a long way. Many would agree... Read More

CRM and Customer Life Cycle

Customer Relationship Management or CRM is a combination of enterprise... Read More

Cisco CCNA Certification: Becoming A Truly Valuable CCNA.

I've been active in the Cisco Certification track for four... Read More

Microsoft Great Plains Customization Tools Evolution ? Overview for Consultant

When Great Plains Software introduced the first graphical accounting application... Read More

Ukraine IT Myths Dispersed

While Ukraine is becoming a new popular IT outsourcing destination,... Read More