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

green cleaning service Park Ridge ..
In The News:

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.
GAC Group's new Govy AirCab flying car combines lightweight design with advanced safety systems and smart cabin technology to revolutionize city travel with an 18.6-mile range.
The Berkeley Humanoid Light (BHL) is a lightweight, open source humanoid robot that anyone can build using 3D-printed parts and off-the-shelf components.
Meta’s new AI chatbot is getting personal, and it might be sharing more than you realize.
A new threat targets both Android and iPhone users: SparkKitty, a powerful mobile malware strain that scans private photos to steal cryptocurrency recovery phrases and other sensitive data.

Microsoft CRM ? Consulting in the Post-Recession Time

New post-recession era has new features, which didn't exist in... Read More

Antivirus Software ? Get The Bugs Before They Get You!

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

Microsoft Update - 5 Reasons to Update Service Pack 2

Microsoft released Service Pack 2 (SP2) for the Windows XP... Read More

Popular Fleet Maintenance Programs

Some companies that are in need of fleet management may... Read More

The Opera Alternative

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

C++ Tutorial 2, Input and Variables

This is the tutorial where we really get into programming.... Read More

OS 101: Suggestions for Choosing an Operating System

An operating system (abbreviated OS) is essentially the path through... Read More

Is Software Tester a Most Infamous Person in a Software Project Team?

The fact that a software tester is a most infamous... Read More

Open Source Concepts: Dual Licensing Explained

We were recently faced with a decision: either to let... Read More

How to Upgrade Dexterity Customization ? Tips for IT Manager

If you have Microsoft Great Plains and support it... Read More

Microsoft Great Plains Customization Recovery & Upgrade for Large Corporation

At the end of XX century, in the late 1990th... Read More

Ukraine IT Myths Dispersed

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

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

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

How to Backup Windows XP Home Edition

Your computer cost you from hundreds to thousands of dollars,... Read More

Make or Break Factors - When Considering Estimating Software

Make-or-Break Factors in Success and ProfitabilityFor quick printers, estimating can... Read More

Microsoft Great Plains Installation ? Overview for IT Director/Controller

Microsoft Great Plains is main mid-market application from Microsoft Business... Read More

Destination: Desktop for Google

First we had the original Google search that evolved into... Read More

Groupware: Answers the 5 Questions of Document Collaboration

Every organization which creates collaborative documents, whether they are budgets,... Read More

Microsoft CRM Customization ? Programming Closed Email Activity

Microsoft CRM is CRM answer from Microsoft and attempt to... Read More

What is Fleet Maintenance Software?

Simply put, fleet maintenance allows companies to monitor and maintain... Read More

Microsoft Great Plains PM: Payables Management

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

Microsoft Great Plains 8.0 Brazilian Version ? Overview For International Consultant

Microsoft Great Plains has substantial mid-market share in the USA... Read More

The XP Firewall Isnt Enough

You might think you don't need a firewall... Read More

Not All Project Management Software is Created Equal

The purpose of Project Management Software is to provide an... Read More

Spyware Protection: A Must for Any Home Computer

Now that spyware is the single most dangerous threat to... Read More

emergency cleaning services Morton Grove ..