WITH Rules(RuleNo,RuleName) AS (

SELECT 1,'MonthlyFinal' UNION ALL

SELECT 2,'MonthlyEstimated' UNION ALL

SELECT 3,'MTDFinal' UNION ALL

SELECT 4,'MTDEstimated' UNION ALL

SELECT 5,'DailyEstimated'),

Ordered AS (

SELECT t.assetid,t.type,t.dt,t.ret,

ROW_NUMBER() OVER(PARTITION BY t.assetid,t.dt...

Change

PARTITION BY ID ORDER BY ID

to

PARTITION BY ID ORDER BY ServiceDate DESC

Maybe this?

DECLARE @t TABLE(AccNo VARCHAR(10))

INSERT INTO @t(AccNo)

SELECT '1212345601' UNION ALL

SELECT '1212345602' UNION ALL

SELECT '1212345603' UNION ALL

SELECT '1412345601' UNION ALL

SELECT '1412345602';

SELECT MAX(AccNo) AS AccNo

FROM @t

GROUP BY LEFT(AccNo,8)

If you're after the number of character differences between two addresses you could use something like 'Levenshtein distance'. It's straightforward to implement as a SQLCLR - there is C code...

DECLARE @T TABLE(id INT,stnum INT,realDay INT,pass INT)

INSERT INTO @T(id,stnum,realDay,pass)

SELECT 42, 100001, 0, 1 UNION ALL

SELECT 43, 100001, 1, 1 UNION ALL

SELECT 44, 100001, 2, 1 UNION ALL

SELECT 45, 100001, 3,...

Use ROW_NUMBER

WITH CTE AS (

SELECT EmpID,Date,Amount,

ROW_NUMBER() OVER(PARTITION BY EmpID ORDER BY Date DESC) AS rn

FROM CheckTable)

SELECT EmpID,Date,Amount

FROM CTE

WHERE rn=1

ORDER BY EmpID;

SELECT q.col.value('local-name(.)','VARCHAR(10)') AS Element,

q.col.value('.','VARCHAR(10)') AS ElementValue

FROM @xmlSomeVariable.nodes('/Parent/*') AS q(col)

You can use EXCEPT

SELECT * FROM #Cartesis_to_hfm

EXCEPT

SELECT * FROM #Cartesis_to_hfm

WHERE (D_OT = 'IM06' OR D_TE = 'TM06')

Try this

WITH CTE1 AS (

SELECT [ASSOCIATEID],[LASTNM],[FIRSTNM],[DateWorked],

CONVERT(VARCHAR(8),

DATEADD(Day,-DENSE_RANK() OVER(PARTITION BY [ASSOCIATEID] ORDER BY [DateWorked]),[DateWorked]),

...

Similar to this

http://www.sqlservercentral.com/Forums/Topic1125847-392-1.aspx

Here's my solution, looks a bit complex but runs quickly

WITH StartsAndEnds(StartEnd,ID,START_DT,END_DATE,AGRMNT,PARTY_ID,BAL_AM) AS (

SELECT 'S' AS StartEnd,

ID,

...

vee_jess (6/28/2011)

---

Thank you for your reply and apologies, I should have read the ethitec before posting! I've basically created a temp table and loaded some data in it to play...

WITH CTE1 AS (

SELECT [ASSOCIATEID],[LASTNM],[FIRSTNM],[DateWorked],[HoursWorked],

CONVERT(VARCHAR(8),DATEADD(Day,-ROW_NUMBER() OVER(PARTITION BY [ASSOCIATEID] ORDER BY [DateWorked]),[DateWorked]),112) AS rnDiff

FROM CVTEST2

WHERE [DateWorked] >= '20090928' AND [DateWorked]...

crackbridge (6/24/2011)

---

Good morning Mark, it's DONE!!!.. I'm actually glad! if sometime you need a team, count on me! Please test it when you get some time:

DECLARE @Products TABLE(ID VARCHAR(20) NOT...

You could probably split the primary key into the leading alphanumeric part and trailing numeric part by searching for the first non-numeric character from the end of the string (use...

This may break depending on your data

WITH CTE AS (

SELECT ID,

ROW_NUMBER() OVER(ORDER BY ID) -

-- If...