Heh heh! It doesn't have a profanity checker. At least two of my colleagues have something along the lines of "FindTheF****r"!

Like this...

SELECT

SUM(Amount) AS Amount_1 ,

SUM(CASE WHEN Column_1 = 2 AND Column_2 = 4 THEN Amount ELSE 0 END) AS Amount_2

FROM xyz

WHERE Column_1 = 2

AND Column_2 = 4

AND Column_3 =...

Karthick,

1. what are you planning to do once you've found a null and stopped "the process"?

2. what is the process for? Is it for finding nulls?

This is almost certainly easier to test, and quite possibly quicker, if you expand it out into an onion select:

[font="Courier New"]SELECT LagDays, CASE

   WHEN LagDays BETWEEN 0 AND 3...

Using ISNULL?

[font="Courier New"]SELECT m.MonitoredEntityCode,

   CASE WHEN m.MonitoredLocationCode = 'EMISSION_POINT_1' THEN m.NumericResult END AS 'EMISSION_POINT_1',

   CASE WHEN m.MonitoredLocationCode = 'EMISSION_POINT_2' THEN m.NumericResult END AS 'EMISSION_POINT_2',

   CASE WHEN m.MonitoredLocationCode = 'EMISSION_POINT_3'...

Did some work for a mailing shop some years ago, we used a method like this for the same thing:

[font="Courier New"]DROP TABLE #Test

CREATE TABLE #Test (RECID INT, COL1 CHAR(1), COL2...

Madhivanan (10/15/2008)

---

Chris Morris (10/15/2008)

---

That's interesting, I also assumed it took a new connection.

If you create the temp table before executing the string, then (as Barry's code shows) the table...

You're welcome, barcode, many thanks for the feedback.

Try this, barcode:

[font="Courier New"]SELECT c.CONTAINERID, '*** FULL CONTENT ***', 7, c.CONTAINERORIGINID, 1, c.COMPANYID, c.SUBCOID, 1, c.DEPT, c.LEVELID, 1

FROM CONTAINERS c

LEFT JOIN INDEXDATA i ON i.CONTAINERID = c.CONTAINERID AND i.INDEXFIELD1...

That's interesting, I also assumed it took a new connection.

If you create the temp table before executing the string, then (as Barry's code shows) the table is visible to...

Richard Fryar (10/14/2008)

---

Taking the basic idea posted by Chris Morris (page 2 of this thread) and modifying the string build part gets the following. If there are duplicate properties...

I use this handy gadget:

http://extras.sqlservercentral.com/prettifier/prettifier.aspx

Cheers

ChrisM

Hi Debbie, as Peso points out, your best bet is a clustered index on person + startdate.

This generates your new column. Personally, I'd normalise that into two columns, but here...

Debbie, how about posting a chunk of sample data?

Debbie Edwards (10/14/2008)

---

I think Im doing something very wrong.... :crazy:

I got the first example to bring back data but I dont understand the results. The 2nd example worked also but...