|
|
Posted by J on 11/08/06 15:18
Hey there, sorry about the lack of details.
<< o CREATE TABLE statement for your deposits table>>
CREATE TABLE datQuotationDueDates(
QuoteDueDateID int NOT NULL IDENTITY,
QuoteID int NOT NULL, --clients are assigned to quotes so this is the
field I'll be using for the join
DateTypeID int NOT NULL, --type of payment (datetypeid = 1 is deposit)
DueDate datetime NOT NULL, --date when the payment is due
Amount float NULL, --amount for the payment
TypeID int NOT NULL DEFAULT 1 --(typeid 0 or 1 means amount is a
dollar amount. typeid = 2 means amount is a percentage amount)
PRIMARY KEY(QuoteDueDateID)
)
<<, and a table that represents the view, including the essential
columns only.>>
The result from the view is pretty big but the important columns are
QuoteID (int) - the quote they belong to -, BillingPrice (money) -the
total they have to pay - and payments (money) - how much they've paid
so far -
<< INSERT statements with sample data.>>
INSERT INTO datQuotationDueDates VALUES (4, 1, '10/1/2006', 15, 2)
this is a deposit due on October 1st for 15% of their total billing
price for all clients in quote 4
INSERT INTO datQuotationDueDates VALUES (4, 1, '11/1/2006', 100, 1)
this is a deposit due on November 1st for $100 for all clients in quote
4
INSERT INTO datQuotationDueDates VALUES (4, 1, '12/1/2006', 100, 1)
this is a deposit due on December 1st for $100 for all clients in quote
4
INSERT INTO datQuotationDueDates VALUES (4, 1, '1/1/2007', 100, 1)
this is a deposit due on January 1st for $100 for all clients in quote
4
INSERT INTO datQuotationDueDates VALUES (10, 1, '11/1/2006', 20, 2)
this is a deposit due on November 1st for 20% of their total for all
clients in quote 10
INSERT INTO datQuotationDueDates VALUES (10, 1, '11/15/2006', 10, 2)
this is a deposit due on November 15th for 10% of their total for all
clients in quote 10
INSERT INTO datQuotationDueDates VALUES (10, 1, '12/1/2006', 300, 1)
this is a deposit due on December 1st for $300 for all clients in quote
10
<<The desired result given the sample.>>
let's say, for argument's sake that the view only brings back the
columns I mentioned above: Quote, billingprice and payments along with
client name
4 | 1000.00 | 250.00 | john smith
4 | 1000.00 | 150.00 | jane doe
10 | 2000.00 | 400.00 | jack jones
10 | 2000.00 | 0.00 | james james
now, what I'm looking for is this:
- Quote 4 has 2 deposits that should have been paid already (Oct 1st -
15% of their total - and Nov 1st - $100 -) and the next one is due on
Dec 1st for another $100
- Quote 10 has 1 deposit that should have been paid already (Nov 1st -
20% of their total -) and the next one is due on Dec 1st for another
$300
john smith has paid his deposits in full
jane doe has only paid $150 of $250 that she should have paid
jack jones paid his first deposit in full
james james has paid nothing
The result should be (QuoteID, billingPrice, Payments, Name,
NextDueDate, TotalDue: total due is (the total of deposits due by
NextDueDate) - (payments))
4 | 1000.00 | 250.00 | john smith | 12/1/2006 | 100.00
4 | 1000.00 | 150.00 | jane doe | 12/1/2006 | 200.00
10 | 2000.00 | 400.00 | jack jones | 12/1/2006 | 300.00
10 | 2000.00 | 0.00 | james james | 12/1/2006 | 700.00
<< Which version of SQL Server you are using.>>
SQL Server 2000
Erland Sommarskog wrote:
> 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
[Back to original message]
|