Try changing p1.City_Name != p2.City_Name to p1.City_Name > p2.City_Name - that way you will only pick up the link in one direction.

Antares686 (7/13/2015)

---

Kutang Pan (7/10/2015)

---

Is there any other way to write the below query:

SELECT

SUM(CASE WHEN Type = 4 AND STATUS IN (1, 2, 3) THEN Value ELSE 0 END)

,SUM(CASE...

I've worked on a similar process; we had to identify clients across multiple 'unique' identifiers who were actually the same person and where some records had more data populated than...

Yes, use ChrisM's version. Mine would give SalesGrouping according to orderid rather than number of orders.

You can't use the column aliases in the GROUP BY clause; you would either need to repeat the definitions or wrap them in a CTE as below;

;WITH OrderGroups as

(SELECT [orderid]

...

Enya? When the database is down? Surely it has to be Motorhead?

We have code reviews for all languages including SQL. The SQL (DDL and DML) is included largely because we don't have a DBA and a lot of very poor code...

Update: I tried the J Livingstone solution as a straight SELECT rather than a view definition. It takes about the same time as my solution and has the same duplicates...

Thanks for your responses to this.

J Livingstone, I got an error message trying to create a view with your code; "The ORDER BY clause is invalid in views, inline functions,...

Both! We use SSMS for developing/testing. When a project is ready to go into UAT, the changes from development are put into the project in Visual Studio so that they...

The correct number is zero!

In case it helps anyone with a similar problem, here is the closing summary from MS support.

Technical Summary : on one of your server, the observed EXECUTE permissions didn’t match...

As an antidote to Jeff's quick interview technique; the shortest one I ever had was for a contract position. My backside had barely touched the chair when the interviewer asked...

As I said, we've had to take the stance that we're not going to spend more time on it. This was the latest MS response (they think the problem may...

It is a member of other groups with server logins, but none of these are linked to users in the database. The permissions, etc. currently in the database are a...