Incorrect results. I'm genuinely surprised you can't see it.

mattech06 (5/27/2015)

---

Hi Chris,

what do you mean by actual execution plan?

The statement works - just if the result set (100+) is bigger than normal it takes over a minute whilst under...

devsql123 (5/27/2015)

---

Thank you very much guys for this solution...But i made little Typo..so this solution needs little bit update...

Currently my 1st Update statement has Isnull(td.IsPosted, 0) = 0 in WHERE...

Your CASE expressions don't look correct with multiple AND / OR with no defining parens.

mattech06 (5/27/2015)

---

Is there a way of improving the SUM statements below? When I run the query without them it's very fast but with them it takes ages, thanks,

SELECT

r.dbPatID,...

UPDATE t SET

t.AODYD = CASE WHEN ISNULL(td.IsPosted, 0) = 0 THEN td.ODYD ELSE t.AODYD END,

t.DAODYD = CASE WHEN ISNULL(td.IsPosted, 0) <> 0 THEN td.ODYD ELSE t.DAODYD END

FROM #ReportData...

DECLARE @txtMessageText VARCHAR(MAX)

SET @txtMessageText = 'QU - 119480,8377,EX,0810,4300,LT,3,154,1,0,15005,08R,17'

-- NOTE: STUFF() removes the final character from the string. Since the string is

-- reversed, the expression is STUFF(expression,1,1,'')

SELECT...

-- ALWAYS figure out a SELECT to test the update.

SELECT

b.*, a.*

FROM dbo.CardioStaffClinical a

INNER JOIN (

SELECT

[Name (Username):],

Last_Name2 = SUBSTRING([Name (Username):], 1, x.CommaPos-1),

First_Name2 = SUBSTRING([Name (Username):], x.CommaPos+3, 8000)

FROM...

Food for thought:

SELECT DATEDIFF(millisecond,'20150521','20150522') --86400000 ms in a day

SELECT

RightNow, RightNowNumeric, RightNowDecimalPart, RightNowIntegerPart,

ReconstructedDate = DATEADD(day,RightNowIntegerPart,0),

ReconstructedDateTime = DATEADD(millisecond,86400000*RightNowDecimalPart,DATEADD(day,RightNowIntegerPart,0))

FROM (

SELECT

RightNow,

RightNowNumeric = CAST(RightNow AS NUMERIC(18,12)),

RightNowDecimalPart = CAST(RightNow AS NUMERIC(18,12))%1,

RightNowIntegerPart = CAST(CAST(RightNow...

You're welcome. Don't settle for the first attempt though;

Make it work (see above)

Make it fast (see next query)

Make it pretty...

OK, think I've got it now:

SELECT j.*,

s1.Gate1ID, s1.Gate1ProcessId,

s2.Gate2ID, s2.Gate2ProcessId,

s3.Gate3ID, s3.Gate3ProcessId,

s4.Gate4ID, s4.Gate4ProcessId,

s5.Gate5ID, s5.Gate5ProcessId

FROM #Jobs j

LEFT JOIN (

SELECT JobId, n

FROM (

SELECT JobId, cnt...

Or like this?

SELECT j.*,

Gate1ID = (CASE WHEN s.GateId = 1 THEN 1 ELSE NULL END), Gate1ProcessID = (CASE WHEN s.GateId = 1 THEN s.ProcessID ELSE NULL END),

Gate2ID = (CASE...

Like this?

SELECT j.*,

Gate1ID = s1.GateId, Gate1ProcessID = s1.ProcessID,

Gate2ID = s2.GateId, Gate2ProcessID = s2.ProcessID,

Gate3ID = s3.GateId, Gate3ProcessID = s3.ProcessID,

Gate4ID = s4.GateId, Gate4ProcessID = s4.ProcessID,

Gate5ID = s5.GateId, Gate5ProcessID = s5.ProcessID ...

Sean Lange (5/18/2015)

---

There is something missing here for sure. You stated that you use the DelimitedSplit8K function in your 2000 instance. In the original format it would NOT work in...

Compare the estimated plans for the following statements:

SELECT i.*

FROM @invoice i

WHERE i.org_id in (select item from dbo.DelimitedSplit8K(@org_list,','))

SELECT i.*

FROM @invoice i

INNER JOIN dbo.DelimitedSplit8K(@org_list,',') d ON d.item = i.org_id

In the first plan,...