Here's another version that will give monthly results for each employee

with cte as (

select ID,date_e,val_unit,

row_number() over(partition by ID, convert(char(6),date_e,112) order by date_e desc)...

Not entirely clear what you want, ID 4807756 seems to turn up twice in your expected results?

Maybe this

with cte as (

select ID,date_e,val_unit,

row_number() over(partition...

select Email,max(Fname) as Fname

from mytable

group by Email

> Note: I'm not so sure your ROW_NUMBER (ordinal position logic) is correct as you're ordering on the > Numbers, not their position in the string.

It does work. Try it.

As...

You can quite easily get ordinal numbers by doing this.

CREATE FUNCTION Foo

(@p_integer_list VARCHAR(MAX))

RETURNS @return_table TABLE (ROW_NUMBER int IDENTITY(1,1), INTEGER_ID int)

AS

BEGIN;

INSERT INTO @return_table (INTEGER_ID)

SELECT CAST(SUBSTRING(@p_integer_list,

...

> Personally, I don't like the "numbers" table approach due to limitations on

> the values that can be passed in the delimited string along with duplicate handling.

John,

Can you expand on...

Using a numbers/sequence table

http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-numbers-table.html

create procedure [dbo].[_test]

@list varchar(50)

as

WITH CTE AS (

SELECT CAST(SUBSTRING(@list,

...

with cte as

(select Col0,Col1,Col2,Col3,

row_number() over(partition by Col1 order by Col0) as rn

from #rollupTable)

select Col1,

max(case when rn=1...

You can script view, functions, stored procedures and triggers using this

select object_definition(object_id)

from sys.objects

where type_desc in ('SQL_SCALAR_FUNCTION',

'SQL_STORED_PROCEDURE',

'SQL_TABLE_VALUED_FUNCTION',

'SQL_TRIGGER',

'VIEW')

There are plenty of scripts around for tables.

Worth reading this

http://www.sommarskog.se/error-handling-I.html

Maybe this?

WITH CTE AS(

SELECT a.colA,a.colB

FROM MyTable a

WHERE NOT EXISTS(SELECT * FROM MyTable b

WHERE...

Try adding a materialised path to your CTE

With downline (ConsultantID,ConsultantName,SponsorID,SponsorName,DownLineLevel,FullPath)

AS

(

SELECT A.ConsultantID

,A.FirstName + ' ' + A.LastName as ConsultantName

,A.SponsorID

,(SELECT [FirstName] + ' ' + [LastName] FROM Consultant WHERE ConsultantID = a.SponsorID)

,0...

Using a calendar table such as found here

http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-calendar-table.html

you can query for the number of employees hired, terminated and total per month using the query below.

There are plenty...

select r.value('First_Name[1]','varchar(10)') as First_Name,

r.value('Last_Name[1]','varchar(10)') as Last_Name,

r.value('Age[1]','int') as Age

from @Xmldocument.nodes('/Data/Customer') as d(r)

select row_number() over(partition by Value order by Value) as ID,

Value

from TableA