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