ALL >> Computers >> View Article
Microsoft Great Plains: Interest Calculation Example - Store

This is intermediate level SQL scripting article for DB Administrator, Programmer, IT SpecialistOur 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--, asdeclare @char39 char --for single quote markdeclare @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_TABLEselectyear(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_TABLEfrom(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.PERDBLNCend 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
Add Comment
Computers Articles
1. React Vs Angular: Which Should You Use To Build Your Startup In 2022?Author: goodcoders
2. Php Vs Java: Differences & Similarities For Web Development
Author: goodcoders
3. Most Common Mistakes When Developing A Mobile App
Author: goodcoders
4. Hvac Market To Soar To $456.6 Billion By 2032: Growth, Trends & Innovations
Author: Rutuja kadam
5. Sports Analytics Market To Hit $22.2 Billion By 2032: A Game-changer For The Industry!
Author: Rutuja kadam
6. Bluetooth Printer: Efficiency & Portability Combined For Your Business
Author: srikanth
7. Mmoexp Cfb 25 Coins:master The Basics To Build Confidence
Author: kyw
8. Top 5 Marketing Resource Management Software Of 2025
Author: Ben Gross
9. Reliable Logistics Company In Saudi Arabia: Is Your Trusted Supply Chain Partner?
Author: IAP Logistics
10. The Rise Of Digital Marketing In Bangalore: Your Ultimate Guide To Choosing The Right Digital Marketing Agency
Author: Cubikeymedia
11. How To Select The Right Nema Power Cord For Your Needs
Author: Jennifer Truong
12. Top 5 Sales Intelligence Software Of 2025
Author: Ben Gross
13. Seo Company In India Increases The Fame Of This Country
Author: SEO Company Kolkata
14. Top 5 Marketing Intelligence Software Of 2025
Author: Ben Gross
15. Why Web Content Management Software Is Essential For Digital Growth In 2025
Author: dnd teams