How do I get substarction of first and last value of each date between a given date range

Question

1.00/5 (1 vote)

See more:

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

What I have tried:

I have tried CTE but i can not get the desire value.

Posted 18-Sep-16 21:37pm

Uttam_Kharwar

Updated 18-Sep-16 21:52pm

Add a Solution

Comments

Garth J Lancaster 19-Sep-16 3:48am

what is 'CTE' ?
what do you mean by 'date range' - is this represented in a row or is it multiple rows >

how about updating your question to show what sort of value you expect for a row or small sub-range ?

Maciej Los 19-Sep-16 4:16am

As to the CTE -> Common Table Expressions:
WITH common_table_expression (Transact-SQL)
Using Common Table Expressions
Recursive Queries Using Common Table Expressions

Garth J Lancaster 19-Sep-16 9:34am

cheers Maciej

Maciej Los 19-Sep-16 4:16am

-- posted by mistake --

Uttam_Kharwar 19-Sep-16 4:18am

My table is as
ReceiveDate,ReceiveTime,Odometer,Rank.
suppose on receive date 01-09-2016 i have 15 records and on 02-09-2016 i have 12 records.Now i want to get the substraction of odometer of 1st and last record of 01-09-2016 and same 02-09-2016.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Garth J Lancaster · Answer 1 · 2016-09-18T21:48:00

Solution 1

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;

Posted 18-Sep-16 21:48pm

Garth J Lancaster

Updated 18-Sep-16 21:49pm

v2