UFN_DESIGNATIONREPORT_GETPERIODS
This function returns periods for the designation reports.
Return
| Return Type |
|---|
| table |
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @START | datetime | IN | |
| @END | datetime | IN | |
| @PERIODOPTION | tinyint | IN |
Definition
Copy
CREATE function dbo.UFN_DESIGNATIONREPORT_GETPERIODS
(@START datetime, @END datetime, @PERIODOPTION tinyint)
returns @PERIODINFO table
(
SEQUENCE int not null,
PERIODYEAR int not null,
STARTDATE datetime not null primary key clustered,
ENDDATE datetime not null
)
as
begin
select @START = dbo.UFN_DATE_GETEARLIESTTIME(@START),
@END = dbo.UFN_DATE_GETLATESTTIME(@END);
With PERIODS_CTE (SEQUENCE, PERIODYEAR, STARTDATE, ENDDATE)
as (select case @PERIODOPTION when 1 then datepart(mm, @START)
when 2 then datepart(qq, @START)
else 1 end,
datepart(yyyy, @START),
@START,
case @PERIODOPTION when 1 then
case when @END < dateadd(dd, -1, dateadd(mm, 1, @START + 1 - day(@START)))
then @END
else dateadd(dd, -1, dateadd(mm, 1, @START + 1 - day(@START)))
end
when 2 then
case when @END < dateadd(ms, -3, dateadd(qq,datediff(qq, 0,@START)+ 1, 0))
then @END
else
dateadd(ms, -3, dateadd(qq,datediff(qq, 0,@START)+ 1, 0))
end
else
case when @END < dateadd(ms, -3, dateadd(yyyy,datediff(yyyy, 0,@START)+ 1, 0))
then @END
else dateadd(ms, -3, dateadd(yyyy, datediff(yyyy, 0,@START)+ 1, 0))
end
end
union all
select case @PERIODOPTION when 1 then datepart(mm, dateadd(dd, 1, p.enddate))
when 2 then datepart(qq, dateadd(dd, 1, p.enddate))
else datediff(yyyy, @START, dateadd(dd, 1, p.enddate)) + 1 end,
datepart(yyyy, dateadd(dd, 1, p.enddate)),
dateadd(dd, 1, p.enddate),
case @PERIODOPTION when 1 then
CASE WHEN @END < dateadd(dd, -1, dateadd(mm, 1, dateadd(dd, 1, p.enddate) + 1 - day(dateadd(dd, 1, p.enddate))))
THEN @END
ELSE dateadd(dd, -1, dateadd(mm, 1, dateadd(dd, 1, p.enddate) + 1 - day(dateadd(dd, 1, p.enddate))))
END
when 2 then
CASE WHEN @END < dateadd(ms, -3, dateadd(qq,datediff(qq, 0,dateadd(dd, 1, p.enddate))+ 1, 0))
THEN @END
ELSE dateadd(ms, -3, dateadd(qq,datediff(qq, 0,dateadd(dd, 1, p.enddate))+ 1, 0))
END
else
CASE WHEN @END < dateadd(dd, -1, dateadd(yyyy, 1, dateadd(dd, 1, p.enddate) + 1 - day(dateadd(dd, 1, p.enddate))))
THEN @END
ELSE dateadd(dd, -1, dateadd(yyyy, 1, dateadd(dd, 1, p.enddate) + 1 - day(dateadd(dd, 1, p.enddate))))
END
end
from PERIODS_CTE p
where dateadd(dd, 1, p.enddate) <= @END
)
insert @PERIODINFO
select SEQUENCE, PERIODYEAR, dbo.UFN_DATE_GETEARLIESTTIME(STARTDATE), dbo.UFN_DATE_GETLATESTTIME(ENDDATE)
from PERIODS_CTE
option (MAXRECURSION 0);
return
end