Home
>
Software
>
Microsoft Great Plains: Interest Calculation Example ? Stored Procedure for Crystal Report

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--,

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

spotless home service Deerfield ..

In The News:

Google, Dior, Allianz and dozens of other companies lost sensitive customer data in Salesforce-related breaches affecting millions of records across multiple sectors.

Apple launches iOS 26 with new Preview app that combines document editing, PDF annotation and scanning features into one streamlined iPhone experience.

New AI road monitoring system uses sensor-embedded fabric to predict infrastructure problems, potentially reducing maintenance costs and traffic disruptions for cities.

Holiday charity scams target retirees through lookalike organization names, untraceable payment requests, and data broker information to steal donations.

The Federal Trade Commission says criminals are posing as IRS agents, law enforcement officers or other officials, often over the phone or online, to steal thousands of dollars at a time.

AI phishing scams now use voice cloning and deepfake technology to trick victims, but Kurt "CyberGuy" Knutsson reveals warning signs to watch for.

Inversion Space unveils Arc, a reusable reentry vehicle that can deliver up to 500 pounds of cargo from orbit to anywhere on Earth in under an hour.

Red flags like processing fees, urgent countdowns and requests for full Social Security numbers expose fraudulent settlement sites targeting consumers.

Comprehensive analysis of Google Maps, Waze and Apple Maps examines usability, routing accuracy, data handling and features across the top navigation platforms.

Expert analysis reveals whether wired Ethernet or wireless Wi-Fi connections are safer for home internet use, plus practical steps to secure your network from attackers.

Australian construction robot Charlotte uses sand, crushed brick and recycled glass to 3D print fireproof, floodproof homes with reduced carbon footprint.

Cybercriminals are using fake invitation emails to trick recipients into downloading malware and stealing personal information and data.

Flying drones could help retailers fight a 93% increase in theft rates as Flock Safety promotes airborne security systems to track suspects and deter crime.

The Fox News Artificial Intelligence Newsletter brings you the latest news on the emerging technology every Saturday, highlighting top stories.

Hacker group Radiant stole data from 8,000 children at Kido nursery chain, demanding ransom and directly contacting parents with intimidation tactics.

As 18 states implement bell-to-bell cell phone bans, creative students use Google Docs, iMessage on MacBooks and Post-It notes to stay connected in class.

A sheriff's captain says deputies often spend hours writing reports between calls, but Axon's AI program, Draft One, helps them save crucial time in the field.

Sora 2, OpenAI's new video-generation app, can create AI-generated videos based on a singular prompt. The results are both mind-blowing and terrifying.

iPhone and Android users can reduce Wi-Fi calling battery drainage through settings adjustments, background app limits and stronger Wi-Fi connections.

Work email scams are becoming harder to detect as criminals use AI and spoofed addresses to trick employees into opening malicious attachments and links.

From her Arizona living room, Christina Chapman ran a covert hub that helped North Korean operatives infiltrate U.S. firms, netting $17 million in stolen salaries.

UC Santa Barbara researchers developed a soft robotic intubation system that achieved 100% success rates for experts and 96% for paramedics with minimal training.

Scammers exploit probate filings to target grieving families with fake fees and debts, Kurt "Cyberguy" Knutsson reports.

Automotive giant Stellantis becomes latest victim of widespread Salesforce breaches affecting companies like Google, Cisco and Adidas this year.

A woman from Washington reunited with her missing Maine Coon cat Louie after 11 days using Love Lost, a free AI-powered pet recovery platform.

Microsoft Great Plains: Interest Calculation Example ? Stored Procedure for Crystal Report

Software