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

non-toxic cleaning company Winnetka ..
In The News:

Researchers are now showing us that old smartphones as data centers could be the next big thing in sustainable tech.
Scientists have created micro-robots for sinus infection treatment that can enter the nasal cavity, eliminate bacteria directly at the source, and exit without harming surrounding tissue.
Let's examine how your data is collected in everyday life, who is buying and selling it, what happens to it afterward, and, most importantly, what you can do to protect yourself.
Researchers at the University of Sheffield in the U.K. developed small robots called "Pipebots" that can travel inside water pipes to find and potentially repair leaks, all without any excavation.
A groundbreaking new study has uncovered disturbing AI blackmail behavior that many people are unaware of yet.
Four teams of autonomous humanoid robots competed in China's first AI soccer tournament, demonstrating advanced capabilities in ball detection and independent decision-making.
The ID. Buzz autonomous van features self-driving technology with 13 cameras, nine LiDAR units and five radars after Volkswagen partnered with Hamburg and Uber for 2026 deployments.
Social Security phishing scams use urgency and impersonation to steal personal data. Learn how to spot fake SSA emails and implement 10 protective measures.
French startup Pazzi Robotics created an AI-powered pizza robot that made pies in under five minutes without human help, but it closed in 2022 despite patents and expert partnerships.
Reclaim your time from big tech with effective screen time reduction strategies for iPhone and Android, featuring steps to limit app usage and create phone-free zones.
Amazon Prime Day shoppers face threats from 120,000-plus scam websites as cybercriminals prepare phishing traps and malware ahead of the July sales event.
Fox News' AI Newsletter brings you the latest on this rapidly evolving technology.
The AEON humanoid robot tackles labor challenges with Nvidia AI, Microsoft Azure cloud and advanced spatial awareness, working alongside industry leaders Schaeffler and Pilatus.
Google's Ask Photos feature brings AI-powered searches to your photo library, letting you find memories with natural language queries while maintaining privacy controls.
The return of blue book exams emerges as universities fight widespread AI academic dishonesty and educators debate whether to ban AI tools or teach responsible usage.
Signs your phone might be hacked include strange behavior, unauthorized texts, battery drain and pop-ups, while protection involves updating software and avoiding public Wi-Fi.
Tokyo startup H2L has launched Capsule Interface technology enabling full-body robot control with muscle sensors, offering immersive remote operation.
The A.I. industry seems set for growing pains as Big Tech companies scramble for solutions to the medium's unprecedented strain on the power grid.
Fourth of July fireworks cause a 60% spike in lost pets, but GPS trackers and AI photo-matching services like Love Lost can help reunite missing dogs with their families.
Protect yourself from jugging, the rising crime by which thieves monitor ATM users and follow them to steal cash, with six practical safety tips to stay alert and secure.
U.S. airlines like Delta, American and United are selling your domestic flight records to government agencies through the little-known Travel Intelligence Program.
Autonomous robots from Uber Eats are rolling out across U.S. cities, featuring LIDAR sensors, secure compartments and all-weather operation for food and grocery delivery.
Costco has expanded into EV infrastructure with new ultra-fast charging stations that can charge most electric vehicles to 80% in 20-60 minutes while shoppers browse the warehouse.
The new Gemini Robotics On-Device AI allows robots to perform complex tasks without internet, offering enhanced privacy, reliability and adaptability for real-world use.
Protect yourself from Amazon phishing scams by spotting red flags like suspicious sender addresses and spelling errors while using Amazon's Message Center to verify communications.

Internet Relay Chat - A Basic Introduction

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

Things You Can Do With Windows XP!

Did you ever give a thought to the number of... 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

Great Plains Dexterity Development: FAQ

Microsoft Business Solutions Great Plains is Dexterity-written application and currently... Read More

Free Software for Newbies and Web Developers

Here is some free software tools to help you build... Read More

Freight Forwarding ERP: Microsoft Business Solutions Great Plains customization & setup ? overview

Microsoft Great Plains may be recommended for international freight forwarding... Read More

Design a Web Album Using Adobe Photoshop- Part 2

So let's begin crunching down these 300 images using Adobe... Read More

Make 2005 the Year You Save Time!

Today's business world is fast-paced. No matter what it is... Read More

eCommerce development for Microsoft Great Plains: tools and highlights for programmer

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

Rapid Application Development - Is it Really a Need of Today?

Rapid Application Development (RAD) is a software development methodology. In... Read More

Groupware as a Document Manager: Collaboration Series #3

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

Know Linux

Linux essentials:It's free for download but you have to pay... Read More

Adware and Spyware

What is 'adware'?Adware is basically software or scripts that are... Read More

Great Plains Dexterity History and Programming Overview

As of now - Great Plains Dynamics/eEnterprise is transformed/renamed into... Read More

Internet Faxing Service Review

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

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

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

Oracle Development: JDeveloper 10G ? Java, J2EE, EJB, MVC, XML - Overview For Programmer

In 2004 Oracle, Inc. made its new step toward J2EE... Read More

Microsoft Great Plains Implementation for Large Corporation ? Overview for VP IT

Microsoft Business Solutions Great Plains was historically designed to serve... Read More

An Easy Way to Deal with Email Viruses and Worms

If you feel intimidated when someone tries to teach you... Read More

Microsoft Great Plains in Construction & Building ? Implementation & Customization Highlights

Microsoft Great Plains could be tuned and setup to fit... Read More

Free Software - Powerful Alternatives to Budget-Busting Software

When you buy a computer, it most likely comes with... Read More

Benefits of Shareware

Shareware has been fighting the stigma of being misunderstood for... Read More

Business Planning Software

Once a business idea is selected, it is highly recommended... Read More

Builders Beware

Which Type of Shop Can Rely On A Home Built... Read More

Will Adobe Manage to Replace Industry Work Horse Quark Express by Giving Adobe InDesign for Free?

And kill the best layout software in the process of... Read More

experienced cleaning professionals Glencoe ..