|
|
Posted by Erland Sommarskog on 11/07/06 22:54
J (julian.solis@gmail.com) writes:
> This view is already bringing data from 40+ tables so I am to modify it
> without screwing with anything else that is already in there.
>
> I need to (left) join it with a new table that lists deposits and the
> dates they are due. What I need is to print, for each record in the
> view, the due date for the next deposit due and the total of all
> payments that they will have made by the next due date.
>
> So this is how things are. I join the table and it obviously brings
> multiple records for each record (one for each matching one in the new
> table). I need, instead, to be able to make out what due date I should
> print (the first one that is > GETDATE()?) and the total of deposits up
> to that date.
Obviously no one here can say whether getdate() is right for you.
That depends on the business requirements.
From what you have said, it could be something like this:
LEFT JOIN (SELECT a.clientid, a.duedate, a.amt,
totalamt = (SELECT SUM(c.amt)
FROM deposits c
WHERE c.clientid = a.clientid
AND c.duedate <= b.duedate)
FROM deposits a
JOIN (SELECT clientid, MIN(duedate)
FROM deposits
GROUP BY clientid) AS b ON a.clientid = b.clientid
AND a.duedate = b.duedate
Here I am asuming that (clienid, duedate) is a key in your table,
but that is of course a plain guess. Hopefully this can serve as
a starting point for you endeavour.
> Now, payments can be either dollar amounts or percentages of another
> amount in the view. So if it's an amount I add it, if it's a % I
> calculate the amount and add it.
I did not cover the percentage thing, since that was just too unclear
to me how it works.
If you want more accurate assistance, I would suggest that you post:
o CREATE TABLE statement for your deposits table, and a table that
represents the view, including the essential columns only.
o INSERT statements with sample data.
o The desired result given the sample.
o Which version of SQL Server you are using.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|