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:
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):
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