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
expert residential cleaners Park Ridge ..There is many things more frustrating than surfing a website... Read More
How many steps does it take you to locate and... Read More
Microsoft CRM customization techniques are very diversified and based on... Read More
C++ Function templates are those functions which can handle different... Read More
Introduction To ISDN, Part III: Configuring PPP PAP AuthenticationNow we... Read More
XML parser is a software module to read documents and... Read More
Microsoft Business Solutions Great Plains, Navision, Axapta, Solomon and CRM... Read More
What is a Web Database?A web database is a database... Read More
Now there are Three Steps To Heaven Just listen and... Read More
The vast majority of us will have, at some point,... Read More
Mike Dunville* had a decision to make. As the new... Read More
Enabling Chinese input is quick and easy, there are only... Read More
Buying accounting software is a major investment. It's an important... Read More
When my daughter was getting into AOL instant messaging (AIM)... Read More
2005 ? Back to the Future.What does the future hold?... Read More
Disclaimer: All the thoughts expressed are my views only! Your... Read More
Midsize business or non-profit organization should decide if one-vendor solution... Read More
Microsoft Great Plains is main Microsoft Business Solutions accounting package... Read More
Best Software Act! is very popular CRM for small and... Read More
Icons are used everywhere; right from software applications, to internet... Read More
MicroWorld Technologies, Inc. the leading solutions provider in the area... Read More
Intro This concise article will tell you in plain English... Read More
Music downloads are off the charts! We're listening to digital... Read More
Stop the Runaway MouseWhat's the "runaway mouse?" You've seen it...you... Read More
There are certain pluses and minuses in both cases and... Read More
green cleaning service Park Ridge ..Finally, you have some time to personalize your desktop with... Read More
OEComplete is a utility for managing the personal information of... Read More
Should one use Windows Update?This topic has good and valid... Read More
Whether you are an experienced web programmer or a complete... Read More
We will base our prognosis on our Microsoft Business Solutions... Read More
In our case ? we serve Microsoft Business Solutions ERP... Read More
The most important benefit of XML is its simplicity. Though... Read More
IntroductionDuring the early years of our modern computer era, very... Read More
1. With mapping software you can create a report that... Read More
Microsoft Business Solutions Great Plains has substantial market share and... Read More
If you are to buy a HelpDesk & Asset Management... Read More
Pirated software is on the increase and now accounts for... Read More
When Great Plains Software was designing and developing Great Plains... Read More
Microsoft Business Solutions Great Plains is very good fit for... Read More
What is RAID RECOVERY?RAID stands for Redundant Array of Inexpensive... Read More
Looks like Microsoft Great Plains becomes more and more popular,... Read More
The Windows Indexing Service provides you with the ability to... Read More
What is Tripwire?Tripwire is a form intrusion detection system (IDS)... Read More
.NET platform does not support multiple inheritance. Do not confuse... Read More
At the end of XX century, in the late 1990th... Read More
When Great Plains Software introduced the first graphical accounting application... Read More
Have you noticed WordPerfect is gearing up for a comeback... Read More
Is your PC is slow and wimpy? Then you need... Read More
Document Management or Enterprise Information Management is perhaps one of... Read More
Navision Software was purchased by Microsoft and now it is... Read More
Software |