cgrigolini (2/11/2008)

---

What are CTEs?

How can I check the version I'm using?

What I get from SQLSERVER2005 console is:

Microsoft SQL Server Management Studio 9.00.3042.00

Herramientas cliente de Microsoft Analysis Services 2005.090.3042.00

Microsoft Data Access...

cgrigolini (2/11/2008)

---

The error message is:

Mens. 102, Nivel 15, Estado 1, Línea 3

Sintaxis incorrecta cerca de 'datediff'.

Translating to English it sounds:

Message 102,...

Using Adam's data

WITH Promotions AS (

SELECT a.ConsultantID,

a.Level,

a.Title,

a.PeriodEndDate,

...

Tom Leykis (2/8/2008)

---

Now we're cooking! Any chance that could...

Another way

UPDATE [TableName]

SET [ColumnName] = STUFF([ColumnName],1,1,'44')

WHERE [ColumnName] LIKE '0%'

Does this help?

Select r.value('local-name(.)','varchar(20)') as Name,

r.value('(text())[1]','varchar(100)') as Value

From @X.nodes('//*') as x(r)

Here's 'GrpBal' using XML

WITH CTE AS (

SELECT a.AccountID,

(SELECT b.Date AS "@Date",

...

See if this helps

declare @x xml

set @x='

... your xml

'

select r.query('.')

from @x.nodes('/Transmission') as x(r)

where r.exist('(TransmissionHeader/TransmissionType)[text()="QUOTEREQUEST"]')=1

and r.value('(TransmissionHeader/TransmissionDate)[1]','datetime') between '20080101' and '20081231'

and r.exist('(Request/RequestHeader/ForeignRef/text())[1]')=1

and r.value('(Request/RequestHeader/TransportMode)[1]','char(2)') in ('AB','CD')

...

Can you provide some more detail here. Sample XML, table structure, expected output etc.

Maybe this?

SELECT

ROW_NUMBER() OVER (ORDER BY createdatetime) AS RowNum,

COUNT(*) OVER() AS TotalRows,

id,

...

Thanks, I'm a UK citizen.

select A1

from test

group by A1

having count(distinct case when A2 in (1,4) then A2 end)=2

select c.Company_ID,

(select r.value('.','int') as ID,

m.Member_name as Name

...

Your XML doesn't show up, also I think WITH "tablename" will only pick up attributes.

Either change to this

INSERT ID SELECT * FROM OPENXML(@docHandle, N'/IDs/ID') WITH (ID char(4) 'text()')

or use the...

Are you after continuous employment, i.e. no breaks?

In which case try

select CustNumber

from dbo.InvoicesView

where Year(InvoiceDate) between 2003 and 2007 -- 5 years

group by CustNumber

having count(distinct Year(InvoiceDate))=5