sách gpt4 ai đã đi

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

In lại 作者:行者123 更新时间:2023-12-03 02:34:23 34 4
mua khóa gpt4 Nike

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

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

lựa chọn 
trường hợp
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%')

(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 Câu trả lời

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

lựa chọn 
trường hợp
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)

lựa chọn
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 是否包含时间信息,该方法都有效。这对您的查询并不重要,但对于保持一致性来说是一个很好的做法。

关于sql - SQL Server SQL 语句中的动态日期,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/4754507/

34 4 0
Bài viết được đề xuất: sql - MSSQL 2008 中的行合并,
Bài viết được đề xuất: javascript - 使用 javascript 在 html 中制作一个重定向按钮
Bài viết được đề xuất: javascript - 使用ajax和php更新数据库
Bài viết được đề xuất: SQL Server - 计算数据集中属性更改的次数(非并发)
行者123
Hồ sơ cá nhân

Tôi là một lập trình viên xuất sắc, rất giỏi!

Nhận phiếu giảm giá Didi Taxi miễn phí
Mã giảm giá Didi Taxi
Giấy chứng nhận ICP Bắc Kinh số 000000
Hợp tác quảng cáo: 1813099741@qq.com 6ren.com