sql - SQL Server SQL 语句中的动态日期

Question

预先感谢您对此提供的任何帮助。

假设我有一个查询，可以比较跨年的数据，从某个任意年份开始，永无止境(进入 future )，每年同一时期直到最后一个完整的月份(其特点是一月数据永远不会显示至 2 月 1 日)。还说不能使用 T-SQL。有没有办法重新编写以下查询，以动态生成从 2008/01/01 开始的日期(甚至只是在所有年份中执行)并永远持续下去，而无需任何硬编码？

select 
       case
         when oact.fathernum like '112%' then sum(jdt1.debit) - sum(jdt1.credit)
       end as [Accounts Receivable],
       jdt1.refdate as [Posting Date]
from jdt1
  inner join oact on jdt1.account = oact.AcctCode
where (oact.fathernum like '1%')
          and 
          (jdt1.refdate between '2008/01/01' and dateadd(day, -1, '2008/' + cast(month(getdate()) as varchar(2)) + '/01')
           or jdt1.refdate between '2009/01/01' and dateadd(day, -1, '2009/' + cast(month(getdate()) as varchar(2)) + '/01')
           or jdt1.refdate between '2010/01/01' and dateadd(day, -1, '2010/' + cast(month(getdate())  as varchar(2)) + '/01')
           or jdt1.refdate between '2011/01/01' and dateadd(day, -1, '2011/' + cast(month(getdate())  as varchar(2)) + '/01')
           or jdt1.refdate between '2012/01/01' and dateadd(day, -1, '2012/' + cast(month(getdate())  as varchar(2)) + '/01')
           or jdt1.refdate between '2013/01/01' and dateadd(day, -1, '2013/' + cast(month(getdate())  as varchar(2)) + '/01')
           or jdt1.refdate between '2014/01/01' and dateadd(day, -1, '2014/' + cast(month(getdate())  as varchar(2)) + '/01')
           or jdt1.refdate between '2015/01/01' and dateadd(day, -1, '2015/' + cast(month(getdate())  as varchar(2)) + '/01')
           or jdt1.refdate between '2016/01/01' and dateadd(day, -1, '2016/' + cast(month(getdate())  as varchar(2)) + '/01')
           or jdt1.refdate between '2017/01/01' and dateadd(day, -1, '2017/' + cast(month(getdate())  as varchar(2)) + '/01'))

group by oact.fathernum, jdt1.refdate

如果做不到这一点，有人愿意尝试在存储过程中使用 T-SQL 重新制定来解决问题吗？只要日期上限是动态的，日期上限就可以始终是当前年份。

Answer 1

下面的 TSQL 显示了构建动态日历表的方法。所示的查询会更改每年的枢轴日期，但进一步显示如何将日历“开始”日期固定在特定年份。

select 
       case
         when oact.fathernum like '112%' then sum(jdt1.debit) - sum(jdt1.credit)
       end as [Accounts Receivable],
       jdt1.refdate as [Posting Date]
from jdt1
inner join oact on jdt1.account = oact.AcctCode

inner join (select
    FirstDayOfYear  =DATEADD(m,datediff(m,0,getdate())-MONTH(getdate())+1,0),
    FirstDayOfMonth =DATEADD(m,datediff(m,0,getdate()),0)) D
inner join master..spt_values v on v.type='P'
    and v.number between 0 and 500 -- is 500 years enough? max=2047 from this table
  on jdt1.refdate >= DATEADD(year,v.number,D.FirstDayOfYear)
     and jdt1.refdate < DATEADD(year,v.number,D.FirstDayOfMonth)

where (oact.fathernum like '1%')
group by oact.fathernum, jdt1.refdate

该选择创建一个包含 2 个基准日期的单个记录，如命名的

inner join (select
    FirstDayOfYear  =DATEADD(m,datediff(m,0,getdate())-MONTH(getdate())+1,0),
    FirstDayOfMonth =DATEADD(m,datediff(m,0,getdate()),0)) D

2 个基准日期是**当前年份** 的第一天，以及当前月份(也是当前年份)的第一天。如果您需要**特定** 年的第一天和该月(当月)的第一天，但​​在同一特定年份，您可以使用下面的变体(例如 2008 年 1 月) -01)

select
    FirstDayOfYear  =cast('20080101' as datetime),
    FirstDayOfMonth =dateadd(m,month(getdate())-1,'20080101')

这使用基准日期和内置数字序列每次向基准日期逐渐添加 1 年，从添加 0(当前年份)开始。

inner join master..spt_values v on v.type='P'
    and v.number between 0 and 500
  on jdt1.refdate >= DATEADD(year,v.number,D.FirstDayOfYear)
     and jdt1.refdate < DATEADD(year,v.number,D.FirstDayOfMonth)

另请注意，而不是

date between A and B

我通常更喜欢

date >= A and date < B+1

无论 B 是否包含时间信息，该方法都有效。这对您的查询并不重要，但对于保持一致性来说是一个很好的做法。