Code for previous amount for customer in specific date

Hello
The code below  to display the previous amount for customer in
specific date
Mybe useful for someone



SELECT     SUM(ORTRXAMT) AS EXPR1
FROM         (SELECT     CUSTNMBR, DOCNUMBR, RMDTYPAL, DOCDATE,
                                              ORTRXAMT = CASE RMDTYPAL
WHEN 2 THEN ORTRXAMT * - 1 WHEN 7 THEN ORTRXAMT * - 1 WHEN 8 THEN
ORTRXAMT * - 1 WHEN 9 THEN
                                               ORTRXAMT * - 1 WHEN 1
THEN ORTRXAMT WHEN 3 THEN ORTRXAMT WHEN 4 THEN ORTRXAMT WHEN 5 THEN
ORTRXAMT WHEN 6 THEN
                                               ORTRXAMT END,
DEX_ROW_ID, VOIDSTTS
                        FROM         BSHRA.dbo.RM20101
                        UNION ALL
                        SELECT     CUSTNMBR, DOCNUMBR, RMDTYPAL,
DOCDATE,
                                              CURRTRXAMNT = CASE
RMDTYPAL WHEN 2 THEN ORTRXAMT * - 1 WHEN 7 THEN ORTRXAMT * - 1 WHEN 8
THEN ORTRXAMT * - 1 WHEN
                                               9 THEN ORTRXAMT * - 1
WHEN 1 THEN ORTRXAMT WHEN 3 THEN ORTRXAMT WHEN 4 THEN ORTRXAMT WHEN 5
THEN ORTRXAMT WHEN
                                               6 THEN ORTRXAMT END,
DEX_ROW_ID, VOIDSTTS
                        FROM         BSHRA.dbo.RM30101) View1
