Hi again,

Finally managed to get my head round this and it works excellently with the one pupil in it but as soon as you try using it with more than...

Sorry that was for the SQL just before the last script was added. I took a comma out and it brought back some data but there isnt a running total...

Im still going to keep trying with it but Im just getting an error at the moment

Msg 156, Level 15, State 1, Line 24

Incorrect syntax near the keyword 'from'.

Msg 156,...

Is this any good?

/****** Object: Table [dbo].[ECAFALL] Script Date: 10/14/2008 11:10:05 ******/

CREATE TABLE [dbo].[ECAFALL](

[Person] [int] NULL,

[TYPE] [varchar](16) COLLATE Latin1_General_CI_AS NULL,

[id] [numeric](9, 0) NOT NULL,

[START_DATE] [datetime] NULL,

[END_DATE]...

Still just populating the column with 1's for everything.

The site isnt allowing me to look at the article about running totals either. an error has occured when I click...

I think Im doing something very wrong.... :crazy:

I got the first example to bring back data but I dont understand the results. The 2nd example worked also but It just...

Fantastic,

I will try these out this morning and let you know how I get on!

Thats exactly what I was thinking....

But I would want it to reset for each person every time it got to the next ECAFST

118007ECAFSTD5028582007-09-102007-09-10 1

118007ECAFMTG5033412007-12-11 2007-12-11 ...

I will see what I can do,

Just got to go to a meeting now but I will try and get it sorted straight after!!!

I just aded a row identifier...

Ah ha... Thanks for that. Yes it is set to 80!

I dont know anything about compatibility settings.....??? :doze:

SELECT cs.tblPUPIL, cs.tblSCHOOL, tmp.TOTAL

INTO #test

FROM dbo.vtblPupil_CatchSchool cs

INNER JOIN #COUNT_OF_PUPILS tmp

ON cs.tblPUPIL = tmp.tblPUPIL

ORDER BY cs.tblPUPIL

SELECT tblPUPIL,tblSCHOOL,Total,

case Total when 1 then 1 else Row_Number()

OVER(PARTITION BY Total ORDER BY...

Im being told ROW_NUMBER is not a recognised function name and Im hoping to stay clear of partitioning for this particular problem.

Mainly because its a bit beyond me :w00t:

Thanks for that,

Ill have a look at that too! 🙂

Sorted.

I went a completely different route

UPDATE Census_Access_Status_DB

SET Older_Version_Exists = 1

WHERE FileName IN

(SELECT a.Filename

FROM dbo.Census_Access_Status_DB a

INNER JOIN Census_Access_Status_DB b

--Join on the filename without the file version

ON Substring(a.FileName,1,19)=Substring(b.FileName,1,19)

LEFT OUTER JOIN...