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 Mundelein ..Professional services firm cuts costs and improves productivity with integrated... Read More
Vince Lombardi once said that, "The achievements of an organization... Read More
In 2004 Oracle, Inc. made its new step toward J2EE... Read More
Microsoft Great Plains has substantial mid-market share in the USA... Read More
As seeing large number of implementations ? in our case... Read More
Do you want to get quality software at a reasonable... Read More
If you feel intimidated when someone tries to teach you... Read More
Document Manager and Version HistoryIn previous articles I have discussed... Read More
Every day millions of new web documents emerge on the... Read More
Now is the time to look at an alternative to... Read More
Customer Relationship Management or CRM is a combination of enterprise... Read More
Scrapbooks are very popular these days. I think that almost... Read More
Sometimes your PC will start acting strange for no apparent... Read More
Looks like Microsoft Great Plains becomes more and more popular,... Read More
How do you run a program on a remote server... Read More
Mike Dunville* had a decision to make. As the new... Read More
Microsoft Retail Management (RMS) and Microsoft Great Plains are retail... Read More
Table of Contents1) Introduction 2) Related Work 3) Framework 4)... Read More
In our small article we'll consider Microsoft Business Solutions Great... Read More
Microsoft CRM is now on the scene and it is... Read More
Once a business idea is selected, it is highly recommended... Read More
I suggest that you do not spend a lot of... Read More
I started using PIP (Picture It Publishing) Platinum 2002 right... Read More
Currently Microsoft Business Solutions is on the way of creating... Read More
Software development is a risky business.Many software developers are barely... Read More
maid service near Glenview ..Anyone who has ever used Microsoft Word knows that it... Read More
When you double-click a layer in the Layer Palette, you... Read More
We live in a post-industrial age where information is the... Read More
Shareware has been fighting the stigma of being misunderstood for... Read More
If you copy something from a Web site or elsewhere...... Read More
Stealing company information used to be the specialty of spies... Read More
In linux, one of great commands for finding out information... Read More
In 2004 Oracle, Inc. made its new step toward J2EE... Read More
Costs of fleet maintenance software can vary widely. It is... Read More
In our case ? we serve Microsoft Business Solutions ERP... Read More
Which Type of Shop Can Rely On A Home Built... Read More
We are in a transition phase in the Managerial Administration... Read More
CD and DVD replication is a process that works by... Read More
While Adobe is the most known maker of PDF tools,... Read More
Microsoft Business Solutions Great Plains is very good fit for... Read More
Running Applications in Compatibility Mode With Windows XP, you can... Read More
This article is the fourth of a series of articles... Read More
This article is for advanced Microsoft CRM SDK C# developers.... Read More
Bad News - the Threat is Bigger than it SeemedHow... Read More
Imagine something that follows you home and sets itself up... Read More
Siebel is traditional CRM market leader, however and mostly due... Read More
Introduction To ISDN, Part III: Configuring PPP PAP AuthenticationNow we... Read More
DBxtra is a powerful query and reporting tool that hides... Read More
It would be easy to think, like most people apparently... Read More
Spyware is like the new technological nuclear weapon on the... Read More
Software |