代码之家 › 专栏 › 技术社区 › NoBullMan

返回会计年度和季度的SQL查询

sql-server-2016 date sql-server sql

NoBullMan · 技术社区 · 8 年前

有一张表列出了四个年度季度的每个季度及其开始/结束日期和月份。

给定此表和起始年份,例如2017年,如何以(结束)日期和名称格式获取季度列表,如2017年第3季度、“2017年6月30日”?

我试过了,但只得到了当前季度:

select q.QID, q.QuarterName, q.StartMonth, q.StartDay, q.EndMonth, q.EndDay,
datefromparts(datepart (yy, '1/1/2017'), q.StartMonth, q.StartDay) StartQuarter, 
datefromparts(datepart (yy, '6/28/2018'), q.EndMonth, q.EndDay) EndQuarter 
from Quarters q 
where datefromparts(datepart (yy, '6/28/2018'), q.EndMonth, q.EndDay) < '06/30/2018';

“四分之一”的桌子看起来像:

QID QuarterName     StartMonth  StartDay    EndMonth    EndDay
1   First Quarter   10          1           12          31
2   Second Quarter  1           1           3           31
3   Third Quarter   4           1           6           30
4   Fourth Quarter  7           1           9           30

不确定这是否相关,但它在.net/c环境中使用。

3 回复 | 直到 8 年前

Salman Arshad 8 年前

试试这个:

DECLARE @year INT = 2017;

WITH numbers(n) AS (
  SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1
  FROM       (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) AS v1(x)
  CROSS JOIN (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) AS v2(x)
)
SELECT CONCAT('Q', QID, ' ', @year + n) AS QuarterName, EndDate
FROM numbers
CROSS JOIN Quarters
CROSS APPLY (SELECT
  DATEFROMPARTS(@year + n - CASE WHEN QID = 1 THEN 1 ELSE 0 END, StartMonth, StartDay) AS StartDate,
  DATEFROMPARTS(@year + n - CASE WHEN QID = 1 THEN 1 ELSE 0 END, EndMonth, EndDay) AS EndDate
) AS CA
WHERE StartDate <= CURRENT_TIMESTAMP ORDER BY EndDate

这个 numbers 公共表表达式生成0到99之间的数字( see this question )中。它们被交叉乘以 Quarters 桌子。日期是通过添加 @year 每年第一季度起调整 n 属于 n - 1 ^第一年。交叉应用使在内部引用计算值更容易 WHERE 我是说, SELECT 和 ORDER BY 条款。

结果:

| QuarterName | EndDate    |
|-------------|------------|
| Q1 2017     | 2016-12-31 |
| Q2 2017     | 2017-03-31 |
| Q3 2017     | 2017-06-30 |
| Q4 2017     | 2017-09-30 |
| Q1 2018     | 2017-12-31 |
| Q2 2018     | 2018-03-31 |
| Q3 2018     | 2018-06-30 |

SQL Fiddle 稍加修改

S3S 8 年前

这似乎是你想要的。您可以使用递归CTE并忽略此表来执行此操作多年…

declare @quarters table (QID int, QuarterName varchar(64), StartMonth int,  StartDay int,  EndMonth int,    EndDay int)
insert into @quarters
values

(1,'First Quarter',10,1,12,31),
(2,'Second Quarter',1,1,3,31),
(3,'Third Quarter',4,1,6,30),
(4,'Fourth Quarter',7,1,9,30)


declare @year int = '2018'

select
    *
    ,convert(varchar,convert(date,cast(@year as varchar) + right('0' + cast(EndMonth as varchar),2)  + cast(EndDay as varchar)),101) 
from @quarters

或者,递归CTE…

;with cte as(
    select 
        *
        ,EndDate = convert(date,convert(date,cast(datepart(year,@startDate) as varchar) + right('0' + cast(EndMonth as varchar),2)  + cast(EndDay as varchar)))
    from @quarters
    union all
    select
        Qid
        ,QuarterName
        ,StartMonth
        ,StartDay
        ,EndMonth
        ,EndDay
        ,dateadd(year,1,EndDate)
    from cte
    where datepart(year,EndDate) <= datepart(year,@endDate)
    )

select
        Qid
        ,QuarterName
        ,StartMonth
        ,StartDay
        ,EndMonth
        ,EndDay
        ,EndDate = convert(varchar,EndDate,101)
from cte

John Cappelletti 8 年前

也许另一个选择是因为你的财政年度是一个季度

例子

Declare @Date1 date = '2017-10-01'

Select DateR1 = D
      ,DateR2 = EOMONTH(D)
      ,AsString = concat(choose(DatePart(QQ,D),'Q2','Q3','Q4','Q1'),' ',year(D)-IIF(DatePart(QQ,D)<4,1,0) )
      ,AsDate   = format(EOMonth(D),'MM/dd/yyyy')
From (
        Select Top (12) D=DateAdd(MONTH,-1+Row_Number() Over (Order By (Select Null)),@Date1) From  master..spt_values n1,master..spt_values n2
     ) A

退换商品

DateR1      DateR2      AsString    AsDate
2017-10-01  2017-10-31  Q1 2017     10/31/2017
2017-11-01  2017-11-30  Q1 2017     11/30/2017
2017-12-01  2017-12-31  Q1 2017     12/31/2017
2018-01-01  2018-01-31  Q2 2017     01/31/2018
2018-02-01  2018-02-28  Q2 2017     02/28/2018
2018-03-01  2018-03-31  Q2 2017     03/31/2018
2018-04-01  2018-04-30  Q3 2017     04/30/2018
2018-05-01  2018-05-31  Q3 2017     05/31/2018
2018-06-01  2018-06-30  Q3 2017     06/30/2018
2018-07-01  2018-07-31  Q4 2017     07/31/2018
2018-08-01  2018-08-31  Q4 2017     08/31/2018
2018-09-01  2018-09-30  Q4 2017     09/30/2018