Hi

Another way without the UNION

Create table #x1

(

iSchoolCode int,

iSchoolYearCode int,

dtCalendarDate datetime,

iCalendarDayId int

)

insert into #x1 values (301,2012,'2012-08-10 00:00:00.000',NULL)

insert into #x1 values (301,2012,'2012-08-11 00:00:00.000',NULL)

insert into #x1 values (301,2012,'2012-08-12 00:00:00.000',NULL)

insert into #x1 values (301,2012,'2012-08-13 00:00:00.000',4259)

insert into #x1 values (301,2012,'2012-08-14 00:00:00.000',4260)

insert into #x1 values (301,2012,'2012-08-15 00:00:00.000',4261)

insert into #x1 values (301,2012,'2012-08-16 00:00:00.000',4262)

insert into #x1 values (301,2012,'2012-08-17 00:00:00.000',NULL)

insert into #x1 values (301,2012,'2012-08-18 00:00:00.000',NULL)

insert into #x1 values (301,2012,'2012-08-19 00:00:00.000',4263)

insert into #x1 values (301,2012,'2012-08-20 00:00:00.000',NULL)

insert into #x1 values (301,2012,'2012-08-21 00:00:00.000',4264)

insert into #x1 values (301,2012,'2012-08-22 00:00:00.000',4265)

insert into #x1 values (301,2012,'2012-08-23 00:00:00.000',4266)

SELECT *

FROM

#x1 X1

LEFT JOIN

(

SELECT

iCalendarDayId

,ROW_NUMBER() OVER ( ORDER BY dtCalendarDate ) RowNum

FROM #x1

WHERE

iCalendarDayId

IS NOT NULL

) AS Row

ON x1.iCalendarDayId = Row.iCalendarDayId

drop table #x1

Cheers

Andy