May 25, 2012 at 6:57 am
Hi,
What would you suggest for a formula to group a column of figures into 3 (or more) coumns according to the due date - this is for an Aged Creditors report from a ERP database.
The due dates would be 0 to 30 days, 31 to 60 days, 61 to 90 days and geater than 3 months. There can be future dates as well as past dates.
I was hoping to use this:
IF {RDH_Detailed_Aged_Creditors.Due Date}>dateadd('d',0,currentdate)
OR {RDH_Detailed_Aged_Creditors.Due Date}>dateadd('d',-30,currentdate)
THEN {RDH_Detailed_Aged_Creditors.O/S Value}
ELSE 0
But I get some duplication in the columns, so for example 28/12/2012 produces two results, one in the 1to30 column and again in the 30to60 column.
Cheers
Robert
May 25, 2012 at 7:48 am
{RDH_Detailed_Aged_Creditors.Due Date}>dateadd('d',0,currentdate)
OR {RDH_Detailed_Aged_Creditors.Due Date}>dateadd('d',-30,currentdate)
I am not sure what you are trying to achieve .But above OR is redudant..
GulliMeel
Finding top n Worst Performing queries[/url]
Improve the performance of Merge Join(special case)
How to Post Performance Problem -Gail Shaw[/url]
May 25, 2012 at 8:52 am
Oh, OK.
I am planning to drop an amount figure into one of three columns based on the due date in that record, so the 'ageing' is reported for amounts due in 1 month, then 1 to 2 months, then 2 to 3 months and also anything greater than that.
The records can be any date from 14 Jun 07 to 31 Dec 12
May 25, 2012 at 8:55 am
If you want some real help read the first link in my signature. You need to provide a lot more details about what you are trying to do before anybody can help.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply