Can you post some DDL and sample data?

Try changing

case

when (r1.rate+r2.rate) = 0 then 0

else round(((r1.rate-r2.rate)/(r1.rate+r2.rate))*100.0,2)

end as perc

to

isnull(round(((r1.rate-r2.rate)/(nullif(r1.rate+r2.rate,0)))*100.0,2),0) as perc

To be honest, I'm not a big fan of text...

Try this

select SUM(CASE WHEN SCM_EMAIL_ANSWER_FACT.QUEST_ANSWER IN ('1','2') THEN 100 ELSE 0 END) / COUNT (*)

from SCM_EMAIL_CONTROL

inner join cli_email_rec on [SCM_EMAIL_CONTROL].RESPONDENT_ID = cli_email_rec.RESPONDENT_ID

inner join SCM_EMAIL_ANSWER_FACT on CLI_EMAIL_REC.RESPONDENT_ID = SCM_EMAIL_ANSWER_FACT.RESPONDENT_ID

where

SCM_EMAIL_CONTROL.PGM_CD...

Jeff Moden (12/27/2012)

---

PhilPacha (12/27/2012)

---

Using the simplest SELECT INTO syntax, the IDENTITY property of a column is part of the definition of the destination table. Is there a simple way to...

Try this, its a "Gaps and Islands" problem, you can find information about it in Jeff Modens excellent article here[/url]

WITH CTE AS (

SELECT [State_ID], [Value_Time], [Value],

...

CELKO (12/28/2012)

---

Here is atrick weith a calendar table.

CREATE TABLE Calendar

(cal_date DATE NOT NULL PRIMARY KEY,

julian_business_nbr INTEGER NOT NULL,

...);

INSERT INTO Calendar

VALUES ('2007-04-05', 42),

('2007-04-06',...

Do you see how illogical your example is now Mr. Celko? You can screw it up just as easily using Standard SQL as you can using the T-SQL variant...

This behaviour is documented in BOL, look at the section 'Using the UPDATE statement with information from another table'

here

http://msdn.microsoft.com/en-us/library/ms177523(v=sql.100).aspx

You need to correct your UPDATE statement to join to only one...

Here's another

SELECT currencypair,LEFT(currencypair,3) AS ccy1,RIGHT(currencypair,3) AS ccy2

FROM currency

INTERSECT

(SELECT ccy1 + ccy2,ccy1,ccy2

FROM off_currency_pair

UNION

SELECT ccy2 + ccy1,ccy2,ccy1

FROM off_currency_pair);

select currencypair, ccy1, ccy2

from currency_test , off_currency_pair

where currency_test.currencypair in (off_currency_pair.ccy2 + off_currency_pair.ccy1, off_currency_pair.ccy1 + off_currency_pair.ccy2)

Change

xmltext.query('/PARAMS/FILTERS/CRM')

to

xmltext.query('/PARAMS/FILTERS/CRM/text()')

Have a look here

http://msdn.microsoft.com/en-us/library/ms345122(v=sql.90).aspx

You need a namespace declaration

SET @AppName = @myDoc.value('declare default element namespace "urn:schemas-microsoft-com:asm.v1";

(/assembly/assemblyidentity/@Appname)[1]', 'char(150)' )

SELECT @AppName

Interesting question, I got it wrong.

There seem to be another couple of ligature characters that show this sort of behaviour. Character 153 (TM) doesn't though

SELECT CHAR(140) AS OE,

...