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

Is Your Small Business Ready For A CRM Software Solution?

I have yet to see a business that, sometimes in spite of themselves, didn't benefit... Read More

ERP System of the Future: Database, Business Logic and Interface

We will base our prognosis on our Microsoft Business Solutions ERP consulting practice: Microsoft Great... Read More

Spyware - The Internet Devil Of Our Times!

Spyware and Adware infest over 90 percent of computers in use today. Internet linked computers... Read More

SQL scripts for Project Accounting: Microsoft Great Plains series ? overview for developer

Microsoft Business Solutions Great Plains has Project Accounting module where you can budget the project,... Read More

ERP for Large Corporation ? New Selling Strategy

Our opinion is based on our Microsoft Business Solutions Great Plains, Microsoft CRM sales and... Read More

Microsoft RMS ? Great Plains Integration ? Overview For IT Specialist

Microsoft Great Plains and Microsoft Retail Management System (Microsoft RMS) are getting new market share... Read More

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

Ok... Where to start?Well, I guess I will start at the beginning.....Like many surfers and... Read More

History of Java

The java programming language is becoming more and more popular each day. It is the... Read More

Microsoft CRM Integration With Microsoft Office Documents ? Overview For System Integrator

Microsoft CRM is CRM application, maintained and supported by Microsoft Business Solutions. Microsoft CRM utilizes... Read More

Fundraising Software ? How Can That Help Me?

Fundraising software lets you connect with donors in a way that is unlike any communication... Read More

Save Your Resources - Combine Your IM Clients

There are so many different programs that clutter up your desktop and icon bar, every... Read More

Great Plains Dynamics on Pervasive/Ctree support ? overview for consultant

All of us know that Microsoft bought former Great Plains Software and formed Microsoft (Great... Read More

My Experience - Making a Vision into Reality

Disclaimer: All the thoughts expressed are my views only! Your perception might differ...1. How different... Read More

Free Program Removes Spyware not Detected by Premium Security Scan

What is Spyware?Spyware monitors your surfing habits and sends the data off to some remote... Read More

Linux ? Keyboard Or Mouse

Just stress testing one of the latest Linux distributions. Been testing it for about two... Read More

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

Microsoft Business Solutions Great Plains serves to the wide spectrum of horizontal markets and could... Read More

Introduction To ISDN, Part III: PAP

Introduction To ISDN, Part III: Configuring PPP PAP AuthenticationNow we know how the ISDN link... Read More

The Truth about Colossus: Are You Just A Magnetic Image?

What is Colossus?Colossus is software licensed to about twenty-five insurance companies to aid in predicting... Read More

Snort for Network IDS

What is Snort?Snort is an open source network intrusion detection system (NIDS) that can audit... Read More

The Opera Alternative

Security flaws have long plagued Internet Explorer (IE), the market-dominating web browser from Microsoft. IE... Read More

PHP On-The-Fly!

IntroductionPHP can be used for a lot of different things, and is one of the... Read More

Database Guru James F. Koopmann Reviews DBxtra Reporting and Query Tool

DBxtra is a powerful query and reporting tool that hides many of the complexity and... Read More

Microsoft Great Plains implementation: Restaurants Supply Chain Management Example

Microsoft Great Plains serves majority of US based horizontal and vertical markets. Being relatively flexible... Read More

Software Process Improvement -A Successful Journey

Background: For many organizations like ours, the interim target of achieving ISO 9001 or CMM... Read More

How To Choose A Fire Wall Software Program

In the real world a "fire wall" is a fireproof wall that is built to... Read More

An Easy Way to Deal with Email Viruses and Worms

If you feel intimidated when someone tries to teach you something new on the computer,... Read More

What to Do if All Screensavers Fun is Grayed Out?

Finally, you have some time to personalize your desktop with an interesting screensaver. But wait...... Read More

Scrap Booking Online: Word Perfect or Corel Graphics Suite?

Scrapbooks are very popular these days. I think that almost everyone wanted... Read More

Dashboard Widgets for Windows

For a windows user like me, just can watch with envy the new eye candy... Read More

Software Engineering: An Introduction

Software Engineering is the Systematic Approach for analysis design implementation and maintenance of Software .It... Read More

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

I completed an experiment recently. I wanted to find out exactly what software I could... Read More

Keep Your Software Simple! A Review of EditPlus

I like my software simple. If it's too complex or difficult to figure out, I... Read More

Microsoft CRM Lotus Notes Domino Connector FAQ

Microsoft Business Solutions CRM and IBM Lotus Notes Domino, being two groupware products from competing... Read More

Microsoft Great Plains on Ctree or Pervasive SQL ? What to Do ? Tips for IT Manager

As you probably know, when Microsoft purchased Great Plains Software ? the whole strategy for... Read More

What Stands Behind The Software Description?

With thousands of web pages added to the Net every day, it's getting more and... Read More

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

Microsoft Business Solutions CRM is present several years on the US software market plus it... Read More

Data Quality Best Practices for Salesforce.com

Executive SummaryAn effective plan for entering, cleaning and updating the data for your salesforce.com (SFDC)... Read More

Instant Messenger Clients

If you have been using the Internet for any amount of time you have probably... Read More

Groove Network. Are you in it?

If you are in a business that passes documents around to be reviewed and edited... Read More

Hubris - Definition: Microsofts Passport

Before September of 1995, Microsoft ignored the Internet because their 16-bit Windows 3.1 operating system... Read More

A Simple Computer Software Definition

What is Software?Software is a set of instruction written to interface between man and machine.Who... Read More

Microsoft Great Plains - Typical Problems And Fixes ? Overview For IT Administrators

How to delete the user? This is the first problem you encounter - when user... Read More

An Easy Way to Develop JAVA Enterprise Applications

Research bears that less than 70 percent of development projects are actually completed, and more... Read More

Professional XP Style Icons in Software and Website Development

Using professional icons in your application or website can bring a modern and aesthetically pleasing... Read More

Business Plan Software Review: Ultimate Business Planner?

One of the main reasons business owners and entrepreneurs use business plan software is to... Read More

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

1. Replace Dexterity cursor with SQL Stored Procedure Dexterity was designed as multiplatform technology... Read More

Benefits of Shareware

Shareware has been fighting the stigma of being misunderstood for decades. While corporate software giants... Read More

Microsoft Great Plains Customization and Development ? Overview for Programmer

When Great Plains Software was designing and developing Great Plains Dynamics/Dynamics CS+/eEnterprise - it placed... Read More

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

Not all of us have the luxury of working both at home and at work... Read More

Change Your Word Docs in Record Time!

Anyone who has ever used Microsoft Word knows that it is a powerful word processing... Read More