Max(date) problem

[Susan Murray]

Hello,
I am stuck trying to select records from a view. I need max(date) to select the topmost date, but I also need to select records based on date criteria.

SELECT MAX(rc_date) AS rc_date, SUM(rc_amount) AS rc_amount, rc_method, p_p_acno AS CustomerNumber
FROM View_ReceivingReport
GROUP BY rc_method, p_p_acno, pc_name
HAVING (rc_method = 'CREDITCARD') AND (pc_name <> '') AND (dbo.GetDelimitedFieldRight(pc_name) = 'PST')
ORDER BY pc_name
 
2010-01-20 6000.00 Showname1 CREDITCARD Customer1 Event1
2010-01-18 796.00 Showname2 CREDITCARD Customer2 Event2

This gives the max date of all records in the view, but how do I select a date range while keeping single max(rc_date) and grouping?

Thank you

[ck9663]

Where do you need the date range? Could you give some sample data and how you want your result to look like?

~~ CK

[Susan Murray]

I solved it by selecting all records by date into a temp table, then applying the query above. Thanks!

[NeoPa]

That would be your WHERE clause Susan.

GROUP BY queries can have WHERE, as well as HAVING, clauses. The WHERE clause applies its filtering to the incoming records (IE. Before any grouping) while the HAVING clause applies its filtering to the processed/outgoing records.

Welcome to Bytes!

[Susan Murray]

Thanks, but I obviously had used the Where clause without the result I wanted.

[NeoPa]

I'm struggling to see obvious Susan. It wasn't included in the post.

[Susan Murray]

I didn't mean to offend, problem is solved.