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

---

Microsoft Great Plains - Payroll & HR Inexpensive Solution? Not Any More

Microsoft Great Plains is main Microsoft Business Solutions accounting package... Read More

Internet Relay Chat - A Basic Introduction

What is IRC?IRC is Internet Relay Chat. It is a... Read More

OLAP, An Alternative Technology Over Spreadsheets

Are Spreadsheets Robbing your Enterprise of Competitive Advantage?'90% of "average"... Read More

15 Questions to Ask Your Software Vendor

When making a decision to buy any piece of software... Read More

IT Strategy for Midsize Business: Microsoft vs. Java, Great Plains & CRM

Midsize business or non-profit organization should decide if one-vendor solution... Read More

Free Software: How Not To Get More Than You Bargained For!

I completed an experiment recently. I wanted to find out... Read More

Microsoft CRM in Latin America: Implementation, Customization, Support ? Overview for Consultant

Microsoft Business Solutions CRM is present several years on the... Read More

Microsoft Great Plains: Manufacturing or Bill of Materials - Overview for IT Specialist

Microsoft Great Plains is main Microsoft Business Solutions product, targeted... Read More

Microsoft CRM and No-Frills Cadillacs

In today's business world it's all but impossible to escape... Read More

Keep Your Software Simple! A Review of EditPlus

I like my software simple. If it's too complex or... Read More

Microsoft Great Plains Security Setup - Overview For Consultant

Microsoft Business Solutions Great Plains is very good fit for... Read More

Is Your Computer Sick?

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

Microsoft Great Plains Customization: Project Organization ? International Business Example

Microsoft Business Solutions Great Plains fits to majority of horizontal... Read More

Microsoft CRM or Siebel

Siebel is traditional CRM market leader, however and mostly due... Read More

Story Development Software: Good or Evil?

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

Groupware and Online Collaboration: Collaboration Series #4

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

Adware, What Is It?

Adware is a type of Spyware program that displays some... Read More

Great Plains DOS Support ? Notes for Consultant

Great Plains Accounting, accounting package for mid-size and small companies... Read More

Increase Office Efficiency With One Simple Tool

When you need a phone number, you do a quick... Read More

Reduce Pop-ups and Annoying Ads

There is many things more frustrating than surfing a website... Read More

Microsoft CRM for Large Corporation ? Security

Microsoft Business Solutions CRM proved to be reliable solution in... Read More

Great Plains Custom Development: Dexterity, VBA, SQL, Crystal, eConnect ? Overview For Programmer

Microsoft Great Plains is main Microsoft Business Solutions accounting package... Read More

The Truth: Netzero 3G

We've all seen the ads on TV for Netzero 3G.... Read More

Microsoft Great Plains Customization and Development ? Overview for Programmer

When Great Plains Software was designing and developing Great Plains... Read More

FreeDOS

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

Microsoft Great Plains RW: Report Writer

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

Antivirus Software ? Get The Bugs Before They Get You!

You turn on your computer, and it doesn't look quite... Read More

QuarkXpress Tips: How to Move Content Between Print and Web Layouts

Since its release in 1987, QuarkXpress had made an immediate... Read More

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

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

Microsoft CRM Implementation & Customization: MS CRM Fax Gateway

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

Microsoft Great Plains in Metal Distribution: Implementation & Customization ? Consultant Overview

Microsoft Business Solutions Great Plains serves to the wide spectrum... Read More

Groupware: Avoid the Ad Hoc Shuffle

GroupwareEfforts are continually made to manage the unavoidable ad hoc... Read More

Did You Ever Want to Completely Erase Everything on Your Computer?

Did you ever want to erase everything on your computer?... Read More