| |
| |
|
softwarecatch.com
|
|
|
|
Microsoft Great Plains: Interest Calculation Example - Stored Procedure for Crystal Report
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 MORE RESOURCES:
Google News |
|
|
|
RELATED ARTICLES
Chinese Input - Step by Step Instruction on How to Input Chinese Characters in English Windows XP Enabling Chinese input is quick and easy, there are only a few steps you need go through to install the Chinese Language support and input method before you can type Chinese in your word processor or other program.We have broken the steps to 3 different sections for your easier to read and follow. Know-how in Microsoft Publisher For those who still don't know, Microsoft Publisher helps computer users to easily create, customize and publish materials such as flyers, brochures, catalogs, newsletters and websites. It provides tools to create professional and compelling marketing materials that will help you take your business further. Microsoft Business Solutions VAR/Partner Selection - Overview for IT Director/Manager/Controller Microsoft Great Plains and Microsoft CRM become more and more popular, partly because of Microsoft muscles behind them. Now Great Plains is targeted to the whole spectrum of horizontal and vertical market clientele. Reloading Windows XP If you have been running Windows XP for a couple of years or more you may find that it is not running quite as quickly and smoothly as it was when you did your first install. I am constantly 'evaluating' software and uninstalling and reinstalling beta software on my computer and have always gradually become more and more disappointed after nine months to a year with the performance of my PC. Corporate ERP Selection: Microsoft Great Plains In our opinion, traditional approach when you select ERP/MRP system for large or mid-size company by functionality, database and hardware platform should be revised. We are convinced that new generation of Windows servers plus the reliability of relatively inexpensive database platform, such as Microsoft SQL Server, enable large corporation to decrease the cost of hardware, database and ERM software, plus have good internal support by hiring Microsoft certified professionals, available on the market in your area. Microsoft Great Plains - Payroll & HR Inexpensive Solution? Not Any More Microsoft Great Plains is main Microsoft Business Solutions accounting package for the US market. You should probably expect some issues when you have software development company to be bought by big player - in this case Great Plains Software was bought by Microsoft (Bill Gates was a friend to Doug Burgum - owner and leader of Great Plains Software - Great Plains Dynamics/eEnterprise). Rapid Application Development - Is it Really a Need of Today? Rapid Application Development (RAD) is a software development methodology. In this competitive world, software product delivery time should be as little as it can be. MySQL for Beginners - How to Create a MySQL Database Whether you are an experienced web programmer or a complete novice attempting to provide data interactivity with your web site, MyQSL is an easy to use and free database solution that can allow you to store and configure data to be displayed on your web site.The best way to create and manage a MySQL database is to download an open source (free) program called PhpMyAdmin. Database Guru James F. Koopmann Reviews DBxtra Reporting and Query Tool DBxtra is a powerful query and reporting tool that hides many of the complexity and technical expertise required for creating connections to disperse data sources, building queries, designing reports, and delivery information throughout an enterprise or to your customer base.With the ability to connect to multiple databases or data sources at the same time, DBxtra allows for cross platform data aggregation and the ability to answer questions that would typically take IT time to migrate data into a single reporting database. 7 Things to Consider Before Buying Small Business Accounting Software The world of small business accounting software can be a minefield for any business owner. However choosing the right package is one of the most critical business decisions you will make. 20 Extra Hours Per Week: What Would You Do? While I was in college, if you would have asked me what I would do if I had an extra 20 hours to spend in a week, the first thing I would have told you is that I would definitely NOT spend it studying. I actually would have gone down a long list of hobbies and activities I would like to do if I had the time. The Truth: Netzero 3G We've all seen the ads on TV for Netzero 3G. You know the ones, "speeds so fast you sworn it was broadband" Well if your using it, you may not think it really is. Does your Company have Documentum? Are you lost in the mess of documents that get passed around your company, never knowing what the latest version is and which one you should work on without worrying if someone else has already made the same editions that you are making? Perhaps you have heard of collaboration software solutions such as Documentum to help your company manage its documents that are passed around. Documentum is a very good solution to this problem but is it the only one? In this article you will be presented with some basic information about the differences in collaboration software from Documentum and NextPage. FreeDOS Before September 1995, Microsoft Windows was an MS-DOS program. DOS was an easy to use command line operating system that provided you with complete ability to control and troubleshoot your computer. Microsoft Great Plains Partner Selection: Overview Microsoft Great Plains, Navision, Solomon and Axapta are Microsoft Business Solutions ERP applications, serving to the whole spectrum of vertical and horizontal markets. Assuming that you already have one of these systems installed and implemented we would like to give you - IT director, Database Administrator, Programmer, Software Developer, Controller your options in supporting/upgrading Microsoft Great Plains for your companyIt is always good idea to install everything on your own - however Great Plains requires combination of computer networking, some programming, good accounting/distribution/logistics/reporting background - so it is not a surprise that Microsoft Business Solutions requires their customers to have specially assigner Microsoft Great Plains Partner/VAR/Reseller to serve the account. Two Reasons to Use Timesheet Software This short paper will expand on two key reasons to move to a computerized timesheet system at your company. There are more than two reasons of course, but these will be expanded on at a later date. Tools for Customizing Great Plains Microsoft Business Solutions - Great Plains has captured the US market with its integrated business applications for small and mid-size organizations. Its comprehensive accounting and business management capabilities provide businesses with tools to customize various modules of the Great Plains software. 10 Things You Can Do With Photoshop CS2 That You Couldnt Do Before Now Is Photoshop CS2 worth the upgrade? You bet it is! Here are just 10 things you can do with this fantastic new version of Photoshop that were impossible (or difficult) to do with previous versions:1. Manipulate objects in the Adobe BridgeThis new generation of file browser enables you to look at photographs and artwork in detail before you open the file, process multiple Camera Raw images, flip through pages in PDF documents, size, rate and organise your work in a convenient and highly user-friendly way. The Religion And Philosophy Of Small Internet Business I have always had a tendency to focus on the positive. We, all of us, know the negative details in society and governments. What is Shareware? Shareware is software that you can try before you buy; shareware is a kind of marketing method for software. Software developers post trial versions of their software on websites.
|
|
Powered By: Free Work At Home Business Opportunity! |
| © 2006 |
