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

efficient cleaning crew Northbrook ..
In The News:

Chinese hackers used Anthropic's Claude AI to launch autonomous cyberattacks on 30 organizations worldwide, marking a major shift in cybersecurity threats.
Apple's new Sleep Score feature gives you a rating for your nightly rest quality. Learn how to set it up on your Apple Watch and iPhone today.
Essential phone settings to enable before losing your device, including Find My network, location services and security features for iPhone and Android.
The Fox News AI Newsletter gives readers the latest AI technology advancements, covering the challenges and opportunities AI presents.
Cybersecurity research shows weak passwords remain a major threat, with simple patterns and number sequences putting millions of accounts at risk.
New Android malware BankBot YNRK silences phones, steals banking data and drains crypto wallets automatically. Learn how this advanced threat works.
FDA approves first human trial for Paradromics' brain-computer interface that could restore speech for paralyzed patients through neural technology.
New phishing platform QRR targets Microsoft 365 users across 1,000 domains in 90 countries. Learn how to spot fake login pages and protect your accounts.
OpenTable now uses AI to track your dining habits and share insights with restaurants. Learn what data they collect and how to protect your privacy.
Google's discontinued Nest thermostats still secretly upload home data to company servers despite losing smart features, raising serious privacy concerns.
New Android malware NGate steals NFC payment codes in real-time, allowing criminals to withdraw cash from ATMs without your card. Learn protection tips.
DoorDash confirms data breach exposing customer names, emails, addresses after social engineering attack. Learn how to protect yourself from scams.
Concerned about Google's AI scanning your Gmail? Learn how to disable Gemini features that access your emails, Drive files and Chat messages for privacy.
Google warns Android users about dangerous fake VPN apps hiding malware that steals passwords, banking details and personal data from phones and tablets.
Apple's digital passport feature lets iPhone users breeze through TSA checkpoints this holiday season using Digital ID technology at 250+ airports.
A new phishing scam targets family photos with fake "Cloud Storage Full" alerts. Criminals steal credit card information through fake sites. Learn protection tips.
South Korean scientists create ultra-thin fabric muscles that turn clothes into robotic assistants, lifting 33 pounds while weighing under half an ounce.
Archer Aviation has acquired Hawthorne Airport for $126M to launch an LA air taxi network ahead of the 2028 Olympics, featuring AI-powered eVTOL operations and next-gen aviation tech.
Stay up to date on the latest AI technology advancements and learn about the challenges and opportunities AI presents now and for the future.
Fake AI apps disguised as "ChatGPT" and "DALLĀ·E" are flooding app stores with dangerous malware that steals data and monitors users without detection.
Fake buyers demand specific vehicle reports from unknown sites to steal credit card information from car sellers, but warning signs can help identify these scams before paying.
Android users can now manage apps across multiple devices more easily with Google Play Store's updated remote uninstall button in the latest update.
NASA's Perseverance rover discovers shiny metallic rock on Mars that could be a meteorite from an ancient asteroid, containing high levels of iron and nickel.
Holiday scams spike during Black Friday and Cyber Monday as criminals exploit your leaked personal data. Learn how to protect yourself from fake stores and phishing.
Commerce Department proposes TP-Link router ban over Chinese security risks. Learn how this potential prohibition could affect your home network and devices.

Basic Tips and tricks for Windows XP

Running Applications in Compatibility Mode With Windows XP, you can... Read More

ERP Consulting: Microsoft Great Plains Partner Future Directions

In the Clinton era the status quo was simple: you... Read More

Programming Environments And The Software Production Process

Introduction: The creating of a computer program involves a number... Read More

Theres Gold in Your Websites Server Log

Many Webmasters have never bothered to view their website's server... Read More

Free Microsoft Word Online Training Tutorial Resources

Microsoft Word is one of the most popular office applications... Read More

Great Plains Accounting Migration to Microsoft Great Plains - Overview for IT Specialist

This is a short article, written in question/answer/FAQ style to... Read More

Pros and Cons of Using FREE Software in Your Business

It???s easy to understand why you might be drawn to... Read More

Mozilla Vs. Firefox

For those who are unclear on the differences between the... Read More

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

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

MultiNational Corporation ERP Implementation ? Microsoft Business Solutions Great Plains

If you look back to the history, you will see... Read More

OSI Layers Model

IntroductionDuring the early years of our modern computer era, very... Read More

Microsoft Great Plains Remote Support - Overview

Microsoft Great Plains is now standard mid-market ERP application, serving... Read More

Microsoft Great Plains Inventory Control ? Overview For Consultant

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

Microsoft CRM: Implementation, Customization, Support ? Consultant Overview

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

Keeping Track

One of the things we can be as certain of... Read More

Linux Dual Boot-How To Format

Just the thought of a duel-boot scares many people away,... Read More

Software Companies: Generate New Revenue Streams and Decrease Costs with Custom e-Learning Content

It's no secret that software companies operate in a very... Read More

Lotus Notes Domino and Web: Application Development ? Tips for Programmers

Beginning with Domino version R4 it has integration with the... Read More

ERP Remote Support: Microsoft Great Plains Analysis ? Pluses & Minuses

Former Great Plains Software Dynamics/eEnterprise and currently Microsoft Business Solutions... Read More

Microsoft CRM Modification ? Overview for IT Specialist

Microsoft CRM is now on the scene and it is... Read More

.NET :Solving the Multiple Inheritance Issue Under .NET Platform

.NET platform does not support multiple inheritance. Do not confuse... Read More

Bill of Lading ? Custom Reporting for Microsoft Great Plains ? Overview for Consultant

Bill of Lading is required report for Logistics and Freight... Read More

SQL Administrator Skills Required to Support Microsoft Great Plains

Microsoft Great Plains is becoming more and more popular and... Read More

Create A Flash Presentation For Free With Open Office

The intentions of this short tutorial are not to teach... Read More

How to Make Own CMS

Every day millions of new web documents emerge on the... Read More

cleaning lady near Wilmette ..