Help with query

Hi,
I have this two tables:
TABLE1 TABLE2
-- --
A A
B B
C D
D
E
I want my result to be
A Yes
B Yes
C No
D Yes
E No
Joining both tables, having all the values on the first
one and sort of a flag indicating whether it exists in
the second table or not.
Thanks for your help!Try this:
SELECT TableA.ColA,
CASE WHEN (TableB.ColB IS NULL) THEN 'No'
ELSE 'Yes' END
FROM TableA LEFT OUTER JOIN TableB
ON (TableA.ColA = TableB.ColB)
HTH,
Peter.
>--Original Message--
>Hi,
>I have this two tables:
>TABLE1 TABLE2
>-- --
>A A
>B B
>C D
>D
>E
>I want my result to be
>A Yes
>B Yes
>C No
>D Yes
>E No
>Joining both tables, having all the values on the first
>one and sort of a flag indicating whether it exists in
>the second table or not.
>Thanks for your help!
>.
>

Help with joining/selecting values to show

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.J (julian.solis@.gmail.com) writes:

Quote:

Originally Posted by

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.

Quote:

Originally Posted by

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/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hey there, sorry about the lack of details.

<< o CREATE TABLE statement for your deposits table>>
CREATE TABLE datQuotationDueDates(
QuoteDueDateIDintNOT NULL IDENTITY,
QuoteIDintNOT NULL, --clients are assigned to quotes so this is the
field I'll be using for the join
DateTypeIDintNOT NULL, --type of payment (datetypeid = 1 is deposit)
DueDatedatetimeNOT NULL, --date when the payment is due
AmountfloatNULL, --amount for the payment
TypeIDintNOT 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:

Quote:

Originally Posted by

J (julian.solis@.gmail.com) writes:

Quote:

Originally Posted by

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.
>

Quote:

Originally Posted by

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/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

|||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:

Quote:

Originally Posted by

Hey there, sorry about the lack of details.
>
<< o CREATE TABLE statement for your deposits table>>
CREATE TABLE datQuotationDueDates(
QuoteDueDateIDintNOT NULL IDENTITY,
QuoteIDintNOT NULL, --clients are assigned to quotes so this is the
field I'll be using for the join
DateTypeIDintNOT NULL, --type of payment (datetypeid = 1 is deposit)
DueDatedatetimeNOT NULL, --date when the payment is due
AmountfloatNULL, --amount for the payment
TypeIDintNOT 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:

Quote:

Originally Posted by

J (julian.solis@.gmail.com) writes:

Quote:

Originally Posted by

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.

Quote:

Originally Posted by

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/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

|||J (julian.solis@.gmail.com) writes:

Quote:

Originally Posted by

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(
QuoteDueDateIDintNOT NULL IDENTITY,
QuoteIDintNOT NULL, --clients are assigned to quotes so this is the field I'll be using for the join
DateTypeIDintNOT NULL, --type of payment (datetypeid = 1 is deposit)
DueDatedatetimeNOT NULL, --date when the payment is due
AmountfloatNULL, --amount for the payment
TypeIDintNOT 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/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Help with joining records

Table A Table B

BOL# B_BOL#

Chargeback#
Hi All,
I have been struggling with this for the past few months. I have two
tables that I'm inner joining on BOL#=B_BOL#. This works fine. Now for
the problem...When there are chargeback# fields associated with B_BOL#
they aren't being captured as additional records. None of my tables
have primary keys because at any given time any field can contain a
"null" value, so I am unable to assign a foreign key, I think. How can
I attach the subsequent records to the main record to bring back the
additonal data? Please help!!!Not sure this will help, because I'm not sure, did I fully understood
your problem.
If what you mean is like fallow:

B_BOL# from Table B can be a key either with BOL# or/and Chargeback#,
then I will try as fallow:

select * from TableB B
left join TableA A1
on B.B_BOL# = A1.BOL#
left join TableA A2
on B.B_BOL# = A2.Chargeback#

If you do not want to have doble records, you can ommite it with using
is not null (if it is always like this, that the BOL# is null when
chargeback# isn't null ... and opposite).

Or You can use case statement, by removing null records.

In the future, will be better, if you put the script, or some example
data, if the script will be to complicated.

Best regards

Matik

Rnt6872 napisal(a):

Quote:

Originally Posted by

Table A Table B
>
BOL# B_BOL#
>
Chargeback#
Hi All,
I have been struggling with this for the past few months. I have two
tables that I'm inner joining on BOL#=B_BOL#. This works fine. Now for
the problem...When there are chargeback# fields associated with B_BOL#
they aren't being captured as additional records. None of my tables
have primary keys because at any given time any field can contain a
"null" value, so I am unable to assign a foreign key, I think. How can
I attach the subsequent records to the main record to bring back the
additonal data? Please help!!!

|||Rnt6872 (r_fordjr@.msn.com) writes:

Quote:

Originally Posted by

Table A Table B
>
BOL# B_BOL#
>
Chargeback#
Hi All,
I have been struggling with this for the past few months. I have two
tables that I'm inner joining on BOL#=B_BOL#. This works fine. Now for
the problem...When there are chargeback# fields associated with B_BOL#
they aren't being captured as additional records. None of my tables
have primary keys because at any given time any field can contain a
"null" value, so I am unable to assign a foreign key, I think. How can
I attach the subsequent records to the main record to bring back the
additonal data? Please help!!!

1) The standard recommendations for this type of questions, is that
you post a) CREATE TABLE statements for the tables involed. b) INSERT
statements with sample data. c) the desired result given the sample.
This sort of information helps tremendously to understand what you
want to achieve. Also it makes it very easy to develop a tested
solution.

