|
[description], Story, any other text you want to use.
Microsoft Great Plains: Interest Calculation Example – stored procedure for Crystal Report
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
|
broadband multimedia
Real Time Media On The Net
This is one of the best resources for information on Real Time Media On The Net you can possibly find.
Follow our links to find more information on Real Time Media On The Net.
We hope that we have all of the Real Time Media On The Net details that you could need.
Real Time Media On The Net
|
The Internet is fast becoming the dominant medium for business and communication, but it still resembles something of a frontier, because there is little regulation. If you are looking for broadband multimedia then you are doing so in an unregulated marketplace. Most efforts have relied on the Internet industry to police itself. Although there has been some notable success with self-policing, continued abuses have increased calls for government intervention. That's where our role in pre-checking broadband multimedia sites comes in. Our broadband multimedia provider is solid and reliable.
Some aspects of the Internet could undoubtedly use some regulation, but this task is not as simple as it may seem. The very nature of the Internet makes it difficult, if not impossible to regulate. However in the midst of this many broadband multimedia retailers survive and prosper. At the same time, the absence of regulations means that everyone who uses this essentially public network can be a target for anyone who has the technical know-how and the will to invade their privacy. Privacy was foremost in our minds when sourcing the right broadband multimedia retailer for you. Their link appears above.
Real Time Media On The Net Index
|