WHERE     (VOIDSTTS = 0) AND (DOCDATE < CONVERT(DATETIME, '--PUT THE
CUT DATE HERE--', 102)) AND (LTRIM(RTRIM(CUSTNMBR)) = '-- PUT
CUSTOMER--') AND
                      (DOCNUMBR NOT LIKE ' % RCS % ')
GROUP BY CUSTNMBR

0
info2256 (101)
4/12/2007 6:52:41 AM
greatplains 29623 articles. 2 followers. Follow

0 Replies
223 Views

Similar Articles

[PageSpeed] 41

Reply:

Similar Artilces:

Integrate Paycodes, benefit codes and deduction codes
Has anyone used integration manager to update new pay rates, deduction amounts and benefit amounts for employees? At the beginning of each year, our company gives pay increases and we need to update the pay codes, deduction codes and benefit codes for 40 employees, which we get the information from a spreadsheet. I thought that maybe I could use integration manager to update the pay, benefit and deduction codes instead of going into each employee's card, which is time consuming. Thanks, Laura Integration Manager will allow you to do this. Use the Payroll Master Destination. one ...

wrong system date when install Money
I install trial Money 2004 when the system date was accidentally set to October 2004. After discovering wrong system date (by running Money reports), I corrected it. However, now I cannot use Money. It comes up with error "The system date in your computer is set earlier than the date you first used this trial version of Microsoft Money. Your system date must be set correctly to run this edition. Please fix the date and restart Microsoft Money 2004". Any idea how to solve this problem? Thanks in advance, Alex F Did you try uninstalling the trial edition, fixing the date, t...

zip codes don't merge #2
I am trying to mail merge w/ Word 2000 the names and addresses in my worksheet. When I get to the part to choose the format for the mailing labels, I choose F1, F2, etc. to F6 (which is the zip code column). A few do get there, but the vast majority stop at the state, leaving off the entire zip code. I have gone to menu/format and selected text in the number tab. I have gone to format/cells and chosen special/zip code in the number tab. I've read Excel for Dummies. Please help me. TIA bb ...

custom invoices using sql server reporting services
We have 80 companies and each one wants its own unique invoice. What is the best way to go about this? I already have SSRS setup for other things - can I leverage that for this task. How would I go about doing this? Thanks in advance! GP 2010 has the ability for you to do this, but that won't help you now. There are a couple of options out there - I'm not sure of all of the functions these softwares offer, but I know they solve 'out-facing' document issues: Rockton Software's Dynamics Reports Manager: http://www.rocktonsoftware.com/products.aspx?p=3 Ac...

concatenating two vendor codes
My company recently changed the vendor codes and now I end up with two separate sets of data for each vendor. Obviously I could just leave the vendor code field out. I don't want to do that so what I need to do is concatenate the two codes. Example Vendor Code Vendor Name 123 Joe's Supplies ABC Joe's Supplies what I need is this Vendor Code Vendor Name 123/ABC Joe's Supplies any ideas? Rather than create a new record for each vendor, add another field to the table -- call it NewVendorCode -- and put th...

Can MS EXCEL remove duplicates and separate by color coded items ?
Can MS EXCEL remove duplicates and separate by color coded items ? I am NOT technical and have just started using MS EXCEL. Can anyone PLEASE HELP me: 1. How can I automatically remove duplicates using EXCEL ? ie the same info input more than once on different lines. Can EXCEL do this ? 2, I have color coded the text in the certain lines in terms of priority. can Excel rearrange the data by color ? If yes, How do I do it ? I think I have EXCEL '97 Thanks for your help in advance. Hi 1. You can extract the unique items to a new list using menu Data > Filter > Advanced fil...

from "date" to "text"
Hi there, I have cells formatted as date (e.g. 11/9/03) and I'd like to change the format to text. But when I do it, the values change as well (e.g. "36472"). What can I do to avoid this? Thanks You can't. Dates, to Excel, are the number of days since Jan 1, 1900. That's the what you are seeing when you try to change the format to text. You need to tell us why you want it as text. You have lots of other choices of formats available. If you absolutely have to have text, you will need to create a helper cell, and use a formula like: =text(a1,"m/d...

16 bit code
Hello, I have a 16 bit code to maintain. (It is too complex to copile it to 32 bit) so all we are doing is support. Now they want to add a small feature, where I need to copy long file names in the project. I know we can not do long file name copies in vc++ (1.52). So I wrote a program in VC++(6.0) which does directory copy. My questions are 1. How can I call a 32 bit exe (I guess WinExec should work)? 2. How can I make the 16 bit to wait till the 32 bit code completes? 3. Is there any other better way to do this? (other than converting to 32 bit) Thanks. I actually do this in an old 16-bi...

VBA Code for Pasting Sheets
I would like a spreadhseet that pastes the contents of one sheet into another sheet. I like like to do this for 7 different sheets For example: I would like paste the contents form sheet titled "sheet1" into a sheet titled "data1". Continue to process for pasting "sheet2" into "data2" and "sheet3" into "data3" all way until "sheet7" and "data7". thanks, Curt Subject: Automated Copy Paste Subject: Copy/Paste Import/Export Data VBA Code On Apr 27, 10:49=A0am, Curt <C...@discussions.mi...

money dates
In my reports, the dates are backwards, the most recent date is at top of report and earliest date is at the end, how do I reverse this? Under "view" sort by date etc, does not fix the problem. This never happend in Money 2002, I now have Money 2003. thanks for any help you can give me. sandie In microsoft.public.money, sandie wrote: >In my reports, the dates are backwards, the most recent >date is at top of report and earliest date is at the end, >how do I reverse this? > >Under "view" sort by date etc, does not fix the problem. >This never hap...

area codes is auto filling my own 9 digit ph# vs just area code
When I enter a phone number for a contact it autofills with my personal area code and phone number instead of just the area code. How to I change this to just autofil the area code? I am using Outlook 2007 on an ACER laptop. Make sure you have your area code entered correctly in "Dialing Properties". "Computer Dummy" wrote: > When I enter a phone number for a contact it autofills with my personal area > code and phone number instead of just the area code. How to I change this to > just autofil the area code? I am using Outlook 2007 on an ACER ...

Money 2004 - Experian Offer
I just loaded Money 2004 Deluxe and converted my 2003 files with no problem. I decided to give the free year of credit monitoring from Experian a try but after filling everything out I get an error that the provided code has expired. I neither had nor provided any code for this offer, I only used the link from Money 2004. Now Experian was more than willing to set up a $79 account if I wanted to proceed. Any solution out there? I have the same problem... I am looking for a FREE way to contact Microsoft about this problem. >-----Original Message----- >I just loaded Money 2004 ...

Custom properties
Hi, I have added over 30 Custom Properties to a shape by defining Custom Property sets and then applying the Custom Property set. When I view the Custom Properties for the said shape only 20 of the Custom properties are shown and there is a message in the Custom properties window - 'Note: Additional properties not shown'. How do I view all custom properties without being limited to viewing 20? Thank you The Custom Properties WINDOW is unlimited, but the Custom Propeties DIALOG is limited to 20. You can group your custom properties though, and have page custom properties to co...

Customization of paper size.....
Hi goodmorning Everyone! hope someone can help me...i am trying to figure out if i can customize the paper size options in the page set up? is it possible? I am working right now with a continous paper with 5 x 8 inches measurement (portrait orientation)....thanks in advance!!! jerome This really depends on what paper sizes your printer (i.e. printer driver) supports - ensure that you have the latest driver for your printer. Hope this helps. Pete jerome.tamayo@gmail.com wrote: > Hi goodmorning Everyone! hope someone can help me...i am trying to > figure out if i can customize the p...

previous email
Read some more responses and set account to requires athentification and it seems to work now. No need to respond to earlier email thanks! OK, but please always respond in the same Subject thread you started. Gary VanderMolen, Microsoft MVP (Mail) ------------------------------------------------------ "larame" wrote in message = news:96018FB3-01EE-4AE5-BAD9-401A82169E88@microsoft.com... Read some more responses and set account to requires athentification and = it seems to work now. No need to respond to earlier email thanks!=20 ...

Using date function with inline prompt
I am using the Now() function and trying to use a variable input to go back a couple of days for checking dates. In the Date Column, I am using >Date() - 3 to go back 3 days as a hard code. When I change it to a inline prompt it does not work ie. > Date() - [Enter number of days before now]. How best can I get this to work? My first choice would be to use the DateAdd function > DateAdd("d",-[Enter number of days before now],Date()) Or force the parameter value type > Date() - CInt([Enter number of days before now]) John Spencer Access MVP 2002-2...

Crystal Report Error
Hi, We have a custom quote report that is triggered from a button. When a user selects the Print Quote Button on the quote page, the quoteid is passed into a custom report we created and the quote displays. For some reason, this report is stopped working. All others are still working. When we try to run the report, we receive an erroneous error: Page header or footer longer than a page..... I opened the report in Crystal Report designer and was able to run it with no error.There are references to print driver issues on other Web sites. Has anyone run into this issue and if so,...

MC/VISA joint code
Isn't there any way to combine the blocks (4* & 5*) for MC/VISA credit cards? It's a pain to have them separate because my bank posts them together. It is the same company afterall. It would save me a lot of time adding separate entries together to reconcile the statement! I do not know of a way to do what you are asking, but I have found that using the # symbol as follows works better than using the * symbol: 4############### 5############### If you use the * symbol it is a wild card with no determined length. The # symbol requires that the characters after the first d...

Analytical Accounting
Hi, One of my Client's is considering to go down AA path with approx 300,000 AA dimension codes. I would like to hear whether such data set is manageable from Query wizard etc. appreciate your thoughts. Good Morning PR, I found that the issue isn't the number of AA Trx Dim Codes it is the size of the AAG30000 and AAG40000 tables the more entries the longer it takes to product the report. I had one client that started having a cached SSRS report with AA information as a management level report and a different SSRS report with a more restrictive dataset for reporting ...

Customize Lead Phone Call Activity form and Add New Activities
Help Please??? I have a new MS-CRM implementation and I have some questions on customizing the activity forms and the types of activities available: 1. How do I customize the Lead Phone Call Activity form to add fields like Company name and City, etc.? Shouldn't this be able to be done with the built-in form editor? Since it seems that phone call activities have an object code of "137" (got this by digging through the Form Editor’s default XML files), I tried manually going directly to: http://crm/tools/formEditor/formEditor.aspx?objectTypeCode=137 but the form that app...

Getting a specific parent of any node via xpath navigation
I am storing my site structure as such: <menuItems> <menuItem> <pageID>1</pageID> </menuItem> <menuItem> <pageID>2</pageID> <menuItem> <pageID>4</pageID> <menuItem> <pageID>5</pageID> <menuItem> <pageID>6</pageID> </menuItem> </menuItem> </menuItem> </menuItem> <menuItem> <pageID>3</pageID> ...

Need advice on setting up a test server/customization platform...
I'm in the process of setting up a dedcated machine (SBS 2K3 with all the bells and whistles including SQL 2005) to handle testing and customization of GP 9.0 systems. Since customizing GP seems to leave a pretty nasty mess behind. (importing cnk files, vba and so on) plus different clients have different 3rd party add-ins with separate registration keys, I am looking for advice ont the best way to go about doing this. What I want is a fairly straightforward way of duplicating the GP environment of each client so if they require maintenance work or new customizations I can just lo...

Change code with code?
Hi All..... I've got 31 Excel .xlsm files to modify the "Change Event" macro in. Is it possible to open and perform this with code, or must I do each one by hand? TIA Vaya con Dios, Chuck, CABGx3 If it is the same change in each sheet, it would probably be just as easy to go into the VBE, double click on the first sheet, make the correction, then copy that correction and double click the second sheet, delete and paste. Repeat the process 30 times. Takes less time than writing the code to do it. "CLR" <CLR@discussions.microsoft.com> wr...

Customization Report
How can I find: (i) The customizations that I haven't published yet? (ii) The cutsomizations that I have alread published and when? ...

How To Match Dates
On worksheet1, I have a several columns (A-M) one of which (colA) is the daily date in the format of 21-Nov-2004 so by the end of a year there will be approx 365 rows. On worksheet2, there is a single row monthly summary of the above data (so sheet1 has approx 30 rows per month while worksheet2 has a one row summary) where the date on this second worksheet is headed as Month Begining (A1) 01-Nov-2004, (A2) 01-Dec-2004, (A3) 01-Jan-2005 I now need reference a date from sheet1 to the summary on sheet2, so if I input 12-Jan-2005 on sheet1, I need to have the monthly summary row from she...