WITH CTE AS (

SELECT *,

ROW_NUMBER() OVER(PARTITION BY id, order_status

...

Maybe this?

WITH CTE AS (

SELECT company,details,details2,

ROW_NUMBER() OVER(PARTITION BY company,details

...

WITH CTE AS (

SELECT col1,col2,[date],

COUNT(*) OVER(PARTITION BY col2,[date]) AS num

FROM mytable)

SELECT col1,col2,[date]

FROM CTE

WHERE num>1;

Not sure what you mean by one statement only but this looks like straightforward aggregation using GROUP BY

SET DATEFORMAT DMY

DECLARE @t TABLE([date] DATETIME,acode INT,bcode CHAR(3),amt FLOAT)

INSERT INTO @t([date],acode,bcode,amt)

SELECT '14/09/2011',6019,'002',1427.1969 UNION...

This is the nearest I could come up with - it works with your sample data but you'll need to try this with a larger data set.

WITH CTE AS (

SELECT...

Try changing

a.xmlcontent.value('/Report/DataSources/DataSource/DataSourceReference','varchar(max)')

to

a.xmlcontent.value('/Report/DataSources/DataSource/DataSourceReference[1]','varchar(max)')

Maybe I'm just missing something here, but can't you join on the partial path using LIKE

SELECT m.[Path],p.UserName,p.AccessLevel,p.Path,

ROW_NUMBER() OVER(PARTITION BY m.[Path] ORDER BY LEN(p.[Path]))...

DECLARE @t TABLE(DateValue datetime,Value int)

INSERT INTO @t(DateValue,Value)

SELECT '20120102',0 UNION ALL

SELECT '20120103',1 UNION ALL

SELECT '20120104',1 UNION ALL

SELECT '20120105',1 UNION ALL

SELECT '20120106',1 UNION ALL

SELECT '20120107',1 UNION ALL

SELECT '20120108',0 UNION ALL

SELECT '20120109',1 UNION...

Use the XML exist method

DECLARE @t TABLE (xmlfield XML)

INSERT INTO @t(xmlfield)

VALUES('

<root>

<AList>

<DeliveryNameAddresses>

<DelivN1>

...

GrassHopper (1/19/2012)

---

Mark, this is awesome! thanks...i think i will be using tally tables more often.

Can yo please explain what AND type='P' is ?

and how does Number work? ...

roryp 96873 (1/19/2012)

---

I can't seem to quote messages in my replys for some reason, but holy cow Mark, that is way more elegant than the tally table solution I had...

Using a numbers/tally table

DECLARE @t TABLE (Data VARCHAR(1000))

INSERT @t (Data)

VALUES ('2:42 1/29 On R.P.C resolved vendor was instructed on Friday failed to send 2020817 813 811 810 806 796...

If you can change the INSERT, you don't need the trigger at all

INSERT INTO Customer(CustomerId,Name,Value,Percentage)

SELECT CustomerId,Name,Value,dbo.GetPercentage(Value)

FROM CustomerTemp

Try this

WITH CTE1 AS (

SELECT job, oper_num, wc, move_hrs,

ROW_NUMBER() OVER(PARTITION BY job ORDER BY oper_num) -

...

bicky1980 (1/16/2012)

---

Hi Everyone, Thanks for all your help with this query.

With ChrisM's help, I think I now have the solution:

CREATE TABLE #test2 (indkey NVARCHAR(2), ...