日期范围之间的财政年度报税表

Question

也许我想得太多了，但我有点困惑。

我试图按月、按财政年度汇总数据，每个财政年度返回一行，每个月有一列。

我每月可以很容易地返回一列，然而，试图返回财政年度，因为行证明是困难的部分。

我的理论是，一旦我选择了连续的财政年度，我就可以把它加入到一个按财政年度/月汇总的子查询中，给我1 /1。

麻烦的是从8月1日开始的财政年度。查询的日期范围可以从1个财政年度到可能跨越5年的日期范围不等。

例如，如果我的日期为01/08/2013-31/07/2014，我希望该财政年度返回如下：

2013/2014

如果我的日期为01/08/2012-31/07/2014，我希望将下列日期作为财政年度返回：

2012/2013 
2013/2014

如果我的日期为01/01/2012-28/08/2014，我希望将下列日期作为财政年度返回：

2011/2012
2012/2013
2013/2014
2014/2015

到目前为止，这是我的工作，但不像预期的那样工作。

  DECLARE @DateFrom datetime, @DateTo datetime
SET @DateFrom = '2011-08-01'
set @DateTo = '2014-07-31'


; with  FinYr as
        (
        select  @DateFrom as AllDate, @DAteTo as EndDate, case when datepart(MONTH, @DateFrom) < 8 then convert(varchar(4),datepart(year, @DateFrom)-1)+'/'+convert(varchar(4),datepart(year, @DateFrom)) else convert(varchar(4),datepart(year, @DateFrom))+'/'+convert(varchar(4),datepart(year, @DateFrom)+1) End as FinYear
        union all
        select  dateadd(year,1,AllDate) as AllDate, dateadd(year,1,EndDate) as EndDate,case when datepart(MONTH, dateadd(year,1,AllDate)) < 8 then convert(varchar(4),datepart(year, dateadd(year,1,AllDate))-1)+'/'+convert(varchar(4),datepart(year, dateadd(year,1,AllDate))) else convert(varchar(4),datepart(year, dateadd(year,1,AllDate)))+'/'+convert(varchar(4),datepart(year, dateadd(year,1,AllDate))+1) End as FinYear
        from    FinYr
        where   dateadd(year,-1,EndDate) <= convert(datetime,(convert(varchar(4),year(@Dateto))+'-07-31'))
        )
select  AllDate,EndDate, FinYear
from    FinYr

Answer 1

您可以使用理货表或数字表进行类似的操作。在我的代码中，我创建了动态计数表。这是一种零读取类型的implementation...super快速。

DECLARE @DateFrom datetime, @DateTo datetime;
SET @DateFrom = '2011-08-01';
set @DateTo = '2014-07-31';

WITH
    E1(N) AS (select 1 from (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n)),
    E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
    E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
    cteTally(N) AS 
    (
        SELECT  ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
    )

select CAST(N as CHAR(4)) + '/' +  CAST(N + 1 as CHAR(4))
from cteTally
where N >= DATEPART(YEAR, @DateFrom)
and N <= DATEPART(YEAR, @DateTo);

通过更新的需求，您只需稍微修改一下上面的代码就可以完成这一任务。这应该能带你过去。

select CAST(N as CHAR(4)) + '/' +  CAST(N + 1 as CHAR(4))
from cteTally
where N >= case when DATEPART(MONTH, @DateFrom) > 6 then DATEPART(YEAR, @DateFrom) else DATEPART(YEAR, DATEADD(YEAR, 1, @DateFrom)) end
and N <= case when DATEPART(MONTH, @DateTo) < 6 then DATEPART(YEAR, @DateTo) else DATEPART(YEAR, DATEADD(YEAR, 1, @DateTo)) end

我想我看到了我做错了什么，there....try。

select CAST(N as CHAR(4)) + '/' +  CAST(N + 1 as CHAR(4))
, case when DATEPART(MONTH, @DateTo) < 8 then DATEPART(YEAR, DATEADD(YEAR, -1, @DateTo)) else DATEPART(YEAR, DATEADD(YEAR, 1, @DateTo)) end
from cteTally
where N >= case when DATEPART(MONTH, @DateFrom) > 6 then DATEPART(YEAR, @DateFrom) else DATEPART(YEAR, DATEADD(YEAR, 1, @DateFrom)) end
and N <= case when DATEPART(MONTH, @DateTo) < 8 then DATEPART(YEAR, DATEADD(YEAR, -1, @DateTo)) else DATEPART(YEAR, DATEADD(YEAR, 1, @DateTo)) end

Answer 2

我相信这会显示出你想要的年表：

DECLARE 
    @DateFrom datetime, 
    @DateTo   datetime
SET @DateFrom = '2011-01-01'
SET @DateTo = '2014-07-31'


SELECT CAST(YearList AS VARCHAR(4)) + '/' + CAST((YearList +1) AS VARCHAR(4)) AS FiscalYear
FROM
  (
    SELECT 
        DATEPART(YEAR, @DateFrom) - CASE WHEN DATEPART(MONTH, @DateFrom) >= 8 THEN 0 ELSE 1 END + 
        ROW_NUMBER() OVER (PARTITION BY 1 ORDER BY (SELECT NULL)) -1 AS YearList
    FROM sys.all_objects
  ) q
WHERE YearList <= DATEPART(YEAR, @DateTo) - CASE WHEN DATEPART(MONTH, @DateTo) >= 8 THEN 0 ELSE 1 END