June 9, 2011 at 2:49 pm
I am trying to create a view/select certain rows. I am only using one table, AR_INVOICE, to create the view.
AR_INVOICE table has multiple entries for debit and credit amounts. I am only concerned with the debit amount. The debit amount is posted for/under one [PERSON_ID], the class a person is taking (column name [COURSE]), and date.
There are 18 columns in this table and I need to select all of them, but for filtering I am only concerned with 4 columns: [PERSON_ID], [COURSE], [DATE], [DEBIT_AMT]
I want to:
1. Exclude any rows where [DESCRIPTION] IS NULL
2. for each [PERSON_ID], it will pull the most recent [DEBIT_AMT] for a particular [COURSE]. If the [DEBIT_AMT] = 0 then go to next recent date and display that [DEBIT_AMT]
3. One [PERSON_ID] may have multiple entries of the same [COURSE]. And one [COURSE] may appear in multiple [PERSON_ID].
This has been boggling my mind for past 24 hours. Please help.
------------
🙂
June 9, 2011 at 6:29 pm
To receive tested help please post table definition, including indexes, along with sample data and desired results. You can do this simply if you read the article whose link is the first link in my signature block.
June 10, 2011 at 4:18 am
Also, what have you tried so far?
This sounds like a programming class question.
June 10, 2011 at 12:36 pm
Please see attached file. I have included code to create the table and insert the sample data as asked.
What I am looking for:
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
above code weeds out some of the stuff. But I only want to pull in rows with most recent date for each class "DESCRIPTION_LINE_2". If there are more than two entires for a class on same date, then just pull in one.
Any ideas?
------------
🙂
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply