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