Jeff Moden (6/3/2015)

---

Alvin Ramard (6/3/2015)

---

ChrisM@home (6/3/2015)

---

The OP is confused by the location of the ON clauses. Since there are only INNER...

mohanaprabhu.v (6/4/2015)

---

SELECT tblInvoice.*, tblCustomer.FirstName + SPACE(1) +

tblCustomer.LastName as CustomerName FROM tblInvoice WITH(NOLOCK)

LEFT JOIN tblCustomer WITH(NOLOCK) ON tblInvoice.CustomerNo = tblCustomer.CustomerNo

please help us to improve this query performance level without...

Nice thought-provoking article SQLMickey. Here's a thought for you:

"Let’s take a look at a stored procedure query written four different ways."

Whilst a junior developer might select one of the four...

There are many impediments to performance in your query. You want it to run as quickly as possible. Test this as a functional equivalent.

;

WITH TheChosenOne AS (

SELECT TOP 1...

-- Sample data setup

drop TABLE #StaffHours

CREATE TABLE #StaffHours (RecID INT, StaffID INT, StartDate DATETIME, EndDate DATETIME)

INSERT INTO #StaffHours (RecID, StaffID, StartDate, EndDate) VALUES

(17,969,'2015-05-18 00:00:00.000','2015-05-18 06:00:00.000'),

(18,969,'2015-05-18 18:00:00.000','2015-05-19 06:00:00.000'),

(19,969,'2015-05-19 18:00:00.000','2015-05-20 06:00:00.000'),

(20,969,'2015-05-20 18:00:00.000','2015-05-21...

;

WITH SalesPerEmp AS (

SELECT

e.firstname + N' ' + e.lastname AS FullName,

COUNT(*) AS TotalOrders

FROM dbo.Employees e

INNER JOIN Sales.Orders o

ON e.empid = o.empid

GROUP BY e.firstname + N' ' + e.lastname

)

SELECT...

Some ideas here[/url]. As Gianluca says, difficult to tell what is going wrong with no code. Your statement could be something like this:

UPDATE UnassignedWork

SET unassigned = 1

OUTPUT

INSERTED.column1,...

Optimising performance of queries is much easier if...

Ed has the crystal ball today, plus nerves of steel and the patience of a person with a tent guarded by a hippo..

richardmgreen1 (5/28/2015)

---

Hi Chris

I'll take all the help I can get (I'm always willing to learn).

I still don't understand why (without any changes) I could select 53 records last week and...

richardmgreen1 (5/28/2015)

---

Hi Sean

It's quite a monster select statement that normally runs quite happily (or so we thought).

You're basically correct. When the select runs on it's own for some records...

richardmgreen1 (5/28/2015)

---

Hi Sean

It's quite a monster select statement that normally runs quite happily (or so we thought).

You're basically correct. When the select runs on it's own for some records...

The Scientific Method. Million-row tests &...

Have some kip first 🙂

This article [/url]describes how to capture and send/attach a .sqlplan file (execution plan export file).

Have a look at row 16 of my test set and compare it to your CASE statements.