That works, thanks!:-)

"or you need to convert the column to a date without time (no index seeks possible)." - how do I do that?

ScheduledActivity.ScheduledStartTime = CONVERT(datetime, '14/03/2016',110) gives me no values returned and that same out of range error

That does work, thanks. But it means I cannot use it the way I need to. It would be fine is I was running the sql manually but...

I don't want to use a declare function as I am using the sql query to feed a report where the report parameter is a single date entered by the...

Thanks, but using that returns no records, and I know that it should return something. I have written in the WHERE:

ScheduledActivity.ScheduledStartTime = CONVERT(datetime, '14/03/2016',103)

The datetime field in the output...

Sorry, its Adaptive server enterprise version 15

Am I on the wrong forum? Can you point me in the right direction?

Thanks

Transact SQL? Im using a package that comes with our system but can also use SQL advantage of that makes any difference.

; WITH cte (Patient.PatientId,Course.CourseId,Course.StartDateTime,RowNumber) AS (

SELECT Patient.PatientId,Course.CourseId,Course.StartDateTime

,ROW_NUMBER() OVER (PARTITION BY Patient.PatientId,YEAR(Course.StartDateTime),MONTH(Course.StartDateTime ORDER BY YEAR(Course.StartDateTime),MONTH(Course.StartDateTime

))

FROM

Patient,

Course

WHERE

Patient.PatientSer = Course.PatientSer

)

SELECT Patient.PatientId,Course.CourseId,Course.StartDateTime,SIGN(RowNumber)-SIGN(RowNumber-1) AS [CountUnique]

FROM cte ;

I get the error...

Incorrect syntax near keyword 'with'

Incorrect syntax near keyword 'with'

Hi, thanks for the reply. Is there a bit missing at the start?

I get an error when starting with

; WITH ...

?

Thanks