Help grouping results in date ranges

Question

Help grouping results in date ranges

mquinn-496424

SSC Rookie

Points: 30
More actions
May 1, 2008 at 1:25 pm

#190149

I have invoices for projects and need to sort invoices that are outstanding by how late they are. I am calculating invoice amount - amount paid to give total amount owed. I have invoice date for all invoices to determine their age. I am currently calculating the invoice age by subtracting invoice date from data extract date to give me an invoice age, which is correct. Problem is that for some projects i have multiple invoices outstanding and need to categorize them by 0-30, 31-60, 61-90, 91-120, and 120+ days late. I've tried a table and text boxes with Iif statements calculating the sum of amount owed if the age is within the specified range. If I have 1 invoice it works fine, if I have two for a project it makes 1 pass for each invoice and although it enters the amount owed in the correct date range it goes to a second line. I'm trying many different ways to get around this problem, but thought I'd throw it out there to see if anyone could give me some ideas. Thanks.

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004646 More actions · Answer 1

Obviously, you'll need to group by something else other than the invoice number... can't you group by the customer number of something similar that spans invoices?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

mquinn-496424 SSC Rookie Points: 30 More actions · Answer 2

I'm grouping the page by project manager for them to see their outstanding invoices, then grouping by project so they can see total amount owed for each project, but then need another level of grouping to show how much of that total owed is 30 days old or less, 31-60, 61-90...

Jeff Moden SSC Guru Points: 1004646 More actions · Answer 3

Obviously, I don't have your data nor the query that calculates the age of the invoices, but if you use the output of that query as a derived table in the following (you may have to change a column name here and there), you should be able to get what you want. Lookup "Cube Operator" in Books Online and study the "Grouping" part to do some tricks with automatically changing things like the ProjectName to the word "Subtotal" when it needs to be.

SELECT ProjectManager,

ProjectName,

SUM(CASE WHEN InvoiceAge BETWEEN 0 AND 30 THEN InvoiceAmt ELSE 0) END AS [0-30],

SUM(CASE WHEN InvoiceAge BETWEEN 31 AND 60 THEN InvoiceAmt ELSE 0) END AS [31-60],

SUM(CASE WHEN InvoiceAge BETWEEN 61 AND 90 THEN InvoiceAmt ELSE 0) END AS [61-90],

SUM(CASE WHEN InvoiceAge BETWEEN 91 AND 120 THEN InvoiceAmt ELSE 0) END AS [91-120],

SUM(CASE WHEN InvoiceAge > 120 THEN InvoiceAmt ELSE 0) END AS [120+]

FROM yourtableorderivedtable

GROUP BY ProjectManager,ProjectName WITH ROLLUP

Correctly indexed, these types of "Cross Tab" queries absolutely fly.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

mquinn-496424 SSC Rookie Points: 30 More actions · Answer 4

Thanks for your help, I'm using SSRS, so I just took your idea and applied it to calculated fields in my dataset. I have 1 calculated field for each category then grouping my table by project and page by project manager. It's working fine now. Thanks again.