Как получить субаркт первого и последнего значения каждой даты между заданным диапазоном дат
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, но не могу получить желаемое значение.
Garth J Lancaster
что такое "CTE"?
что вы подразумеваете под "диапазоном дат" - это представлено в строке или это несколько строк >
как насчет обновления вашего вопроса, чтобы показать, какого рода значение вы ожидаете для строки или небольшого поддиапазона ?
Maciej Los
Что касается CTE - > Собщих Тспособный Еxpressions с широким ассортиментом:
С common_table_expression (Transact-SQL)
Использование Общих Табличных Выражений
Рекурсивные Запросы, Использующие Обобщенные Табличные Выражения
Garth J Lancaster
ура Мацей
Maciej Los
-- отправлено по ошибке --
Uttam_Kharwar
Мой стол таков
ReceiveDate, ReceiveTime, Одометр, Ранг.
предположим, что на дату получения 01-09-2016 у меня есть 15 записей, а на 02-09-2016 у меня есть 12 записей.Теперь я хочу получить вычитание одометра 1-й и последней записи 01-09-2016 и того же 02-09-2016.