calculate financial periods into temp table

Question

I have a client who reports on 13 27 day periods in the financial year and I am trying to work out some dynamic SQL to identify what reporting period an invoice was raised in.

This is what I have so far but the while loop is crashing after the first loop.

IF OBJECT_ID('#Periods', 'U') IS NOT NULL
drop table #Periods

create table #Periods
([start_date] date, [end_date] date, Period varchar(3) )

declare @LYdt datetime,
        @CYdt datetime,
        @Period int

SET @Period = 0
SET @LYdt = '09/01/2016'
SET @CYdt = '09/01/2017'
While @Period  <=13

insert #Periods
select 
[Start_Date] = dateadd(mm,datediff(mm,'',@LYdt),'') - datepart(dw,dateadd(mm,datediff(mm,'',@LYdt),'')+0)+ 22,
[End Date] = (dateadd(mm,datediff(mm,'',@LYdt),'') - datepart(dw,dateadd(mm,datediff(mm,'',@LYdt),'')+0)+ 22)+27,
[Period] = 'P'+ convert(varchar(2),@Period)



SET @Period = @Period + 1
SET @LYdt =  dateadd(d,27,@LYdt)
SET @CYdt = dateadd(d,27,@CYdt)

Can anyone assist with where I have gone wrong please?

Dave

addiditonal: sample result set of sql will look like this:

I'm guessing that no one really can help. You need to provide sample data, desired results, and an explanation of what you are doing. "Crashing" after the first loop is not a very precise description. — Gordon Linoff, May 24 '18 at 10:47
I should add that this script is to give me a temp table with start date end date and period number only. — Dave Edmonds, May 24 '18 at 10:50
This is the sort of problem that really makes me instinctively say - date tally table. — Andrew, May 24 '18 at 10:50
No-one reports on 13x 27-day periods per year. Because that's 351 days and a year has either 365 or 366 days. They may have an algorithm they use, if so ***ask them for it***. Or, to simplify life, create a calendar table in your database. Then manually or semi-manually update some meta data on each date *(fiscal year id, fiscal quarter id, fiscal month id, etc, etc)*. Populate it with dates from *(for example)* 2000-01-01 to 2099-12-31, as long as it covers every date your application will ever need. Then you can index the hell out of that table. It will save ***alot*** of complexity. — MatBailie, May 24 '18 at 10:51
Hi Gordy thanks for getting in touch. I terms of my required assistance, what you see here is what you get. I am simply trying to insert some calculated dates into a temp table using a while loop. Apologies if the post title is a little off, my question kind of morphed whilst I was typing it. — Dave Edmonds, May 24 '18 at 10:52
Ok MatBailie this is what I thought too but they are pretty adamant that is their reporting period makeup, who am I to argue with a customer? — Dave Edmonds, May 24 '18 at 10:53
Andrew this is what I am trying to achieve with a temp table so that I don't have to update anything year on year. — Dave Edmonds, May 24 '18 at 10:55
@DaveEdmonds Then get them to give the algorithm or a calendar enumerating all the dates for you. Guess the algorithm yourself is going to lead to pain and suffering. — MatBailie, May 24 '18 at 11:00
@MatBailie I have added their enumerated dates above. Still only comes to 351 days though :) — Dave Edmonds, May 24 '18 at 11:12
@DaveEdmonds - I guarantee that with that scheme some years have 14 weeks. Get the full enumerated list for all years of relevance and copy that list in to your database as a lookup. Don't get bogged down in calculating it. — MatBailie, May 24 '18 at 11:29
Also, the dates given are 13 periods of 28 days *(Mondays to Sundays, inclusive)*, for a total of 364 days. Meaning that every *(roughly)* five or six years, a 14th period will be needed to make up for the deficiency. — MatBailie, May 24 '18 at 11:33

Gordon Linoff · Answer 1 · 2018-05-24T12:11:17.097

If you are trying to generate periods between two dates, you can use a recursive CTE:

with periods as (
      select cast('2016-09-01' as date) as start_date, 1 as lev
      union all
      select dateadd(day, 27, start_date), lev + 1
      from periods
      where start_date < '2017-09-01'
     )
select start_date,
       lead(start_date) over (order by start_date) as end_date,
       'P' + right('00' + cast(lev as varchar(255)), 2) as period_num
from periods;

EDIT:

You can do this just as easily by doing:

with periods as (
      select cast('2016-09-01' as date) as start_date, 1 as lev
      union all
      select dateadd(day, 27, start_date), lev + 1
      from periods
      where start_date < '2017-09-01'
     )
select start_date,
       dateadd(day, 27, start_date) as end_date,
       'P' + right('00' + cast(lev as varchar(255)), 2) as period_num
from periods;

Gordon thanks for this, I should have mentioned,I am on 2008R2 so lead() isn't an option I am afraid. — Dave Edmonds, May 24 '18 at 11:10

Dave Edmonds · Accepted Answer · 2018-05-25T07:43:48.093

despite agreeing with you guys that they need to provide the dates for me to work with, I couldn't let the task beat me so I have finally written up an answer that works.

It combines a scalar function for concatenating dates that I found here (Thanks to Brian for the function):

Create a date with T-SQL

With Gordons Code from above

to get the final product:

declare @LY datetime,
        @TY datetime,
        @FD datetime,
        @TY_DATE datetime,
        @LY_DATE datetime,
        @FD_DATE datetime,
        @Use_date datetime

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;



with periods as (
      select @use_date as start_date, 1 as lev
      union all
      select dateadd(day, 28, start_date), lev + 1
      from periods
      where start_date < dateadd(year,1,start_date) and
      lev <=12)



select start_date as [Start_Date],
       dateadd(day, 27, start_date) as end_date,
       'P' + right('00' + cast(lev as varchar(255)), 2) +'LY' as period_num

from periods

union all

select dateadd(year,1,start_date) as [Start_Date],
       dateadd(year,1,dateadd(day, 27, start_date)) as end_date,
       'P' + right('00' + cast(lev as varchar(255)), 2) +'TY' as period_num



from periods;

CREATE FUNCTION [dbo].[Datemaker]
(
    @Year INT,
    @Month INT,
    @DayOfMonth INT

)
RETURNS DATETIME
AS
BEGIN

    RETURN 
            DATEADD(day, @DayOfMonth - 1, 
            DATEADD(month, @Month - 1, 
            DATEADD(Year, @Year-1900, 0)))

END

GO

Thanks to all of you for contributing.

Regards,

Dave

calculate financial periods into temp table

2 Answers2