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
no-contract cleaning service Des Plaines ..If there still are few unprotected computers left, I haven't... Read More
Microsoft Business Solutions Great Plains is marketed for mid-size companies... Read More
NOTE: Please take time to read on - it may... Read More
Heard about the Quark "killer"?Adobe InDesign CS2. Will it really... Read More
Currently Microsoft Business Solutions is on the way of creating... Read More
In our case ? we serve Microsoft Business Solutions ERP... Read More
If you are to buy a HelpDesk & Asset Management... Read More
Follow the steps below to quickly design, generate, and deploy... Read More
Microsoft Client Relation Management system (Microsoft CRM) and Microsoft RMS... Read More
Microsoft CRM is winning market share step-by-step from such the... Read More
.NET platform does not support multiple inheritance. Do not confuse... Read More
Microsoft Business Solutions Great Plains is very generic accounting application... Read More
I completed an experiment recently. I wanted to find out... Read More
The term "document management" and "paperless office" is the subject... Read More
Just the thought of a duel-boot scares many people away,... Read More
Around the same time Microsoft made its move with .Net... Read More
This article will not attempt to advocate the use of... Read More
It's all about turn times in the eMedia industry! The... Read More
The research in the field of Natural Language Processing usually... Read More
We will base our prognosis on our Microsoft Business Solutions... Read More
Introduction to C++Why Learn C++?C++ may at first seem like... Read More
Microsoft Business Solutions Navision serves both European and American megamarkets.... Read More
Microsoft Great Plains, designed back in 1990th as database transferable... Read More
When making a decision to buy any piece of software... Read More
Passwords protect your most sensitive personal, financial and business information.... Read More
bathroom cleaning service Lake Forest ..Lotus Domino/Notes ? Microsoft Great Plains tandem as ERP with... Read More
The world of small business accounting software can be a... Read More
As of now - Great Plains Dynamics/eEnterprise is transformed/renamed into... Read More
IBM Lotus Notes Domino and Microsoft CRM (Client Relation Management)... Read More
Rapid Application Development (RAD) is a software development methodology. In... Read More
1. Replace Dexterity cursor with SQL Stored Procedure Dexterity... Read More
Spyware is software or hardware installed on a computer without... Read More
Let's first look at your ERP system selection (without Retail... Read More
Although statistics often is blamed for various deadly sins --... Read More
Microsoft CRM is CRM application, maintained and supported by Microsoft... Read More
Most people don't use Photoshop to its fullest capabilities. Here... Read More
Considering whether or not your software company should hire a... Read More
You've finally created databases that you can actually use to... Read More
Siebel is traditional CRM market leader, however and mostly due... Read More
One of the main reasons business owners and entrepreneurs use... Read More
IBM Lotus Domino or Microsoft Exchange?The severe competition continues for... Read More
Microsoft Business Solutions ? Navision is an integrated solution for... Read More
Software development is a risky business.Many software developers are barely... Read More
If you have Microsoft Great Plains and support it for... Read More
Adobe Photoshop is one of the world's leading graphics editing... Read More
Domino server is a buffer between the operation system and... Read More
Homeland security, airport security, Internet security ??" these days we???re... Read More
Language development computer: Computer-based method for aiding language development seems... Read More
Well, even if the combination might look very unusual, we... Read More
Looks like Microsoft Great Plains becomes more and more popular,... Read More
Software |