Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
grrr223
Starting Member
30 Posts |
 Posted - 2004-03-01 : 14:25:39
|
I am working on the record source for a report to produce monthly customer statements. Thanks to everyone here, I have been able to overcome many, many hurdles I have encountered. I just have one last issue I need to get resolved (famous last words, I know). At the heart of my record source are two entities:1. AROPNFIL - A table of all Accounts Receivable items (invoices, payments, credits, etc.)2. fnBalance(@StartDate) - A table valued function that gives me the starting balance for a customer on a particular date by summing the net Amounts of all items in the AROPNFIL that occured before the @startdate. (i.e. Invoices have a positive amount, payments have a negative amount, so they net out so that only unpaid invoices remain.)These are joined on customer_number. The query also selects only the rows in the AROPNFIL table that are between @StartDate and @EndDate for the month. Everything was going great until...I realized that no statement was being created for a customer if they didn't have any activity in the current month, even though they had a starting balance.So I tried an outer join, telling the query to select all rows from the fnBalance table. But that still wasn't returning the rows I wanted. After several hours of cursing and feeling the need for a drink, I realized why that wasn't working. Because the customer in question, let's call it "Coast01" had rows in the AROPNFIL table before the @StartDate, it was seeing that as having completed the join and hence no reason to return a row for that starting balance.So, this is what I need, a way to write what I am going to try to interpret as the following.Select *From (AROPNFIL WHERE Doc_date BETWEEN @StartDate AND @EndDate) RIGHT OUTER JOIN fnBalance(@StartDate) Does that make sense? I need the join with fnBalance to take place after the query has selected only the rows between @startdate and @enddate so that the row for the balance for "Coast01" will appear even though there is no activity in the current month.But I am at a loss for how to write that in the FROM and WHERE clauses. I guess one way would be to create a query selecting rows from AROPNFIL WHERE doc_date BETWEEN @StartDate AND @EndDate and then create another query performing the outer join between that query and fnBalance, but I don't really want to do that because the rest of my record source query is actually a lot more complicated than what I've explained here, and I'd rather not have to create additional queries if I don't have to. But if you guys tell me there is no other way, I'll believe you.Thank you all! |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2004-03-01 : 14:34:39
|
| What is the result set suppose to look like?I use SELECT * alot when I interogate data...Get used to not using when you're building stuff...And it would help use to see the data...I'm afraid however ther's no miracleIf they don't have the data they don't have it....You could create a table of dates and left join from that to the customer table...and for the dates where there is anything, you'd get nullBrett8-) |
 |
|
|
grrr223
Starting Member
30 Posts |
Posted - 2004-03-01 : 14:44:28
|
Actually, I think I'm on the right path here using a derived table, please correct me if I'm wrong:SELECT *FROM (SELECT * FROM AROPNFIL WHERE Doc_date BETWEEN @StartDate AND @EndDate) RIGHT OUTER JOIN fnBalance(@StartDate) StartBalance ON AROPNFIL.cus_no = StartBalance.cus_no Obviously, I'll get rid of the *s, but no one here cares about what columns I'm actually pulling out. |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2004-03-01 : 14:47:12
|
| you haven't given enough info .... fnBalance is the balance of WHAT ? How does it relate to AROPNFL ?And avoid RIGHT OUTER JOIN's if you can; logically, you will find LEFT OUTER join's make more sense.Give us some sample data if you can...- Jeff |
|
|
|
grrr223
Starting Member
30 Posts |
Posted - 2004-03-01 : 14:54:42
|
| Actually, it is a FULL OUTER JOIN because I want to return statements for all customers. Some of whom may have a starting balance but no activity in the current month AND some of whom may have activity in the current month but no starting balance (i.e. they're a new customer with no rows in the AROPNFIL before @startdate). But, this shouldn't matter should it?fnBalance is the balance owed at a particular date. I use it to sum the value of all items in the AROPNFIL table that are less than the @startdate. This gives me the net sum that is owed by that customer on that date. This is because an invoice for $1,000 has an amount of $1,000 and a payment against that invoice would have an amount of -$1,000 so they net to $0. So only unpaid invoices would be left. |
|
|
|
grrr223
Starting Member
30 Posts |
Posted - 2004-03-01 : 15:08:49
|
| I was correct with the derived table, it worked!!! |
|
|
|
|
|
|
|
|
Outer Join Issues