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
Culver prom limo ..Spyware is software or hardware installed on a computer without... Read More
Make-or-Break Factors in Success and ProfitabilityFor quick printers, estimating can... Read More
The research in the field of Natural Language Processing usually... Read More
Let us give you - developer some hints in the... Read More
Sticky Noteshttp://www.deprice.com/stickynote.htmWith StickyNote 9.0, you can create beautiful 3D notes... Read More
Fundraising software lets you connect with donors in a way... Read More
A UNIX Shell is in simplest terms, a command line... Read More
SAP Inc., a global leader in client/server enterprise application software... Read More
Microsoft Business Solutions CRM is now approaching the phase of... Read More
This article is for advanced Microsoft CRM SDK C# developers.... Read More
The Internet is reshaping every form of communications medium, and... Read More
In the real world a "fire wall" is a fireproof... Read More
Microsoft CRM is CRM answer from Microsoft and attempt to... Read More
Follow the steps below to quickly design, generate, and deploy... Read More
Microsoft CRM has built-in conversion tool, however you should probably... Read More
Navision Software was purchased by Microsoft and now it is... Read More
TikiWiki is open source software - it is written in... Read More
Program Flow is what you think it is. How the... Read More
eStore Advantage allows front-office applications to communicate with back-office business... Read More
This article is the fourth of a series of articles... Read More
The destruction of the Soviet Union about 15 years ago,... Read More
When you double-click a layer in the Layer Palette, you... Read More
Microsoft Great Plains and Microsoft CRM become more and more... Read More
If you have been using the Internet for any amount... Read More
The objective for Zandi Digital is to make available clever... Read More
Wood Dale limo ..In our small article we'll consider Microsoft Business Solutions Great... Read More
Microsoft Business Solutions CRM data conversion deserves FAQ type of... Read More
Vince Lombardi once said that, "The achievements of an organization... Read More
Microsoft CRM is CRM answer from Microsoft and attempt to... Read More
With the advent of 'Service Pack 2' for Windows XP... Read More
Microsoft Business Solutions is emerging as very attractive vendor for... Read More
If you are to buy a HelpDesk & Asset Management... Read More
First we had the original Google search that evolved into... Read More
What is IRC?IRC is Internet Relay Chat. It is a... Read More
It is possible that if one avoided all sources of... Read More
Spyware and malware are large problems for Internet users today... Read More
An integral part of any quality CRM system is lead... Read More
I suggest that you do not spend a lot of... Read More
Microsoft Business Solutions Great Plains is mid and even corporate... Read More
Microsoft Business Solutions Great Plains is Dexterity-written application and currently... Read More
In our case ? we serve Microsoft Business Solutions ERP... Read More
After seeing many people complain about their weak Internet security... Read More
In the previous ISDN article, we looked at how and... Read More
Each Industry and market niche has business specific and unique... Read More
A wiki is an editable text-based website. But you don't... Read More
Crystal Reports is the most flexible tool on the market... Read More
Microsoft Great Plains is one of the Microsoft Business Solutions... Read More
I've been active in the Cisco Certification track for four... Read More
When my daughter was getting into AOL instant messaging (AIM)... Read More
Microsoft Great Plains is now targeting large and midsize businesses... Read More
Software |