2) If all your columns can be nullable at any time, you have a very
difficult data model to work with. Most people would probably say
that your data model is flat wrong. Maybe there is a good reason
to have all columns nullable, but it sounds very suspicious.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland Sommarskog wrote:

Quote:

Originally Posted by

Rnt6872 (r_fordjr@.msn.com) writes:
>

Quote:

Originally Posted by

>Table A Table B
>>
>>BOL# B_BOL#
>>
>>Chargeback#
>>Hi All,
>>I have been struggling with this for the past few months. I have two
>>tables that I'm inner joining on BOL#=B_BOL#. This works fine. Now for
>>the problem...When there are chargeback# fields associated with B_BOL#
>>they aren't being captured as additional records. None of my tables
>>have primary keys because at any given time any field can contain a
>>"null" value, so I am unable to assign a foreign key, I think. How can
>>I attach the subsequent records to the main record to bring back the
>>additonal data? Please help!!!

>
>
1) The standard recommendations for this type of questions, is that
you post a) CREATE TABLE statements for the tables involed. b) INSERT
statements with sample data. c) the desired result given the sample.
This sort of information helps tremendously to understand what you
want to achieve. Also it makes it very easy to develop a tested
solution.
>
2) If all your columns can be nullable at any time, you have a very
difficult data model to work with. Most people would probably say
that your data model is flat wrong. Maybe there is a good reason
to have all columns nullable, but it sounds very suspicious.
>
>
>

that was so diplomatic...

Given:
"None of my tables have primary keys because at any given time any field can
contain a null"

Then I would say that whoever designed this needed to pass a few more classes in
database design. Your data model needs professional help. Period.

--
Michael Austin.
Database Consultant|||I think you should provide some real examples from your data also, what
you are trying to accomplish by 'bring back additional data'.

Rnt6872 wrote:

Quote:

Originally Posted by

Table A Table B
>
BOL# B_BOL#
>
Chargeback#
Hi All,
I have been struggling with this for the past few months. I have two
tables that I'm inner joining on BOL#=B_BOL#. This works fine. Now for
the problem...When there are chargeback# fields associated with B_BOL#
they aren't being captured as additional records. None of my tables
have primary keys because at any given time any field can contain a
"null" value, so I am unable to assign a foreign key, I think. How can
I attach the subsequent records to the main record to bring back the
additonal data? Please help!!!

help with joining on an index

