with cte as (

select row_number() over (partition by identifier, templateid order by CreateDate desc ) as num,

count(*) over (partition by identifier, templateid) as...

Use the OUTPUT clause

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

Try this

DECLARE @doc XML

SET @doc ='

<AllocateGiftResult xmlns:a="http://schemas.datacontract.org/2004/07/StockControlServices.Models.Response"

xmlns:i="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://www.xxx.co.uk/giftmanagement/">

<a:AllocationReference i:nil="true" />

<a:AmpsReference i:nil="true" />

<a:ErrorString>StockRecordNotFound</a:ErrorString>

<a:ResultCode>Error</a:ResultCode>

</AllocateGiftResult>';

WITH XMLNAMESPACES('http://schemas.datacontract.org/2004/07/StockControlServices.Models.Response' AS a,

...

Try adding

WITH XMLNAMESPACES(DEFAULT 'http://schemas.microsoft.com/sqlserver/2008/sqlaudit_data')

to the start of your query

ScottPletcher (6/27/2012)

---

Code looks OK to me.

Regarding date formatting, I'd suggest using 'YYYYMMDD', which is foolproof on SQL Server, rather than 'YYYY-MM-DD', which can fail depending on specific settings in SQL...

Are you really using SQL Server 2000? If so, this should work but for SQL Server 2005 upwards there are better solutions.

SELECT s.ID, s.PDate, score, code

FROM #mytable S ...

Try this.

WITH CTE AS (

SELECT HotelId,RoomTypeId ,DateKey ,FreeCount,

ROW_NUMBER() OVER(PARTITION BY HotelId,RoomTypeId

...

DECLARE @t TABLE(col1 INT, col2 INT)

INSERT INTO @t(col1,col2)

SELECT 1, 1 UNION ALL

SELECT 2, 2 UNION ALL

SELECT 3, -999 UNION ALL

SELECT 4, -999 UNION ALL

SELECT 5, -999 UNION ALL

SELECT 6, 6...

I don't...

That's because it's in quarter 3

Search for "Calendar Table", you may want something like this

SELECT dt,

DATEPART(quarter,dt)

FROM Calendar

WHERE dt BETWEEN '01/04/2011' AND '30/06/2011'

SELECT DATEPART (quarter , '01/05/2011' )

DECLARE @t TABLE(ID INT, FromID INT)

INSERT INTO @t(ID,FromID)

SELECT 1, 0 UNION ALL

SELECT 2, 1 UNION ALL

SELECT 3, 0 UNION ALL

SELECT 4, 2 UNION ALL

SELECT 5, 3 UNION ALL

SELECT 6, 4;

DECLARE...

WITH CTE AS (

SELECT empcode,absent_date,

ROW_NUMBER() OVER(PARTITION BY empcode ORDER BY absent_date) AS rn

FROM EMP_ABSENT)

SELECT empcode

FROM CTE

GROUP BY empcode,DATEADD(Day,-rn,absent_date)

HAVING COUNT(*)>=3

Sony Francis @EY (6/14/2012)

---

Try this

SELECT level

INTO #myTable

FROM(VALUES('1'),('1.1.3'),('1.2.4.4'),('2'),('3.1'),('10.1'),('10.2'),('11.1'),('11.2'),

('13.1'),('3.2'),('4'),('5'),('6'),('7'),('8'),('8.1'),('9'),('14'))a(level);

;WITH cte

AS

(

SELECT [level], ROW_NUMBER() OVER(

PARTITION BY

SUBSTRING ([level], CASE WHEN CHARINDEX('.',[level],0)=0 THEN 1 ELSE 0 END, CASE WHEN CHARINDEX('.',[level],0)=0 THEN LEN([level]) ELSE CHARINDEX('.',[level],0)...