某个日期附近的价值 [英] value near a certain date

问题描述

嗨


我需要一个查询，它给出了某个日期某列的值..

如果数据库中不存在它需要采取最接近的日期..



目前我有这个

Hi
I need a query that gives me the value of a column on a certain date..
If that does not exist in the DB it needs to take the closest possible date ..

currently I have this

select A, min(B)
from Table1

where gebeurtenis between begindatum and einddatum

group by A

提前感谢！

thanks in advance!

推荐答案

检查下面的示例用tablename替换带下划线的部分

check below example replace underlined portion with tablename

declare @begindatum datetime
set @begindatum = '2013-07-09'
select top 1 A, ABS(DateDiff(d,gebeurtenis,@begindatum)) nearbydays,gebeurtenis
from
(
SELECT 'a' as A, '2013-07-15' gebeurtenis
union all
SELECT 'a' as A, '2013-07-08' gebeurtenis
union all
SELECT 'a' as A, '2013-07-10' gebeurtenis
) as table1
order by  ABS(DateDiff(d,gebeurtenis,@begindatum))

快乐编码！

：）

Happy Coding!
:)

最近可能的日期这里有一个有点模糊的概念：如果它在之前或之后的日期之间是否更接近？



可能你会想做类似的事情：

"Closest possible date" is a slightly nebulous concept here: is it closer if it's before, or after the desired date?

Probably, you are going to want to do something like:

SELECT TOP 1 * FROM myTable WHERE gebeurtenis >= '2013-01-01' ORDER BY gebeurtenis DESC

或

Or

SELECT TOP 1 * FROM myTable WHERE gebeurtenis <= '2013-01-01' ORDER BY gebeurtenis ASC

但是没有好的机制可以同时执行这两个操作。

But there is no good mechanism for doing both at once.

创建一个sql函数CurentorNearestDate并传递这样的代码

create an sql function "CurentorNearestDate" and pass a code like this

select A, min(B)
from Table1

where gebeurtenis = CurentorNearestDate(gebeurtenis) 

group by A

这篇关于某个日期附近的价值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！