I'm trying to speed up a query by joining on a known index. However, I've
forgotten the syntax.
Could someone please help me?
JOIN Cases C WITH(NOLOCK) ON D.DoctorID = INDEX(Cases_Doctor) <-- here!!
Thanks a million,
PatrickYou cannot join to an index. You can use an index hint, but in my
experience they are generally not very effective.
JOIN Cases C (NOLOCK INDEX=Doctor) ON D.DoctorID = C.DoctorID
Can you share more information about your situation and problem?
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"news.microsoft.com" <Patrick@.hotmail.com> wrote in message
news:%232lzQsF8FHA.2600@.tk2msftngp13.phx.gbl...
> I'm trying to speed up a query by joining on a known index. However, I've
> forgotten the syntax.
> Could someone please help me?
> JOIN Cases C WITH(NOLOCK) ON D.DoctorID = INDEX(Cases_Doctor) <-- here!!
> Thanks a million,
> Patrick
>|||Hi Adam,
Thanks for responding.
I'm just getting the sum of sales per doctor for either a particular
department or all departments.
When going to product level from an order, many record scans take place; so
i'm trying to speed things up. I have an index called Cases_Doctor that I
want to explicitly use in hopes of making things go quicker. I don't expect
anyone to do my job, but here is my query:
SELECT BeginningDate = @.BeginningDate, EndingDate = @.EndingDate,
PriorBeginningDate = @.PriorBeginningDate, PriorEndingDate =@.PriorEndingDate
,
C.OriginFacility, CP.Prod_Fac, C.DoctorID, D.DoctorDisplayName,
D.WorkPhone, D.SalesPersonID,
Dollars = SUM(CASE WHEN C.DateInvoiced BETWEEN @.BeginningDate AND
@.EndingDate THEN CP.TotalCharge ELSE 0.00 END),
Units = SUM(CASE WHEN C.DateInvoiced BETWEEN @.BeginningDate AND
@.EndingDate THEN CP.Quantity ELSE 0.00 END),
PriorDollars = SUM(CASE WHEN C.DateInvoiced BETWEEN
@.PriorBeginningDate AND @.PriorEndingDate THEN CP.TotalCharge ELSE 0.00 END),
PriorUnits = SUM(CASE WHEN C.DateInvoiced BETWEEN @.PriorBeginningDate
AND @.PriorEndingDate THEN CP.Quantity ELSE 0.00 END)
FROM dim_Doctors D WITH(NOLOCK)
JOIN Cases C WITH(NOLOCK) ON D.DoctorID = INDEX(Cases_Doctor) AND
C.DateInvoiced BETWEEN @.PriorBeginningDate AND @.EndingDate
JOIN CaseProducts CP WITH(NOLOCK) ON CP.CaseID = C.CaseID AND
COALESCE(CP.Prod_Fac, '1') = COALESCE(@.ProductionFacility,
COALESCE(CP.Prod_Fac, '1'))
JOIN Products P WITH(NOLOCK) ON P.ProductID = CP.ProductID AND
COALESCE(P.ProductTypeID, '1') = COALESCE(@.ProductTypeID,
COALESCE(P.ProductTypeID, '1'))
WHERE COALESCE(C.OriginFacility, '1') = COALESCE(@.OriginFacility,
COALESCE(C.OriginFacility, '1'))
AND COALESCE(D.SalesPersonID, '1') = COALESCE(@.SalesPersonID,
COALESCE(D.SalesPersonID, '1'))
AND C.Type IN(0,1)
GROUP BY C.OriginFacility, CP.Prod_Fac, C.DoctorID, D.DoctorDisplayName,
D.WorkPhone, D.SalesPersonID
If anything jumps out as a definite no-no. Please let me know.
Thanks again for your response,
Patrick
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:%23fkSS4F8FHA.604@.TK2MSFTNGP10.phx.gbl...
> You cannot join to an index. You can use an index hint, but in my
> experience they are generally not very effective.
> JOIN Cases C (NOLOCK INDEX=Doctor) ON D.DoctorID = C.DoctorID
> Can you share more information about your situation and problem?
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
> "news.microsoft.com" <Patrick@.hotmail.com> wrote in message
> news:%232lzQsF8FHA.2600@.tk2msftngp13.phx.gbl...
>|||Hey Adam,
You're exactly right (I'm starting to learn thats more than likely the
case :))
When specifying an index the query took over 2 minutes.. without it took 55
secs.
A total of 6 mons worth of sales for about 16 thousand customers will take
some time,
but I'm still hoping to get it quicker.
Patrick
"news.microsoft.com" <Patrick@.hotmail.com> wrote in message
news:u7OHBAG8FHA.3876@.TK2MSFTNGP09.phx.gbl...
> Hi Adam,
> Thanks for responding.
> I'm just getting the sum of sales per doctor for either a particular
> department or all departments.
> When going to product level from an order, many record scans take place;
> so i'm trying to speed things up. I have an index called Cases_Doctor that
> I want to explicitly use in hopes of making things go quicker. I don't
> expect anyone to do my job, but here is my query:
> SELECT BeginningDate = @.BeginningDate, EndingDate = @.EndingDate,
> PriorBeginningDate = @.PriorBeginningDate, PriorEndingDate
> =@.PriorEndingDate ,
> C.OriginFacility, CP.Prod_Fac, C.DoctorID, D.DoctorDisplayName,
> D.WorkPhone, D.SalesPersonID,
> Dollars = SUM(CASE WHEN C.DateInvoiced BETWEEN @.BeginningDate AND
> @.EndingDate THEN CP.TotalCharge ELSE 0.00 END),
> Units = SUM(CASE WHEN C.DateInvoiced BETWEEN @.BeginningDate AND
> @.EndingDate THEN CP.Quantity ELSE 0.00 END),
> PriorDollars = SUM(CASE WHEN C.DateInvoiced BETWEEN
> @.PriorBeginningDate AND @.PriorEndingDate THEN CP.TotalCharge ELSE 0.00
> END),
> PriorUnits = SUM(CASE WHEN C.DateInvoiced BETWEEN
> @.PriorBeginningDate AND @.PriorEndingDate THEN CP.Quantity ELSE 0.00 END)
> FROM dim_Doctors D WITH(NOLOCK)
> JOIN Cases C WITH(NOLOCK) ON D.DoctorID = INDEX(Cases_Doctor) AND
> C.DateInvoiced BETWEEN @.PriorBeginningDate AND @.EndingDate
> JOIN CaseProducts CP WITH(NOLOCK) ON CP.CaseID = C.CaseID AND
> COALESCE(CP.Prod_Fac, '1') = COALESCE(@.ProductionFacility,
> COALESCE(CP.Prod_Fac, '1'))
> JOIN Products P WITH(NOLOCK) ON P.ProductID = CP.ProductID AND
> COALESCE(P.ProductTypeID, '1') = COALESCE(@.ProductTypeID,
> COALESCE(P.ProductTypeID, '1'))
> WHERE COALESCE(C.OriginFacility, '1') = COALESCE(@.OriginFacility,
> COALESCE(C.OriginFacility, '1'))
> AND COALESCE(D.SalesPersonID, '1') = COALESCE(@.SalesPersonID,
> COALESCE(D.SalesPersonID, '1'))
> AND C.Type IN(0,1)
> GROUP BY C.OriginFacility, CP.Prod_Fac, C.DoctorID, D.DoctorDisplayName,
> D.WorkPhone, D.SalesPersonID
> If anything jumps out as a definite no-no. Please let me know.
> Thanks again for your response,
> Patrick
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:%23fkSS4F8FHA.604@.TK2MSFTNGP10.phx.gbl...
>|||SQL Server will tend to not use a non-clustered index if the lookup into the
cluster is going to be more expensive than simply doing a table scan (which
is probably what's happening in your case.) It looks like you may be able
to create a covering non-clustered index for that query.
Please read the following article, and post back here if you have any
questions:
http://www.sql-server-performance.c...ing_indexes.asp
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"news.microsoft.com" <Patrick@.hotmail.com> wrote in message
news:u7OHBAG8FHA.3876@.TK2MSFTNGP09.phx.gbl...
> Hi Adam,
> Thanks for responding.
> I'm just getting the sum of sales per doctor for either a particular
> department or all departments.
> When going to product level from an order, many record scans take place;
> so i'm trying to speed things up. I have an index called Cases_Doctor that
> I want to explicitly use in hopes of making things go quicker. I don't
> expect anyone to do my job, but here is my query:
> SELECT BeginningDate = @.BeginningDate, EndingDate = @.EndingDate,
> PriorBeginningDate = @.PriorBeginningDate, PriorEndingDate
> =@.PriorEndingDate ,
> C.OriginFacility, CP.Prod_Fac, C.DoctorID, D.DoctorDisplayName,
> D.WorkPhone, D.SalesPersonID,
> Dollars = SUM(CASE WHEN C.DateInvoiced BETWEEN @.BeginningDate AND
> @.EndingDate THEN CP.TotalCharge ELSE 0.00 END),
> Units = SUM(CASE WHEN C.DateInvoiced BETWEEN @.BeginningDate AND
> @.EndingDate THEN CP.Quantity ELSE 0.00 END),
> PriorDollars = SUM(CASE WHEN C.DateInvoiced BETWEEN
> @.PriorBeginningDate AND @.PriorEndingDate THEN CP.TotalCharge ELSE 0.00
> END),
> PriorUnits = SUM(CASE WHEN C.DateInvoiced BETWEEN
> @.PriorBeginningDate AND @.PriorEndingDate THEN CP.Quantity ELSE 0.00 END)
> FROM dim_Doctors D WITH(NOLOCK)
> JOIN Cases C WITH(NOLOCK) ON D.DoctorID = INDEX(Cases_Doctor) AND
> C.DateInvoiced BETWEEN @.PriorBeginningDate AND @.EndingDate
> JOIN CaseProducts CP WITH(NOLOCK) ON CP.CaseID = C.CaseID AND
> COALESCE(CP.Prod_Fac, '1') = COALESCE(@.ProductionFacility,
> COALESCE(CP.Prod_Fac, '1'))
> JOIN Products P WITH(NOLOCK) ON P.ProductID = CP.ProductID AND
> COALESCE(P.ProductTypeID, '1') = COALESCE(@.ProductTypeID,
> COALESCE(P.ProductTypeID, '1'))
> WHERE COALESCE(C.OriginFacility, '1') = COALESCE(@.OriginFacility,
> COALESCE(C.OriginFacility, '1'))
> AND COALESCE(D.SalesPersonID, '1') = COALESCE(@.SalesPersonID,
> COALESCE(D.SalesPersonID, '1'))
> AND C.Type IN(0,1)
> GROUP BY C.OriginFacility, CP.Prod_Fac, C.DoctorID, D.DoctorDisplayName,
> D.WorkPhone, D.SalesPersonID
> If anything jumps out as a definite no-no. Please let me know.
> Thanks again for your response,
> Patrick
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:%23fkSS4F8FHA.604@.TK2MSFTNGP10.phx.gbl...
>|||On Wed, 23 Nov 2005 13:59:20 -0500, news.microsoft.com wrote:
(snip)
>If anything jumps out as a definite no-no. Please let me know.
Hi Patrick,
These four comparisons are almost guaranteed performance-killers:

>COALESCE(CP.Prod_Fac, '1') = COALESCE(@.ProductionFacility,
>COALESCE(CP.Prod_Fac, '1'))
(...)
>COALESCE(P.ProductTypeID, '1') = COALESCE(@.ProductTypeID,
>COALESCE(P.ProductTypeID, '1'))
> WHERE COALESCE(C.OriginFacility, '1') = COALESCE(@.OriginFacility,
>COALESCE(C.OriginFacility, '1'))
> AND COALESCE(D.SalesPersonID, '1') = COALESCE(@.SalesPersonID,
>COALESCE(D.SalesPersonID, '1'))
These can only be satisfied by table scan or index scan (maybe a partial
index scan if some of the other filters allow it).
If you want to take maximum advantage of an index on, for instance,
P.ProductTypeID, then make sure that you write the filter as:
P.ProductTypeID = some interesting expression
This allows SQL Server to calculate the expression first, then use the
index to find the matching row(s) directly, without the need to scan
over lots of other rows and determine if they match as well.
I know too little about your tables and your data to be able to tell you
how "some interesting expression" would have to look.
If you need more assistance, then check www.aspfaq.com/5006 first.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)