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
green cleaning service Park Ridge ..New post-recession era has new features, which didn't exist in... Read More
You turn on your computer, and it doesn't look quite... Read More
Microsoft released Service Pack 2 (SP2) for the Windows XP... Read More
Some companies that are in need of fleet management may... Read More
Security flaws have long plagued Internet Explorer (IE), the market-dominating... Read More
This is the tutorial where we really get into programming.... Read More
An operating system (abbreviated OS) is essentially the path through... Read More
The fact that a software tester is a most infamous... Read More
We were recently faced with a decision: either to let... Read More
If you have Microsoft Great Plains and support it... Read More
At the end of XX century, in the late 1990th... Read More
While Ukraine is becoming a new popular IT outsourcing destination,... Read More
Microsoft CRM is CRM application, maintained and supported by Microsoft... Read More
Your computer cost you from hundreds to thousands of dollars,... Read More
Make-or-Break Factors in Success and ProfitabilityFor quick printers, estimating can... Read More
Microsoft Great Plains is main mid-market application from Microsoft Business... Read More
First we had the original Google search that evolved into... Read More
Every organization which creates collaborative documents, whether they are budgets,... Read More
Microsoft CRM is CRM answer from Microsoft and attempt to... Read More
Simply put, fleet maintenance allows companies to monitor and maintain... Read More
Microsoft Business Solutions Great Plains is marketed for mid-size companies... Read More
Microsoft Great Plains has substantial mid-market share in the USA... Read More
You might think you don't need a firewall... Read More
The purpose of Project Management Software is to provide an... Read More
Now that spyware is the single most dangerous threat to... Read More
emergency cleaning services Morton Grove ..It???s easy to understand why you might be drawn to... Read More
We were recently faced with a decision: either to let... Read More
Adobe Photoshop is one of the world's leading graphics editing... Read More
What is Spyware?Spyware monitors your surfing habits and sends the... Read More
The US House of Representatives has recently passed the "Spy... Read More
Not all of us have the luxury of working both... Read More
Microsoft CRM customization techniques are very diversified and based on... Read More
Configuring PPP PAP AuthenticationNow we know how the ISDN link... 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
Remember old good days when your company probably had Great... Read More
Words we choose to describe things and phenomena often show... Read More
Are you lost in the mess of documents that get... Read More
It's no secret that software companies operate in a very... Read More
Finding the best spyware removers to detect and remove spyware... Read More
Microsoft CRM is CRM answer from Microsoft Business Solutions. If... Read More
Microsoft Great Plains, Navision, Solomon and Axapta are Microsoft Business... Read More
Microsoft Great Plains is one of three Microsoft Business Solutions... Read More
When my daughter was getting into AOL instant messaging (AIM)... Read More
The title of "software engineer" has got to be among... Read More
Microsoft Business Solutions Great Plains, Navision, Axapta, Solomon and CRM... Read More
Just stress testing one of the latest Linux distributions. Been... Read More
Microsoft Business Solutions CRM is web-based CRM application, deploying all... Read More
XML parser is a software module to read documents and... Read More
While I was preparing some personal background information for a... Read More
Software |