June 10, 2011 at 2:10 pm
Hello all,
I've researched a lot but can't figure out a proper way to do this. Please see attached file for code to generate a test table and some sample data to load into it.
First of all, there are two conditions below that i need to meet.
Select * from dbo.invoice_items
where AR_CATEGORY = 'TUIT' and INVI_EXT_CHARGE_AMT > 0
order by INV_PERSON_ID, DESCRIPTION_LINE_2, INV_DATE
Now comes the programming part. I only want to select the Rows with latest date '[INV_DATE]' for each '[DESCRIPTION_LINE_2]' under each '[INV_PERSON_ID]'. I guess it will first be grouped by [INV_PERSON_ID] and then by [DESCRIPTION_LINE_2].
I'm sure it's pretty simple and i'm gonna feel like a dumbass, but i'm stumped.
What will be a proper way to do this?
------------
🙂
June 10, 2011 at 2:40 pm
I wrote an article outlining several ways to get versioned data. Latest date is not any different than latest version. Take a look at this[/url] and post back here if you're still hitting issues.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 10, 2011 at 2:45 pm
Here is another hint:
http://www.sqlservercentral.com/Forums/Topic1123031-149-1.aspx
For additional help please post what you have tried so far.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 10, 2011 at 2:45 pm
It's not clear what order you want to apply your criteria, and it will affect the exact query used.
When you have multiple records, do you want to find the last one and then determine whether the amount is greater than zero or do you want to find all the records where the amount is greater than zero and then find the last one of that group?
There are basically three approaches that you can use. Which one is best will depend on a number of things including indexes and the average number of records in each group.
1) Use a CTE to calculate the Max(date) grouped by your person/item and join that to your main query on the person/item and date.
2) Use a CROSS APPLY that calculates the Max(date) for records that match on the person/item
3) Use a CTE to calculate the Row_Number() partitioned by the person/item and ordered by date DESC, and then select only the first row for each partition.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply