Wonder if it works on the real data though?

My money's on CC's script, it's more flexible 😎

Cadavre (8/31/2011)

---

greg.bull (8/31/2011)

---

On to the next point, if a month row is missing, then it'd be ignored, so for month 6, we'd be summing month 6, month 5 (which'd be...

How about posting a dozen or so of these values?

UPDATE #Sample SET Metric2 = ISNULL(NULLIF(Metric1+Metric1-1+Metric1-2,0),1)

😉

What would be the value for month 5? Is the value 9 correct for month 4?

DROP TABLE #Sample

CREATE TABLE #Sample (Yearmonth DATE, Metric1 INT, Metric2 INT)

INSERT INTO #Sample (Yearmonth, Metric1,...

Probably like this

SELECT LEFT(LTRIM(RTRIM(SUBSTRING(ISNULL(prefix,''),7,4) + phone)),20)

FROM (

SELECT Prefix = ' 0151', phone = '7217164' UNION ALL

SELECT NULL, '7217164'

) [sample]

but it depends on the...

Looks like Excel. Try this:

LEFT(LTRIM(RTRIM(SUBSTRING(prefix,7,4) + phone)),20)

Functions around columns in table dbo.PolicyEntry are causing table scans:

replace(executive1,' ','') = 'DINESHANJARA'

Change these to

[executive1] IN ('DINESHANJARA','DINESH ANJARA').

;WITH SourceTable AS (

SELECT Id = 1, Projectid = 'xxx', MonHour = 8, TueHour = 6, WedHour = 4, ThHour = 6, FriHour = 5, SatHour = 3 UNION...

Job's a good 'un. Thanks for the feedback.

Please post the actual plans, these are estimated plans.

DROP TABLE #Application

CREATE TABLE #Application (BoxID INT, Province VARCHAR(10), BoxPosition INT)

INSERT INTO #Application (BoxID, Province, BoxPosition)

SELECT 5632, 'KZN', 1 UNION ALL

SELECT 5632, 'KZN', 2 UNION ALL

SELECT 5632, 'KZN', 3 UNION...

Polystyrene

Can you please post the actual plan, and the view definition for ViewEntryDetails.

Cheers

tfifield (8/24/2011)

---

ChrisM@Work (8/23/2011)

---

Put it into a stored procedure. Crystal works fine with them, the performance is likely to be better, and there will be no temptation for folks to attempt...