Help with joining/selecting values to show

[J]

I am editing a pre-existing view.

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.

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.

Example:

for group X of clients...
Deposit 1 due on oct 1: $20
Deposit 2 due on oct 15: $30
Deposit 3 due on nov 15: $40
Deposit 4 due on nov 30: $50

for group Y of clients...
Deposit 1 due on Oct 30: $200
Deposit 2 due on Nov 30: $300
Deposit 3 due on Dec 30: $400

So when if I execute the view today (Nov 7th) each client from group X
should have:
Next Due Date: nov 15. Total: $90 (deposit 1 + deposit 2 + deposit 3)

Group Y should have:
Next Due Date: Nov 30, total: $500 (Deposit 1 + deposit 2)

And so on.

[Erland Sommarskog]

J (ju**********@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[J]

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 (ju**********@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[J]

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 (ju**********@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Erland Sommarskog]

J (ju**********@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx