SELECT dt, DENSE_RANK() OVER(ORDER BY YEAR(dt), MONTH(dt)) rn FROM @tbl

ORDER BY dt

Try using DENSE_RANK instead of ROW_NUMBER

For completeness, here's a simple SQLCLR to perform the same task, appears to perform okay.

using System;

using System.Data;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;

using System.Text;

public partial class UserDefinedFunctions

{

[Microsoft.SqlServer.Server.SqlFunction]

...

Also this

WITH CTE AS (

SELECT *,

MAX(IndivOrderRank) OVER(PARTITION BY Rep, ProductGroup, CustAcc) AS mx

FROM @results)

UPDATE CTE

SET MaxOrderRank=mx;

Another solution, this one not requiring a join

WITH CTE AS (

SELECT ITEM, REV, DATE,

ROW_NUMBER() OVER(PARTITION BY ITEM ORDER BY REV DESC) AS rn

FROM...

Haryadi Santoso (5/6/2011)

---

Hi Mark,

As i stated before, i had used ELEMENTS XSINIL, to product XML tag like <kotaDomisili xsi:nil="true" />

but the system still won't accept it. The system will accept...

This seems to work

declare @master_club table(VCLUBCD varchar(100),VCLUBNM varchar(100), DCLUB datetime,

VCTYCLB varchar(100),VPICCLB varchar(100),VJNSCLB varchar(100),VTYPCLB varchar(100))

insert into @master_club

select '00001','HONDA TIGER MAILING LIST','19781025 00:00:00','3101','BUDI','SPORT','TIGER' union all

select '00002','SUZUKI CLUB','20021005 00:00:00',null,'ADY','BEBEK','SATRIA'

declare @output xml;

set @output...

SELECT

(SELECT LastName AS "LASTNAME",

FirstName AS "FIRSTNAME"

FROM #Temp

FOR XML PATH(''),ROOT('INDIVIDUALS'),TYPE)

FOR XML PATH('INDIVIDUALS_LIST')

Maybe this?

WITH CTE AS (

SELECT Id,Type,Amt,

SUM(CASE WHEN Type IN ('Debit', 'Credit') THEN Amt ELSE 0 END) OVER(PARTITION BY Id) AS sm

FROM GLTbl)

SELECT Id,Type,Amt

FROM...

WITH Pivotted AS (

SELECT Bid,

BranchName,

WeekRange,

OfficeHours,

...

Would a binary collation work?

SELECT L6

FROM testdata

WHERE L6 LIKE '%[^0-9A-Za-z]%' COLLATE Latin1_General_BIN

WITH Hours([Hour]) AS (

SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL

SELECT 4 UNION ALL SELECT 5 UNION ALL...

DECLARE @x XML

SET @x='<root>

<product>

<product_id ID = "1"/>

<product_id ID = "2"/>

<product_id ID = "3"/>

</product>

<discount>

<discount_id ID ="9"/>

<discount_id ID ="10"/>

</discount>

<tax>

<tax_id ID = "0"/>

</tax>

</root>

';

SELECT p.value('@ID','INT') AS Product_ID,

...

Try this

WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')

SELECT t.c.value('@Column', 'varchar(128)') AS 'Parameter', t.c.value('@ParameterCompiledValue', 'varchar(128)') AS 'Value'

from @x.nodes('/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple/QueryPlan/ParameterList/ColumnReference') as T(c)

opc.three (4/7/2011)

---

Brandie Tarvin (4/7/2011)

---

Wow. I wonder if they had permissions to reprint that article. I found it yesterday at Simple Talk.

Or maybe it was the guy at Simple Talk...