CATEGORIES Auto & Trucks Business & Finance Computers & Internet Education Family Food & Drink Gadgets and Gizmos Health Hobbies Home Improvement Humor Kids & Teens Legal Marketing Men Music and Movies Online Business Parenting Pets and Animals Politics and Government Recreation & Sports Relationships Religion and Faith Self Improvement Site Promotion Travel & Leisure Web Development Women Writing Keyword Search Article Title Author's Name SITE MENU Search Submit An Article Submit Articles in Bulk Submit Press Release Syndicate Articles Distribute Articles Privacy Policy Terms of Use Link To Us Contact Us Site Map Home

Microsoft Great Plains: Interest Calculation Example – stored procedure for Crystal Report  by: Andrew Karasev 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<="+@char39+@Accountto+@char39 +" group by a.ACTNUMST, case when b.PERDBLNC is null then 0 else b.PERDBLNC end, d.USERDEF1 ) X ") Happy querying and customizing! if you want us to help you - give us a call 1-866-528-0577! help@albaspectrum.com About The Author Andrew Karasev is Chief Technology Officer in Alba Spectrum Technologies – USA nationwide Microsoft CRM, Microsoft Great Plains customization company, based in Chicago, Arizona, California, Colorado, Texas, New York, Georgia, Florida, Canada, UK, Australia and having locations in multiple states and internationally , he is Dexterity, SQL, C#.Net, Crystal Reports and Microsoft CRM SDK developer akarasev@albaspectrum.com This article was posted on October 03, 2004 Read More Articles from the "Computers and Internet" Category: 10 Ways To Save Time And Money With Packing And Shipping On Ebay by Dave Bromley What Is A VoIP Router? by Laura Rupert Rid Your Computer of ITD - Internet Transmitted Diseases by Bruce Cramer SAP Business One vs. Microsoft Dynamics GP – highlights for consultant by Andrew Karasev Avoiding Stress During the Busy Holiday Shopping Season with eCardica.com by Amy Pilkington The Unavoidable Spyware Threats by Mitch Johnson Issues with implementing Software Quality Assurance (SQA) by Ian Fleming Lotus Domino/Notes - Microsoft Great Plains tandem as ERP with Documents Workflow - overview for IT Specialist by Rafel Osipov RSS Feed Tools by Jack Doren Are You Protecting Your Computer? by Alton Hargrave << Back to "Computers And Internet" Index

Disclaimer: The information presented and opinions expressed herein are those of the authors
and do not necessarily represent the views of ArticleCity.com and/or its partners.

	Web www.articlecity.com