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

ohare limo service Grand Rapids ..

In The News:

The International Olympic Committee on Friday announced plans to use AI in various Olympic aspects, including athlete identification, training and judging.

Tech guru Kurt "CyberGuy" Knutsson explains an easy trick to avoid squinting while working or surfing the web by zooming in on your personal computer.

Streaming giant Roku has recently been targeted by a pair of cyberattacks, and the company confirmed over a half million Roku accounts were compromised.

The Land Aircraft Carrier combines an all-terrain, six-wheeled vehicle with a two-seat aircraft, which features electric vertical takeoff and landing.

The European Union has sent TikTok a "request for information" on the video sharing platform's newest app, TikTok Lite, under the Digital Services Act, with the aim to clean up social media.

Stay up to date on the latest AI technology advancements and learn about the challenges and opportunities AI presents now and for the future.

The FBI is warning the public about a recent phishing scam via text that claims its targets owe money in Pennsylvania for unpaid road toll charges.

Your Apple Music settings may be revealing to your contacts what you're listening to. Kurt "CyberGuy" Knutsson explains the process to change those settings.

Learn how to secure social media accounts, implement legacy contacts, and create a digital estate plan to protect your online assets from 'ghost hackers' after your death.

A ported phone number scam is leading to more people having their identities stolen by sophisticated scammers. Kurt “CyberGuy" Knutsson tells you what you need to know.

The Great Pacific Garbage Patch, a vast marine debris vortex, is being tackled by The Ocean Cleanup’s innovative technologies. Kurt “CyberGuy" Knutsson explains.

Discover easy solutions to tame autocorrect frustration on iOS and Android devices. Turn off, customize and add personal touches to your typing experience.

Kurt “CyberGuy" Knutsson reveals the six top things to do right now before it’s too late: Tech and life choices that can keep your safer on and offline.

Kurt “CyberGuy" Knutsson shows you some simple steps to discreetly hide apps or an entire app page on your iPhone for decluttering or confidentiality.

The ambient light sensors on smartphones may be turned into cameras, according to researches at MIT. This could pose a threat to people's privacy.

The Autonomous Road Repair System from tech firm Robotiz3d uses artificial intelligence technology to locate potholes and promptly seal them.

Scammers have stooped to using artificial intelligence to scan obituary websites to create fake obituary websites to target the grieving.

Stay up to date on the latest AI technology advancements and learn about the challenges and opportunities AI presents now and for the future.

A Mercedes-Benz Manufacturing factory in Hungary is using humanoid robots to help with human staffing shortages on the factory floor.

Some video games act like unregulated banks, according to CFPB, which has begun monitoring the situation. The agency says children may be most at risk.

Ascender the robot is capable of climbing stairs with heights up to 8.7 inches, and it can give every corner of your home a deep cleaning.

Tax-return scams are on the rise, and one tax expert from a cybersecurity firm says filing your taxes on time is one way to avoid tax scams.

The innovative XPENG Robot Unicorn is inspired by the mythical unicorn, and you may forget the robot is not actually a living creature.

Stay up to date on the latest AI technology advancements and learn about the challenges and opportunities AI presents now and for the future.

The Federal Trade Commission, the federal agency to report scams to, is being impersonated as part of a new sophisticated phone scam.

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

Software