[description], Story, any other text you want to use.

 by: Andrew Karasev

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<="+@char39+@Accountto+@char39 +"

group by
a.ACTNUMST,
case when
	b.PERDBLNC is null then 0
	else	b.PERDBLNC

end,
d.USERDEF1


) X

")

Happy querying and customizing! if you want us to help you - give us a call 1-866-528-0577! help@albaspectrum.com

About The Author Andrew Karasev is Chief Technology Officer in Alba Spectrum Technologies – USA nationwide Microsoft CRM, Microsoft Great Plains customization company, based in Chicago, Arizona, California, Colorado, Texas, New York, Georgia, Florida, Canada, UK, Australia and having locations in multiple states and internationally , he is Dexterity, SQL, C#.Net, Crystal Reports and Microsoft CRM SDK developer akarasev@albaspectrum.com

radio control

Broadcast On the Net Index

Main Menu
Broadcast On the Net
Site Map

News for 21-Jul-24

Source: BBC News - Home
Syrian girl with severe burns gets life-changing surgery

Source: BBC News - Home
How phones changed the imagery of the American campaign

Source: BBC News - Home
The technology of touch

Source: BBC News - Home
Africa's top shots: 16-22 December 2016

Source: BBC News - Home
Donald Trump gets 'very nice letter from Vladimir Putin'

Source: BBC News - Home
British passenger missing from Queen Mary 2 liner

Source: BBC News - Home
Crash survivor: 'I put my seatbelt on'

Source: BBC News - Home
Is Nigeria's 'plastic rice' actually real?

Source: BBC News - Home
Real-life Libya plane hijack halts hijack film shoot

Source: BBC News - Home
Kim Ghattas: Trump's Syria conundrum