产生财务计帐周期

 

先看看实现的结果，可以by月份和季度，可以调整会计开始日期。

 

前端略去，只分享ms sql存储过程：

 

set ansi_nulls on
go
set quoted_identifier on
go
-- =============================================
-- author:      insus.net
-- blog:        https://insus.cnblogs.com
-- create date: 2019-07-02
-- update date: 2019-07-02
-- description: 动态产生会计周期
-- =============================================
create procedure [dbo].[usp_accountingperiod_select_generateperioddate]
(
    @fiscal_year smallint,
    @perioddatetype nvarchar(1),
    @start1 datetime
)
as
begin
    if object_id('#period_date') is not null
        drop table #period_date
    
    create table #period_date(
        [fiscal_year] [smallint] not null,
        [start1] [datetime] null,
        [start2] [datetime] null,
        [start3] [datetime] null,
        [start4] [datetime] null,
        [start5] [datetime] null,
        [start6] [datetime] null,
        [start7] [datetime] null,
        [start8] [datetime] null,
        [start9] [datetime] null,
        [start10] [datetime] null,
        [start11] [datetime] null,
        [start12] [datetime] null,
        [start13] [datetime] null,
        [end1] [datetime] null,
        [end2] [datetime] null,
        [end3] [datetime] null,
        [end4] [datetime] null,
        [end5] [datetime] null,
        [end6] [datetime] null,
        [end7] [datetime] null,
        [end8] [datetime] null,
        [end9] [datetime] null,
        [end10] [datetime] null,
        [end11] [datetime] null,
        [end12] [datetime] null,
        [end13] [datetime] null
    )    


    if @fiscal_year >= year([dbo].[svf_lowdate]()) and @fiscal_year < year([dbo].[svf_highdate]())
    begin
        if @start1 is null        
            set @start1 = convert(datetime, convert(nvarchar(4),@fiscal_year) + '-01-01', 121)           
        insert into  #period_date ([fiscal_year]) values (@fiscal_year)    

        if @perioddatetype = n'm'
        begin
            declare @m tinyint = 1,@ms tinyint = 12
            while @m <= @ms
            begin
                declare @m_start_field nvarchar(128) = n'[start'+ convert(nvarchar(2), @m) +']'
                declare @m_start_value datetime = dateadd(m,@m -1,@start1)    
                
                declare @m_end_field nvarchar(128) = n'[end'+ convert(nvarchar(2), @m) +']'
                declare @m_end_value datetime = dateadd(day,-1, dateadd(m,@m,@start1))    
                
                declare @s_sql nvarchar(4000) = n'
                update #period_date set '+ @m_start_field +' = '''+ convert(nvarchar(40), @m_start_value ) +''',
                                        '+ @m_end_field +' = '''+ convert(nvarchar(40), @m_end_value ) +''' 
                where [fiscal_year] = '''+ convert(nvarchar(4),@fiscal_year) +''''
                execute sp_executesql @s_sql
                set @m = @m + 1
            end
        end

        if  @perioddatetype = n'q'
        begin
        declare @q tinyint = 1,@qs tinyint = 4
            while @q <= @qs
            begin
                declare @q_start_field nvarchar(128) = n'[start'+ convert(nvarchar(2), @q) +']'
                declare @q_start_value datetime = dateadd(quarter,@q -1,@start1)    
                
                declare @q_end_field nvarchar(128) = n'[end'+ convert(nvarchar(2), @q) +']'
                declare @q_end_value datetime = dateadd(day,-1, dateadd(quarter,@q,@start1))    
                
                declare @q_s_sql nvarchar(4000) = n'
                update #period_date set '+ @q_start_field +' = '''+ convert(nvarchar(40), @q_start_value ) +''',
                                        '+ @q_end_field +' = '''+ convert(nvarchar(40), @q_end_value ) +''' 
                where [fiscal_year] = '''+ convert(nvarchar(4),@fiscal_year) +''''
                execute sp_executesql @q_s_sql
                set @q = @q + 1
            end
        end
    end

    select [fiscal_year],
            [start1],[start2],[start3],[start4],
            [start5],[start6],[start7],[start8],
            [start9],[start10],[start11],[start12],
            [start13],
            [end1],[end2],[end3],[end4],
            [end5],[end6],[end7],[end8],
            [end9],[end10],[end11],[end12],
            [end13] 
    from #period_date

end