Award-winningly sketchy spec, but here goes:

SELECT

offenders = Agg.OffendersOfThisTypeInThisCounty,

m.County_Name,

m.Web_Desc,

'1' AS 'Address_Type'

FROM (

SELECT c.County_Code, c.County_Name, t.Offender_Type_LKP_ID, t.Web_Desc

FROM SPGlobalLookups.dbo.County_LKP c

CROSS JOIN dbo.Offender_Type_LKP t

) m

LEFT JOIN (

SELECT o.Offender_Type_LKP_ID, a.County_Code, OffendersOfThisTypeInThisCounty = COUNT(*)

FROM...

-- Slight rewrite using table aliases and IN to slim-down CASE

UPDATE c

SET Amount = CASE WHEN c.lenght IN (5, 10, 15, 30, 45, 60) THEN r.amount ELSE 5000 END

FROM...

Did you run? 😀

Well done - completing a marathon is no mean feat.

Not really,...

GSquared (11/5/2010)

---

CirquedeSQLeil (11/4/2010)

---

Grant Fritchey (11/4/2010)

---

CirquedeSQLeil (11/4/2010)

---

Has anybody here contemplated changing their name (besides bob err ".") for the forums? I am curious if anybody much cares about the confusion...

andy.roberts (11/5/2010)

---

I agree that with the data you have specified the EMAIL_NO column will always be 1 because...

askquestions (11/1/2010)

---

Hi guys,

can anyone please explain to me in plain english what this update statement is doing (mainly the join select)

Many thanks

update tblEMAIL

set EMAIL_No = EMAILNUMBER

from tblEMAIL a

JOIN (SELECT EMAIL_ID,

row_number()...

Terse

Here's another hacky way of doing it. You would benefit from running the results of the CTE into a #Temp table first.

SELECT ISNULL(r0.DataOutPhoneNumber, ISNULL(r1.DataOutPhoneNumber, r2.DataOutPhoneNumber))

FROM #MUConfigCTE r0

LEFT JOIN #MUConfigCTE r1...

Ok here goes.

Thor (the god of Thunder) is sitting alone on his cloud, envious of the mortals enjoying themselves below. "S0d this" he thinks, floats down and assumes a body....

Do you know the age-old joke about Thor?

Can you please provide a full explanation of the relationship here:

FROM dbo.[Site] s

INNER JOIN dbo.Node n ON s.OwnerNodeID = n.NodeID

Hi Chris

Just as a matter of interest, what is the maximum value for RECURSIONLEVEL that ever appears in a result set from this sproc?

You're welcome. Thank you for the generous feedback 😎

-------------------------------------------------------------

-- Create a tally table - CAREFUL, this is only 120 rows / numbers

-- and should be used only for this demo

-------------------------------------------------------------

DROP TABLE #Numbers

SELECT TOP 120 n = ROW_NUMBER() OVER(ORDER...