|
|
Posted by J on 11/08/06 16:43
Well, this is apparently solved.
There probably was simpler or more optimized way of doing it but I'm
just starting to use SQL server for tasks like this one. My solution
was this:
LEFT JOIN
(SELECT DISTINCT QuoteID,
(SELECT TOP 1 DueDate FROM datQuotationDueDates WHERE DateTypeID = 1
and DueDate > GETDATE() and QuoteID = dQDD.QuoteID ORDER BY DueDate) as
NextDueDate
,(SELECT SUM(CASE WHEN (TypeID = 1 OR TypeID = 0) THEN Amount END )
FROM datQuotationDueDates WHERE DateTypeID = 1 AND QuoteID =
dQDD.QuoteID AND DueDate <= (SELECT TOP 1 DueDate FROM
datQuotationDueDates WHERE DateTypeID = 1 and DueDate > GETDATE() AND
QuoteID = dQDD.QuoteID ORDER BY DueDate) ) AS Dollars
,(SELECT SUM(CASE WHEN TypeID = 2 THEN Amount END ) FROM
datQuotationDueDates WHERE DateTypeID = 1 AND QuoteID = dQDD.QuoteID
AND DueDate <= (SELECT TOP 1 DueDate FROM datQuotationDueDates WHERE
DateTypeID = 1 AND DueDate > GETDATE() AND QuoteID = dQDD.QuoteID ORDER
BY DueDate) ) AS Percentages
FROM datQuotationDueDates dQDD) AS NextDD on NextDD.QuoteID =
vwClients.QuoteID
this way I bring the next due date, a column with to total of dollar
amounts and the total of percentages. Then I print out the date and use
amount and percentages along with billingprice and payments to
calculate the amount due for the next due date
I'd still be very interested in hearing how any of you would have done
it
J wrote:
> 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
Navigation:
[Reply to this message]
|