Hi, I figured it out with dense_rank.

full code is

SET NOCOUNT ON;

declare @LY datetime,

@ty datetime,

@FD datetime,

@TY_DATE datetime,

@LY_DATE datetime,

@FD_DATE datetime,

@Use_date datetime,

@imp int,

@counter int,

@S_date date

/* this is where I calcuate the period numbers based on the financial year start date (the date CHANGES EACH YEAR) :( */

select @LY = dbo.datemaker(datepart(year,getdate())-2, 9, 1)

select @ty = dbo.datemaker(datepart(year,getdate())-1, 9, 1)

Select @FD = dbo.datemaker(datepart(year,getdate()), 9, 1)

select @LY_DATE = dateadd(mm,datediff(mm,'',@LY),'') - datepart(dw,dateadd(mm,datediff(mm,'',@LY),'')+0)+ 22

select @TY_DATE = dateadd(mm,datediff(mm,'',@ty),'') - datepart(dw,dateadd(mm,datediff(mm,'',@ty),'')+0)+ 22

select @FD_DATE = dateadd(mm,datediff(mm,'',@FD),'') - datepart(dw,dateadd(mm,datediff(mm,'',@FD),'')+0)+ 22

select @use_date = case when(convert(date,getdate()) >= @FD_DATE) then @TY_DATE ELSE @LY_DATE END

select @imp = 1

select @S_date = @Use_date

select @counter = 1;

DECLARE @FIRSTDATE DATETIME = @USE_DATE;

DECLARE @ENDDATE DATETIME = DATEADD(YEAR,2,@FIRSTDATE);

DECLARE @SAMPLE_SIZE BIGINT = DATEDIFF(DAY,@FIRSTDATE,@ENDDATE);

;WITH T(N) AS (SELECT X.N FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) X(N))

, NUMS(N) AS (SELECT TOP(@SAMPLE_SIZE) ROW_NUMBER() OVER (ORDER BY @@VERSION) -1 AS N

FROM T T1,T T2,T T3

ORDER BY N)

,DATE_SERIES AS

(

SELECT

NM.N

,DATEADD(DAY,NM.N,@FIRSTDATE) AS TDATE

FROM NUMS NM

)

,ADJUSTMENT_DAYS AS

(

SELECT (DATEDIFF(DAY,0,@FIRSTDATE) % 28) AS ADJ_DAYS

)

SELECT

DS.TDATE

,DENSE_RANK() OVER (ORDER BY ((DATEDIFF(DAY,0,DS.TDATE) - AD.ADJ_DAYS) /28)) AS[Period]

FROM DATE_SERIES DS

CROSS APPLY ADJUSTMENT_DAYS AD

;

Thanks for the help.

Dave