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

no-contract cleaning service Des Plaines ..
In The News:

A 2025 data breach at fintech company 700Credit exposes personal information of more than 5.8 million people through compromised third-party integration partner.
Retailers lose $76.5 billion annually to return fraud as nearly 10% of U.S. retail returns involve fraudulent activity, with $850 billion in returns expected in 2025.
ShinyHunters claims responsibility for stealing 94GB of Pornhub user data affecting over 200 million records and demands Bitcoin ransom.
ChatGPT 2025 now connects to Apple Music, Canva, Expedia, TripAdvisor and OpenTable through built-in apps that help users create playlists, design graphics and more.
Apple releases emergency patches for two zero-day vulnerabilities actively exploited in attacks. iPhone and iPad users urged to update immediately.
DoorDash launches Zesty, an AI-powered social app that recommends restaurants through conversational search, now testing in San Francisco and New York.
Cybersecurity firm Infoblox reveals that over 90 percent of parked domains now redirect visitors to scams and malware, making simple typos extremely dangerous.
The Fox News AI Newsletter covers the latest artificial intelligence technology advancements, including the challenges and opportunities AI presents now and for the future.
GPT-5.2 is now live for all ChatGPT users with improved coding, writing and image interpretation, with Kurt Knutsson offering his review.
New iPhone replacement scam uses pressure tactics and fake carrier calls to steal devices from buyers. Criminals claim shipping errors and demand urgent returns.
Amazon Ring's new facial recognition feature sparks privacy controversy as Electronic Frontier Foundation critics argue the AI upgrade expands surveillance risks.
New Android banking trojan Sturnus steals credentials, reads encrypted messages and controls devices.
Denmark's 3D-printed student village proves automation builds 36 apartments faster than traditional methods. Skovsporet project shows housing future.
Discover Android's new Sound Notifications feature that alerts you to smoke alarms, doorbells, and baby cries even when wearing headphones.
New SantaStealer malware reportedly threatens holiday shoppers with password theft. This Christmas-themed info-stealer targets browsers and crypto wallets.
The Christmas season brings a surge in Netflix phishing scams targeting shoppers with fake emails. Stacey P received convincing scam but verified account first.
San Francisco Giants invite Jamie Grohsong to throw ceremonial first pitch at Oracle Park after he learned to play baseball with a bionic hand following an injury.
FBI warns cybercriminals are stealing family photos from social media to create fake proof of life images in virtual kidnapping scams targeting victims.
Instagram's new 'Your Algorithm' tool lets you control your Reels feed in real time. The app now gives you power to customize what videos you see.
Major Marquis fintech breach exposes 400,000-plus Americans' data through unpatched SonicWall vulnerability, with Texas hardest hit at 354,000 affected.
Free up iPhone storage fast by clearing large photos and videos from Messages app. Simple steps for iOS users to delete attachments without losing chats.
Scammers are flooding inboxes with fake tracking alerts that mimic real carriers, exploiting the holiday rush to steal logins and personal data.
The Fox News AI Newsletter brings you the latest news on AI technology advancements and the challenges and opportunities AI presents now and for the future.
Texas family reunites with missing 11-year-old cat Grayson after 103 days using Petco Love Lost's AI photo matching technology and community help.
Tired of AI customer service loops? These insider tricks help you escape "frustration AI" and get real human help when you need it most for urgent issues.

A Time-Saving Programming Tactic that Doesn?t Work

Let's say that you have a software project that's under... Read More

Do You Know These Facts About Spyware ?

Imagine something that follows you home and sets itself up... Read More

EDI: Electronic Document Interchange for Microsoft Great Plains ? Overview for Software Developer/Pr

Microsoft Great Plains - Microsoft Business Solutions accounting and ERP... Read More

Assertion in Java

Assertion facility is added in J2SE 1.4. In order to... Read More

Microsoft Great Plains Integration with Legacy Systems ? Overview For Developer

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

History of Java

The java programming language is becoming more and more popular... Read More

Basic Steps To Optimize Your Internet Security

After seeing many people complain about their weak Internet security... Read More

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

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

Crystal Reports For Microsoft RMS ? Overview For Developer/Report Designer

If you are software developer or database administrator - we... Read More

Microsoft Retail Management System (RMS) SQL Customization ? Overview for Programmer

Microsoft Retail Management System serves retail single store as well... Read More

Do You Want Your Own Fully Programmable ERP? - Part 1

We are in a transition phase in the Managerial Administration... Read More

The Hidden Power of Online Manual

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

Why Java RDBMS?

It is a well known fact that Java as a... Read More

String in Java

Handling character strings in Java is supported through two final... Read More

Dig Out That Worm

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

How To Create A Data Capture Procedure Checklist For Your Small Business CRM Software

Fortunately one of the most common reasons cited for the... Read More

Microsoft Great Plains GL: General Ledger ? Overview For Consultant

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

Causes of ERP Failures

ERP is the acronym of Enterprise Resource Planning. Multi-module ERP... Read More

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

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

Software For Your Hard-Drive

All your software is stored on a hard-drive. But how... Read More

Microsoft CRM Conversation Gateway: VoIP - Implementation & Customization

Microsoft CRM is winning market share step-by-step from such the... Read More

Bridging the Gap between Paper and Data

The cornerstone of successful automated office systems is the ability... Read More

C++ Tutorial 1, Introduction to C++

Introduction to C++Why Learn C++?C++ may at first seem like... Read More

20 Extra Hours Per Week: What Would You Do?

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

Internet Faxing Service Review

The Internet is reshaping every form of communications medium, and... Read More

bathroom cleaning service Lake Forest ..