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
disinfecting cleaning services Winnetka ..Microsoft Business Solutions Small Business Manager is Great Plains Dexterity... Read More
Microsoft Great Plains fits to majority of industries, in the... Read More
Fundraising software lets you connect with donors in a way... Read More
An integral part of any quality CRM system is lead... Read More
NOTE: Please take time to read on - it may... Read More
Microsoft Business Solutions Great Plains as new ERP for multinational... Read More
Scrapbooks are very popular these days. I think that almost... Read More
Several years ago Microsoft purchased Great Plains Software, then Navision... Read More
We are in a transition phase in the Managerial Administration... Read More
For those who still don't know, Microsoft Publisher helps computer... Read More
Finally, you have some time to personalize your desktop with... Read More
Microsoft CRM is CRM answer from Microsoft Business Solutions.The whole... Read More
Adware is a type of Spyware program that displays some... Read More
Stealing company information used to be the specialty of spies... Read More
If you feel intimidated when someone tries to teach you... Read More
We live in a post-industrial age where information is the... Read More
What is RAID RECOVERY?RAID stands for Redundant Array of Inexpensive... Read More
The Windows Indexing Service provides you with the ability to... Read More
Best Software Act! is very popular CRM for small and... Read More
Some companies that are in need of fleet management may... Read More
With many manufacturing shops heading over seas in favor of... Read More
If someone entered your home, uninvited and installed numerous cameras... Read More
When you visit department stores and see that majority of... Read More
GroupwareEfforts are continually made to manage the unavoidable ad hoc... Read More
What is Groupware?Have you ever had to manage document collaboration... Read More
last minute cleaning help Highland Park ..When Great Plains Software introduced the first graphical accounting application... Read More
If you have Microsoft Great Plains as main accounting and... Read More
This article illustrates the best practices to improve the performance... Read More
When it comes to screenplay software each screenwriter needs to... Read More
If you are in a business that passes documents around... Read More
In the real world a "fire wall" is a fireproof... Read More
In this small article we will show you the possible... Read More
Microsoft Business Solutions CRM and IBM Lotus Notes Domino, being... Read More
The research in the field of Natural Language Processing usually... Read More
Microsoft Business Solutions CRM is now approaching the phase of... Read More
Whether you have used Microsoft Word for years, have just... Read More
Microsoft Business Solutions is emerging as very attractive vendor for... Read More
XML Server can be a Web Server that stores the... Read More
IBM Lotus Notes Domino and Microsoft CRM (Client Relation Management)... Read More
XML parser is a software module to read documents and... Read More
When you think... Read More
It won't matter how effective your WinRunner Team is if... Read More
The Microsoft system preparation utility for Windows 2000 is a... Read More
Microsoft Business Solutions Small Business Manager is Great Plains Dexterity... Read More
A LOT OF UNWANTED FILES.When you uninstall an item of... Read More
Well, even if the combination might look very unusual, we... Read More
The Windows Indexing Service provides you with the ability to... Read More
There are two approaches for application integration:? Programmer's approach ?... Read More
When Windows fails to boot it is normally caused by... Read More
I have recently created my first Php program. I wanted... Read More
Software |