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
scheduled cleaning services Winnetka ..Three highly respected names in Human Resources have joined forces... Read More
User interfaces and accessibility are some of the most important... Read More
Microsoft Business Solutions Small Business Manager is Great Plains Dexterity... Read More
Whether you need to close a sale, gather end-user feedback,... Read More
What is Software?Software is a set of instruction written to... Read More
Microsoft Great Plains is one of the Microsoft Business Solutions... Read More
Microsoft Business Solutions Great Plains, Solomon, Navision, Axapta, Microsoft CRM... Read More
Anti-virus software is used to find, remove or fix files... Read More
This article illustrates the best practices to improve the performance... Read More
Looks like Microsoft Great Plains becomes more and more popular,... Read More
Did you ever give a thought to the number of... Read More
Spyware is like the new technological nuclear weapon on the... Read More
XML Server can be a Web Server that stores the... Read More
This is intermediate level SQL scripting article for DB Administrator,... Read More
Executive SummaryAn effective plan for entering, cleaning and updating the... Read More
Whether you are a small consultancy firm, a medium sized... Read More
Microsoft Business Solutions Great Plains was historically designed to serve... Read More
How do you run a program on a remote server... Read More
Microsoft Business Solutions Great Plains fits to majority of horizontal... Read More
Microsoft Great Plains has substantial mid-market share in the USA... Read More
For a long time now Microsoft's Internet Explorer has ruled... Read More
Microsoft Word is one of the most popular office applications... Read More
An operating system (abbreviated OS) is essentially the path through... Read More
Midsize business or non-profit organization should decide if one-vendor solution... Read More
Just imagine: you are walking, say, towards your car, and... Read More
reliable maid service Lincolnshire ..When you buy a computer, it most likely comes with... Read More
Table of Contents1) Introduction 2) Related Work 3) Framework 4)... Read More
Internet worms. Is your PC infected?If your computer has become... Read More
Spyware and Adware infest over 90 percent of computers in... Read More
It is possible that if one avoided all sources of... Read More
I completed an experiment recently. I wanted to find out... Read More
Considering whether or not your software company should hire a... Read More
It won't matter how effective your WinRunner Team is if... Read More
As you probably know, when Microsoft purchased Great Plains Software... Read More
Intro This concise article will tell you in plain English... Read More
MicroWorld Technologies, Inc. the leading solutions provider in the area... Read More
Domino server is a buffer between the operation system and... Read More
Microsoft Business Solutions offers several ERP applications: Great Plains, Navision,... Read More
Microsoft Business Solutions Navision serves both European and American megamarkets.... Read More
Document Manager and Version HistoryIn previous articles I have discussed... Read More
Microsoft Business Solutions main middle market ERP application - Microsoft... Read More
An integral part of any quality CRM system is lead... Read More
When Great Plains Software was designing and developing Great Plains... Read More
You would like to protect your documents, wouldn't you? Reasons... Read More
First we had the original Google search that evolved into... Read More
Anti-virus software is used to find, remove or fix files... Read More
Microsoft Business Solutions Great Plains was historically designed to serve... Read More
With the advent of 'Service Pack 2' for Windows XP... Read More
Remember back in the days where screensavers were the coolest... Read More
Innovative Maintenance Systems (IMS) is one company that offers solutions... Read More
Software |