The implicit conversion of an empty string to a BIT datatype value 0 is still causing you some confusion - I'd suggest you're...

gcresse (9/6/2011)

---

ChrisM,

This works fantastic with the test data! Thanks for your efforts. Now I will see if I can make it work with my actual tables.

I had actually...

Nope, still waiting for usable sample data scripts.

steveb. (9/6/2011)

---

... an empty string does not equal NULL, to do this with your code you will need.

SELECT CASE WHEN Cast(0 AS bit) = NULL THEN 1...

The image columns aren't required for this exercise, take up an inordinate amount of space,...

Four tables. If you take the time to read the...

Please supply DDL and DML...

Here's a mod which works across different products with different numbers of ingredients:

USE tempdb

GO

CREATE TABLE Transactions(

trnKey ...

kramaswamy (9/2/2011)

---

just out of curiousity Chris, I assume there's no real difference between writing the query like that, or like "with ___ AS (blah) SELECT FROM cte WHERE count =...

gcresse (9/1/2011)

---

Oops. You are correct, I have a mistake in the spreadsheet calculations.

I forgot to mention in my original post that the product is liquid and stored in...

mattech06 (9/1/2011)

---

Thanks guys for the speedy response. I actually used something similar using WHERE instead and it's returned the same amount of rows. However both WHERE and INNER JOIN seems...

Here's the simplest most basic script which makes a lot of assumptions:

SELECT

p.dbPatCnt,

p.dbPatFirstName,

p.dbPatLastName,

f.dbPhoneNumber,

f.dbPhoneTypeID

FROM Patients p

INNER JOIN LnkPhone l ON l.dbKeyCnt = p.dbPatCnt

INNER JOIN Phone f ON...

I think you will find...

Here's another way:

SELECT

question,

Answer,

answereddate,

accredappid

FROM (

SELECT

question,

Answer,

answereddate,

accredappid,

AnswerCount = COUNT(*) OVER (PARTITION BY accredappid, question)

FROM answers

) d

WHERE AnswerCount > 1

ORDER BY accredappid, question

The calculations in your spreadsheet appear to be incorrect, this is what I think it should look like:

[font="Courier New"]

Ingr Pct Ingr...