如何在给定日期范围之间获得每个日期的第一个和最后一个值的子句 [英] How do I get substarction of first and last value of each date between a given date range
本文介绍了如何在给定日期范围之间获得每个日期的第一个和最后一个值的子句的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
How do i get substarction of first and last value of each date between a given date range
This is how my table looks like.
2016-09-01 00:00:00.000 2016-09-01 20:20:04.000 410128 1
2016-09-01 00:00:00.000 2016-09-01 20:20:04.000 410128 2
2016-09-01 00:00:00.000 2016-09-01 20:20:48.000 410128 3
2016-09-01 00:00:00.000 2016-09-01 20:21:05.000 410128 4
2016-09-01 00:00:00.000 2016-09-01 20:21:17.000 410128 5
2016-09-01 00:00:00.000 2016-09-01 20:21:31.000 410172 6
2016-09-01 00:00:00.000 2016-09-01 20:21:46.000 410210 7
2016-09-01 00:00:00.000 2016-09-01 20:22:14.000 410374 8
2016-09-01 00:00:00.000 2016-09-01 20:22:33.000 410377 9
2016-09-01 00:00:00.000 2016-09-01 20:22:45.000 410377 10
2016-09-01 00:00:00.000 2016-09-01 20:22:57.000 410406 11
2016-09-01 00:00:00.000 2016-09-01 20:23:14.000 410561 12
2016-09-01 00:00:00.000 2016-09-01 20:23:44.000 410777 13
2016-09-01 00:00:00.000 2016-09-01 20:24:09.000 410951 14
2016-09-01 00:00:00.000 2016-09-01 20:24:09.000 410951 15
2016-09-01 00:00:00.000 2016-09-01 20:24:37.000 410951 16
2016-09-01 00:00:00.000 2016-09-01 20:24:37.000 410951 17
2016-09-01 00:00:00.000 2016-09-01 20:25:14.000 410951 18
2016-09-01 00:00:00.000 2016-09-01 20:25:27.000 410966 19
2016-09-01 00:00:00.000 2016-09-01 20:25:41.000 411024 20
2016-09-01 00:00:00.000 2016-09-01 20:25:53.000 411062 21
2016-09-01 00:00:00.000 2016-09-01 20:26:14.000 411227 22
2016-09-01 00:00:00.000 2016-09-01 20:26:44.000 411503 23
2016-09-01 00:00:00.000 2016-09-01 20:27:14.000 411563 24
2016-09-01 00:00:00.000 2016-09-01 20:27:40.000 411593 25
2016-09-01 00:00:00.000 2016-09-01 20:27:52.000 411623 26
2016-09-01 00:00:00.000 2016-09-01 20:28:14.000 411633 27
2016-09-01 00:00:00.000 2016-09-01 20:28:44.000 411717 28
2016-09-01 00:00:00.000 2016-09-01 20:28:56.000 411717 29
2016-09-01 00:00:00.000 2016-09-01 20:29:14.000 411804 30
2016-09-01 00:00:00.000 2016-09-01 20:29:44.000 411918 31
2016-09-01 00:00:00.000 2016-09-01 20:30:14.000 411945 32
2016-09-01 00:00:00.000 2016-09-01 20:30:44.000 412098 33
2016-09-01 00:00:00.000 2016-09-01 20:31:14.000 412231 34
2016-09-01 00:00:00.000 2016-09-01 20:31:44.000 412310 35
2016-09-01 00:00:00.000 2016-09-01 20:32:00.000 412315 36
2016-09-01 00:00:00.000 2016-09-01 20:32:12.000 412349 37
2016-09-01 00:00:00.000 2016-09-01 20:32:44.000 412351 38
2016-09-01 00:00:00.000 2016-09-01 20:33:14.000 412421 39
2016-09-01 00:00:00.000 2016-09-01 20:33:44.000 412452 40
2016-09-01 00:00:00.000 2016-09-01 20:34:14.000 412486 41
2016-09-01 00:00:00.000 2016-09-01 20:34:44.000 412486 42
2016-09-01 00:00:00.000 2016-09-01 20:35:14.000 412539 43
2016-09-01 00:00:00.000 2016-09-01 20:35:44.000 412539 44
2016-09-01 00:00:00.000 2016-09-01 20:36:14.000 412562 45
2016-09-01 00:00:00.000 2016-09-01 20:36:44.000 412590 46
2016-09-01 00:00:00.000 2016-09-01 20:37:05.000 412677 47
2016-09-01 00:00:00.000 2016-09-01 20:37:18.000 412685 48
2016-09-01 00:00:00.000 2016-09-01 20:37:35.000 412703 49
2016-09-01 00:00:00.000 2016-09-01 20:38:14.000 412774 50
2016-09-01 00:00:00.000 2016-09-01 20:38:44.000 412796 51
2016-09-01 00:00:00.000 2016-09-01 20:39:02.000 412848 52
2016-09-01 00:00:00.000 2016-09-01 20:39:15.000 412913 53
2016-09-01 00:00:00.000 2016-09-01 20:39:44.000 413097 54
2016-09-01 00:00:00.000 2016-09-01 20:40:14.000 413322 55
2016-09-01 00:00:00.000 2016-09-01 20:40:44.000 413335 56
2016-09-01 00:00:00.000 2016-09-01 20:41:14.000 413472 57
2016-09-01 00:00:00.000 2016-09-01 20:41:44.000 413799 58
2016-09-01 00:00:00.000 2016-09-01 20:42:14.000 414034 59
2016-09-01 00:00:00.000 2016-09-01 20:42:44.000 414177 60
2016-09-01 00:00:00.000 2016-09-01 20:43:14.000 414360 61
2016-09-01 00:00:00.000 2016-09-01 20:44:33.000 415233 62
2016-09-01 00:00:00.000 2016-09-01 20:44:46.000 415371 63
2016-09-01 00:00:00.000 2016-09-01 20:45:14.000 415458 64
2016-09-01 00:00:00.000 2016-09-01 20:45:44.000 415458 65
2016-09-01 00:00:00.000 2016-09-01 20:46:14.000 415458 66
2016-09-01 00:00:00.000 2016-09-01 20:46:42.000 415482 67
2016-09-01 00:00:00.000 2016-09-01 20:46:55.000 415558 68
2016-09-01 00:00:00.000 2016-09-01 20:47:14.000 415678 69
2016-09-01 00:00:00.000 2016-09-01 20:47:32.000 415808 70
2016-09-01 00:00:00.000 2016-09-01 20:47:47.000 415915 71
2016-09-01 00:00:00.000 2016-09-01 20:48:14.000 416117 72
2016-09-01 00:00:00.000 2016-09-01 20:48:39.000 416351 73
2016-09-01 00:00:00.000 2016-09-01 20:49:00.000 416526 74
2016-09-01 00:00:00.000 2016-09-01 20:49:15.000 416609 75
2016-09-01 00:00:00.000 2016-09-01 20:49:34.000 416667 76
2016-09-01 00:00:00.000 2016-09-01 20:49:58.000 416669 77
2016-09-01 00:00:00.000 2016-09-01 20:50:14.000 416669 78
2016-09-01 00:00:00.000 2016-09-01 20:50:32.000 416671 79
2016-09-01 00:00:00.000 2016-09-01 20:50:47.000 416693 80
2016-09-01 00:00:00.000 2016-09-01 20:51:14.000 416806 81
2016-09-01 00:00:00.000 2016-09-01 20:51:44.000 416935 82
2016-09-01 00:00:00.000 2016-09-01 20:52:00.000 416973 83
2016-09-01 00:00:00.000 2016-09-01 20:52:14.000 417012 84
2016-09-01 00:00:00.000 2016-09-01 20:52:44.000 417110 85
2016-09-01 00:00:00.000 2016-09-01 20:53:14.000 417121 86
2016-09-01 00:00:00.000 2016-09-01 20:53:44.000 417128 87
2016-09-01 00:00:00.000 2016-09-01 20:54:14.000 417128 88
2016-09-01 00:00:00.000 2016-09-01 20:54:44.000 417128 89
2016-09-01 00:00:00.000 2016-09-01 20:55:14.000 417138 90
2016-09-01 00:00:00.000 2016-09-01 20:55:44.000 417142 91
2016-09-01 00:00:00.000 2016-09-01 20:56:14.000 417142 92
2016-09-01 00:00:00.000 2016-09-01 20:56:33.000 417186 93
2016-09-01 00:00:00.000 2016-09-01 20:56:46.000 417217 94
2016-09-01 00:00:00.000 2016-09-01 20:57:14.000 417294 95
2016-09-01 00:00:00.000 2016-09-01 20:57:31.000 417361 96
2016-09-01 00:00:00.000 2016-09-01 20:57:44.000 417434 97
2016-09-01 00:00:00.000 2016-09-01 20:58:14.000 417575 98
2016-09-01 00:00:00.000 2016-09-01 20:58:44.000 417764 99
2016-09-01 00:00:00.000 2016-09-01 20:58:57.000 417813 100
2016-09-01 00:00:00.000 2016-09-01 20:59:09.000 417848 101
2016-09-01 00:00:00.000 2016-09-01 20:59:26.000 417901 102
2016-09-01 00:00:00.000 2016-09-01 20:59:39.000 417945 103
2016-09-01 00:00:00.000 2016-09-01 20:59:52.000 417963 104
2016-09-01 00:00:00.000 2016-09-01 21:00:07.000 418028 105
2016-09-01 00:00:00.000 2016-09-01 21:00:21.000 418084 106
2016-09-01 00:00:00.000 2016-09-01 21:00:37.000 418116 107
2016-09-01 00:00:00.000 2016-09-01 21:00:52.000 418116 108
2016-09-01 00:00:00.000 2016-09-01 21:01:14.000 418116 109
2016-09-02 00:00:00.000 2016-09-02 09:29:38.000 418116 1
2016-09-02 00:00:00.000 2016-09-02 09:29:38.000 418116 2
2016-09-02 00:00:00.000 2016-09-02 09:30:09.000 418126 3
2016-09-02 00:00:00.000 2016-09-02 09:30:09.000 418126 4
2016-09-02 00:00:00.000 2016-09-02 09:30:36.000 418155 5
2016-09-02 00:00:00.000 2016-09-02 09:30:49.000 418195 6
2016-09-02 00:00:00.000 2016-09-02 09:31:01.000 418233 7
2016-09-02 00:00:00.000 2016-09-02 09:31:14.000 418275 8
2016-09-02 00:00:00.000 2016-09-02 09:31:29.000 418328 9
我尝试了什么：
我尝试过CTE，但我无法获得欲望值。
What I have tried:
I have tried CTE but i can not get the desire value.
推荐答案
一般来说，你可能会使用像DATEDIFF这样的东西 DATEDIFF（ Transact-SQL） [ ^ ] - 但正如我在评论中指出的那样，你需要提供更多信息 - 你想要的是秒，天，月的差异吗？ ，???例如
a一般形式的SQL可以是
in general, you'd likely use something like DATEDIFF DATEDIFF (Transact-SQL)[^] - but as Ive indicated in the comments, you need to provide more information - do you want the difference in seconds, days, months, ??? for example
a general form of SQL could be
select DATEDIFF(day, column1, column2)
from tablex
where column1 >= startdaterange AND
column2 <= enddaterange;
试试这段代码。 ..
Hi,
Try this code . ..
DECLARE @MinDate DATE = '20140101',
@MaxDate DATE = '20140106';
SELECT TOP (DATEDIFF(DAY, @MinDate, @MaxDate) + 1)
Date = DATEADD(DAY, ROW_NUMBER() OVER(ORDER BY a.object_id) - 1, @MinDate)
FROM sys.all_objects a
CROSS JOIN sys.all_objects b;
在这里学习更多细节 。 。 。
获取两者之间的所有日期SQL Server中的日期 - 堆栈溢出 [ ^ ]
这篇关于如何在给定日期范围之间获得每个日期的第一个和最后一个值的子句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
