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
non-toxic cleaning company Winnetka ..What is IRC?IRC is Internet Relay Chat. It is a... Read More
Did you ever give a thought to the number of... Read More
I completed an experiment recently. I wanted to find out... Read More
Microsoft Business Solutions Great Plains is Dexterity-written application and currently... Read More
Here is some free software tools to help you build... Read More
Microsoft Great Plains may be recommended for international freight forwarding... Read More
So let's begin crunching down these 300 images using Adobe... Read More
Today's business world is fast-paced. No matter what it is... Read More
Microsoft Business Solutions Great Plains, former Great Plains Software Dynamics... Read More
Rapid Application Development (RAD) is a software development methodology. In... Read More
This article is the third of a series of articles... Read More
Linux essentials:It's free for download but you have to pay... Read More
What is 'adware'?Adware is basically software or scripts that are... Read More
As of now - Great Plains Dynamics/eEnterprise is transformed/renamed into... Read More
The Internet is reshaping every form of communications medium, and... Read More
Microsoft CRM ? Client Relationship Management package from Microsoft Business... Read More
In 2004 Oracle, Inc. made its new step toward J2EE... Read More
Microsoft Business Solutions Great Plains was historically designed to serve... Read More
If you feel intimidated when someone tries to teach you... Read More
Microsoft Great Plains could be tuned and setup to fit... Read More
When you buy a computer, it most likely comes with... Read More
Shareware has been fighting the stigma of being misunderstood for... Read More
Once a business idea is selected, it is highly recommended... Read More
Which Type of Shop Can Rely On A Home Built... Read More
And kill the best layout software in the process of... Read More
experienced cleaning professionals Glencoe ..How do you run a program on a remote server... Read More
Microsoft Business Solutions Great Plains is marketed for mid-size companies... Read More
Before being able to choose a secure Internet communication system,... Read More
In order to implement VLANs in a network environment, you'll... Read More
In our case ? we serve Microsoft Business Solutions ERP... Read More
Microsoft Client Relation Management system (Microsoft CRM) and Microsoft RMS... Read More
In the early days of the personal computer, we're talking... Read More
An operating system (abbreviated OS) is essentially the path through... Read More
The software giants don't do everything and don't always produce... Read More
IntroductionDuring the early years of our modern computer era, very... Read More
Since its release in 1987, QuarkXpress had made an immediate... Read More
We don't think about mainframe software pricing anymore, we just... Read More
We've all seen the ads on TV for Netzero 3G.... Read More
Microsoft bought Navision, Denmark based software development company, along with... Read More
"Pfishing", sometimes spelled "Phishing", is a word that's used to... Read More
Rapid Application Development (RAD) is a software development methodology. In... Read More
In today's business world it's all but impossible to escape... Read More
The java programming language is becoming more and more popular... Read More
Microsoft Office program is a programming tool called Visual Basic... Read More
Whether you are an experienced web programmer or a complete... Read More
Logistics automation is often considered as barcoding extension to Sales... Read More
Microsoft Great Plains is one of the Microsoft Business Solutions... Read More
Adobe Photoshop is one of the world's leading graphics editing... Read More
Microsoft Great Plains has full-featured Manufacturing suite of modules: Capacity... Read More
Once a business idea is selected, it is highly recommended... Read More
Software |