May 27, 2018 at 5:25 am
Hi, I am working out some extremely bespoke financial periods for a client and I need to get each date in the range into a table along with its Period Number.
I am having issues with my sub query and the fact I have 2 nested while loops involved.
Essentially I am trying to say:
add 1 to the day counter on each recursion of the inner loop whilst the @Counter <=28 and then each time the counter rises above 28, reset it to 0 and add 1 to @imp which drives the outer loop.
I have got myself in a mess with the loops and am looking for some guidance on how to repair it.
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;
while @imp <= 730 /* I only need 2 years of dates at a time*/
begin
while @counter <=28 /*each period is 28 days long*/
begin
with periods as (
select @use_date as start_date, 1 as lev
union all
select dateadd(day, 1, start_date), case when (@counter <= 28) then lev else lev +1 END
from periods
where start_date < dateadd(year,1,start_date) and
lev <=12)
Insert into dbo.Periods
select
@S_date as Invoice_Date,
right('00' + cast(periods.lev as varchar(255)), 2) as [Period_Num],
case when (@S_Date between @Use_date and DATEADD(day,365,@Use_date)) Then 'LY' ELSE 'TY' END AS [TY_LY]
from periods
OPTION (MAXRECURSION 730)
Set @counter = @counter +1
END
set @counter = 1
set @imp = @imp +1
Set @S_Date = dateadd(day,1,@S_date)
End;
select * from Periods
Cheers
Dave
May 27, 2018 at 5:59 am
Here is a more efficient way of doing this, the code should be self explanatory.
😎
May 27, 2018 at 12:57 pm
May 27, 2018 at 1:42 pm
Eirikur Eiriksson - Sunday, May 27, 2018 5:59 AMHere is a more efficient way of doing this, the code should be self explanatory.
😎
USE TEEST;GOSET NOCOUNT ON;--https://www.sqlservercentral.com/Forums/1961701/Calculating-a-bespoke-period-number-for-each-date-in-a-rangeDECLARE @FIRSTDATE DATETIME = '20180101';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 NFROM T T1,T T2,T T3ORDER BY N),DATE_SERIES AS(SELECTNM.N,DATEADD(DAY,NM.N,@FIRSTDATE) AS TDATEFROM NUMS NM),ADJUSTMENT_DAYS AS(SELECT (DATEDIFF(DAY,0,@FIRSTDATE) % 28) AS ADJ_DAYS)SELECTDS.N,DS.TDATE,((DATEDIFF(DAY,0,DS.TDATE) - AD.ADJ_DAYS) / 28 ) AS GRP_NOFROM DATE_SERIES DSCROSS APPLY ADJUSTMENT_DAYS AD;
Hi, I have it partially working now as I would like but I need GRP_NO to begin at 1 and end at 13 then start at 1 again. Is that possible?
Dave
May 27, 2018 at 1:50 pm
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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply