quote:

---

Originally posted by Kristen

"Date (DD/MM/YY)"

Hopefully that column is actually of DATETIME datatype, rather than a date being stored as a text string in dd/mm/yy format?

If so it won't have any inherent format, such as the dd/mm/yy you mention, but it will be capable of being formatted in any reasonable date-style format that you like

If its actually a text string date then using it "as a date" will turn into nightmare over time

---

quote:

---

Originally posted by Kristen

From an Application Programming background, rather than Relational Data background, perhaps?

They were the ones who created the Y2K issue (or are you too young to remember that? )

---

quote:

---

Originally posted by icw

Thanks will give it a go

it's actually a datetime

---

quote:

---

Originally posted by icw

Hi again

I have just tried it (query below)

SELECT recid, ondate, userid ,USer,ROW_NUMBER() OVER (PARTITION BY Userid,DATEADD(mm,DATEDIFF(mm,0,Date),0) ORDER BY Date) AS Seq,*
From conthist
Where Ref like 'M%' and ondate > '20090101'

and it comes back as
Server: Msg 195, Level 15, State 10, Line 1
'USerROW_NUMBER' is not a recognized function name.

I'm sorry I think i have posted into the wrong forum as I have realised this is SQL 2005 and I querying SQL 2000.
Is that the problem?

Thanks a lot

---

SELECT Unique,	
Date,	 
Userid,
(SELECT COUNT(*) FROM table WHERE Userid=t.Userid AND MONTH(Date)=MONTH(t.Date) AND Unique < t.Unique) + 1 AS IncrementalNumber
FROM table t

quote:

---

Originally posted by icw

Hi Visakh16 I really appreciate your help thanks.

i pasted the query and edited it and tried to run it but it crashed the SQL server.
Have I edited it incorrectly (see below)
My fields are
Unique = Recid
date = Ondate
User = Userid
Thetable is called Conthist

I have done a simple query found that there it should only be bringing back 389 rows so there is not a huge amount of data.

SELECT Recid,
ondate,
Userid,
(SELECT COUNT(*) FROM conthist WHERE Userid=t.Userid AND MONTH(onDate)=MONTH(t.onDate) AND recid < t.recid and srectype = 'S' and Ref like 'M%' and ondate > '20090101') + 1 AS IncrementalNumber
FROM conthist t
Where srectype = 'S' and Ref like 'M%' and ondate > '20090101'


Thanks again if you can help

---

quote:

---

Originally posted by Kristen

"it should only be bringing back 389 "

If there are a lot of rows in [Conthist] and not well indexed for this particular query, it could be slow - makign multiple table scans.

You could try getting the data into a temporary table first, and going from there. (You ought to pre-create a @TableVar and use that, rather than my #TempTable approach, which will potentially block TEMPDB)

SELECT Recid,
ondate,
Userid
INTO #MyTemp
FROM conthist t
Where srectype = 'S' and Ref like 'M%' and ondate > '20090101'

SELECT Recid,
ondate,
Userid,
(SELECT COUNT(*) FROM #MyTemp WHERE Userid=t.Userid AND MONTH(onDate)=MONTH(t.onDate) AND recid < t.recid) + 1 AS IncrementalNumber
FROM #MyTemp AS T

---