|
|
Posted by Erland Sommarskog on 11/09/06 23:17
J (julian.solis@gmail.com) writes:
> Hey there, sorry about the lack of details.
>...
><<, 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 -
Thanks for the table and sample data. Below is my query (together with
a table I composed for the clients.) For quote 10, I got different
results that you had as the desired. Looking at the sample data, my
result seemed OK, but I may have misunderstood something about the
business rules. Whether this query performs better than yours, I don't
know. Only testing can tell.
One note about the table datQuotationDueDates: I would guess that (QuoteID, DueDate) is unique. But in such case that should be the primary key, and
the column QuoteDueDateID does not really serve any purpose.
Here is the script:
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)
)
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
go
CREATE TABLE clients (quoteid smallint not null,
billprice float not null,
payments float not null,
name varchar(20) not null)
go
insert clients values(4, 1000.00, 250.00, 'john smith')
insert clients values(4, 1000.00 , 150.00, 'jane doe')
insert clients values(10, 2000.00, 400.00, 'jack jones')
insert clients values(10 , 2000.00, 0.00, 'james james')
go
SELECT c.quoteid, c.billprice, c.payments, c.name,
due.DueDate,
due.Amount + due.Perc * c.billprice / 100 - c.payments
FROM clients c
JOIN (SELECT a.QuoteID, DueDate = MAX(a.DueDate),
Amount = SUM(CASE TypeID WHEN 1 THEN Amount ELSE 0 END),
Perc = SUM(CASE TypeID WHEN 2 THEN Amount ELSE 0 END)
FROM datQuotationDueDates a
JOIN (SELECT QuoteID, DueDate = MIN(DueDate)
FROM datQuotationDueDates
WHERE DueDate > getdate()
GROUP BY QuoteID) AS b
ON a.QuoteID = b.QuoteID
AND a.DueDate <= b.DueDate
GROUP BY a.QuoteID) AS due
ON due.QuoteID = c.quoteid
go
DROP TABLE datQuotationDueDates
drop table clients
--
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]
|