从日期范围
之间的财务年度返回清单也许我正在克服这一点,但我有点难住。从日期范围
我试图按财政年度按月归总数据,每个财政年度返回一行,每个月都有一列。
我可以轻松地每月返回一列,但是试图返回财政年度,因为行被证明是最困难的部分。
我的理论是,一旦我有选择作为行财政年度,我可以简单地加入到这个金融年/月总结出了子查询,给我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
您可以使用一个理货或数字表来这样的事情。在我的代码中,我创建了Tally表。这是一个零读取类型的实现...超快速。
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
我想我看到我在那里做错了....试试这个。
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
它产生的正是你所说的你想要的输出。什么是不正确的呢?如果你让我知道什么是不正确的,我可以修复它。 – 2014-08-28 14:47:27
嗯似乎很接近... 如果我把日期范围 '2013-08-01' - '2014-08-20' 然后它返回2013/2014和2014/2015预期。 如果我把在: “2013-08-01'-‘2014年7月30日’ 它仍然返回2013/2014和2014/2015凡在那种情况下,它只应该reuturn 2013/2014 – OWSam 2014-08-28 14:47:33
为什么输出变化? 7月1日您的财务年度有变化吗?他们总是在7月1日改变?如果开关日期是可变的,那么你应该使用日历表。 – 2014-08-28 14:48:51
我相信,这将显示出你想要的几年名单:
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
感谢您的回答,但与其他答案一样,我得到了太多的一年。截至2013年7月31日的选择只应归还2010/2011,2011/2012,2012/2013和2013/2014,因为31/07/2014是2013/2014财政年度的最后一天。 我实际上得到2010/2011,2011/2012,2012/2013,2013/2014和2014/2015 – OWSam 2014-08-28 15:14:56
我使用cte的原因是因为它可以让您对这些值进行一些控制。 sys.all_objects可能是一个很小的数字,它可能没有足够的行数。 – 2014-08-28 15:23:39
对不起@OWSam,我的浏览器没有弹出您的消息,我没有及时收到它来处理它。无论它值什么,我都编辑过。肖恩,我认为CTE是同样的事情,但考虑到OP中RDBMS规范的缺乏以及实际可回收的财政年数可能较少的情况,简单起见就很简单了。 – AHiggins 2014-08-28 16:07:34
你能后的DDL和FinYr一些示例数据? – 2014-08-28 14:10:48
FinYr不是来自dbase的数据,它是一个递归函数。 – OWSam 2014-08-28 14:14:15
哦好悲伤....我需要更多的咖啡统计! – 2014-08-28 14:18:31