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
Airbnb cleaning service Glenview ..Microsoft Business Solutions Great Plains has full-featured manufacturing set of... Read More
In the real world a "fire wall" is a fireproof... Read More
Linux essentials:It's free for download but you have to pay... Read More
As seeing large number of implementations ? in our case... Read More
In the Clinton era the status quo was simple: you... Read More
How do you run a program on a remote server... Read More
Need software to record your voice, streaming audio or musical... Read More
If you have been running Windows XP for a couple... Read More
Microsoft Great Plains is now targeting large and midsize businesses... Read More
The various resume software offered, particularly on the internet, can... Read More
If your company has regional and worldwide operations, you might... Read More
Every day millions of new web documents emerge on the... Read More
At the end of XX century, in the late 1990th... Read More
If you look back to the history, you will see... Read More
Think of this, first we had the HAM Radio, then... Read More
Microsoft PowerPoint has dramatically changed the way in which academic... Read More
Microsoft Business Solutions Great Plains, former Great Plains Software Dynamics... Read More
Spyware, what it is and what it does. Basically, spyware... Read More
Are Spreadsheets Robbing your Enterprise of Competitive Advantage?'90% of "average"... Read More
I completed an experiment recently. I wanted to find out... Read More
IntroductionSurprised, by the title? well, this is a tour of... Read More
Disclaimer: All the thoughts expressed are my views only! Your... Read More
It's not very often I get excited about a software... Read More
As we could imagine, if you are reading this article... Read More
Microsoft Office program is a programming tool called Visual Basic... Read More
efficient cleaning crew Highland Park ..Need help making sense of algebra? Have algebra lectures in... Read More
I started using PIP (Picture It Publishing) Platinum 2002 right... Read More
Considering whether or not your software company should hire a... Read More
The US House of Representatives has recently passed the "Spy... Read More
ERP is the acronym of Enterprise Resource Planning. Multi-module ERP... Read More
You've finally created databases that you can actually use to... Read More
Not all of us have the luxury of working both... Read More
DBxtra is a powerful query and reporting tool that hides... Read More
We don't think about mainframe software pricing anymore, we just... Read More
Shareware is software that you can try before you buy;... Read More
Well, even if the combination might look very unusual, we... Read More
C/SIDE (Client/Server Integrated Development Environment) - The core of... Read More
Microsoft Business Solutions ? Navision is an integrated solution for... Read More
Lotus Notes Domino is very efficient in electronic document workflow... Read More
MS CRM is very close to document workflow automation, including... Read More
Security flaws have long plagued Internet Explorer (IE), the market-dominating... Read More
The stakes are high when considering security, privacy, and savings,... Read More
Microsoft Business Solutions Great Plains has Project Accounting module where... Read More
No matter how much you enjoy your favorite screensavers, sometimes... Read More
I have always regretted how Microsoft price gouges and rips... Read More
C++ Function templates are those functions which can handle different... Read More
How to delete the user? This is the first problem... Read More
What is Spyware?Spyware monitors your surfing habits and sends the... Read More
Microsoft Great Plains as ERP and Microsoft CRM as... Read More
Designing without tables by using CSS layouts is fast becoming... Read More
Software |