logo标签列表

SQL Server中动态日期的SQL语句

sqlsql-server
4

提前感谢您对此的任何帮助。

假设我有一个查询，它比较了跨越多年的数据，从某个任意年份开始并永不结束（一直延伸到未来），每年的同一时期直到上一个已完成的月份（1月份的数据直到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重新表达并解决这个问题吗？只要日期上限是动态的，它总可以是当前年份。

1
这是 SQL Server 2005 或以上版本吗？ - RichardTheKiwi
4个回答
2

从数字表开始生成数据集并进行连接

这个SO问题是针对日序列的

同时：https://dev59.com/EXM_5IYBdhLWcg3wNgOZ - OMG Ponies
2
以下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

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

两个基准日期是当前年份的第一天和当前月份的第一天（同样在当前年份）。如果您需要特定年份的第一天和当前月份的第一天（同样在该特定年份），您可以使用以下变体（以2008年1月1日为例）。

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是否包含时间信息，该方法都有效。对于您的查询来说这不重要，但保持一致性是个好习惯。请注意，保留HTML标签。
很不错，谢谢！能看到你如何处理那个问题真棒，是的，500年应该足够了 ; ) - m7d
抱歉我之前打错了你的昵称，我习惯性地看到的是“wiki”而不是“kiwi”。 - m7d
@cyberkiwi - 这是一个不错的解决方案，我可以看到它有很多用途，但是否有一种方法可以使它始终保持从2008年开始而不是随着每个新年而前进呢？我真的想要从2008年开始。它可以正确比较每年相同的时间段，我只是还没有想出如何修改它以修复第一年为2008年，然后从那里开始比较每个后续年份。换句话说，所有从2008年开始的年份的相同数据期间必须同时预设，例如2008年1月1日至1月21日与2009年1月1日至1月21日等。有什么想法吗？ - m7d
我想出了一个解决方案。我把你的解决方案颠倒过来，从一个足够遥远的未来日期开始向后倒数，并通过在where子句日期加法中反转v.number的符号来倒数。我选择了2099年，这对于这个项目来说更加合适。再次感谢。 - m7d
@m7d - 答案已更新，以保持起始年份不变。 - RichardTheKiwi
1
这样的东西可行吗？
YEAR(jdt1.refdate) between 2008 and 2017
  and
MONTH(jdt1.refdate) < MONTH(getdate())
这对索引使用非常糟糕。 - RichardTheKiwi
它是吗？为什么？而更重要的是，它应该很糟糕吗？ - ypercubeᵀᴹ
因为要找出第一部分，需要在“所有”记录上运行YEAR()函数来返回整数值。一个正确构建的日期范围来测试jdt1.refdate只需要收集1/12的数据。 - RichardTheKiwi
1
如果您正在使用SQL Server 2005+，您可以直接动态构建您的日历：
With MaxDate As
    (
    Select Max(refdate) As [Date]
    From jdt1
    )
    , Calendar As
    (
    Select Cast( Cast(Year(GetDate())As char(4)) + '0101' As datetime ) As [StartDay]
        , DateAdd(d, -1, Cast( Cast(Year(GetDate()) + 1 As char(4)) + '0101' As datetime ) )As [EndDay]
    Union All
    Select DateAdd(yyyy, 1, [StartDay])
        , DateAdd(yyyy, 1, [EndDay])
    From Calendar
        Join MaxDate
            On Year(DateAdd(yyyy, 1, [EndDay])) <= Year(MaxDate.[Date])
    )
Select ...
From Calendar As C
    Join jdt1
        On jdt1.refdate Between C.StartDay And C.EndDay
    Join oact 
        On oact.AcctCode = jdt1.account
Where oct.fathernum Like '%1'
Group By oact.fathernum, jdt1.refdate       
Option ( MaxRecursion 0 );

在这个解决方案中，我从今天的年份开始，一直扩展到最后一个refdate的年份。
抱歉，SO不允许我奖励两个答案。不过没关系，这也是一个不错的解决方案。谢谢！ - m7d
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接