OraFAQ Forum: SQL & PL/SQL » Getting Desire Data According to the interval...

Home » SQL & PL/SQL » SQL & PL/SQL » Getting Desire Data According to the interval... (Oracle 11g, Windows 2008 R2....)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Getting Desire Data According to the interval... [message #648069]

Tue, 16 February 2016 00:39

Dips99
Messages: 6
Registered: February 2016
Location: Dehli

Junior Member

Hello Experts,

I want To display my query result just passing the start_date and end_Date and inetrval..
Suppose i want to display my data for vehicles that a running and I want work hour report like Vehicle meter, longitude and latitude etc.. As, the tracking device send the data after few seconds like it may be 10 secs , 5 secs,15, 20,22 seconds etc. Their is no fix time...
So, my problem is to display data for particular vehicles.. after given interval..
Just see the Data and analyze

sys_service_id id I2 gps_time odometer gps_latitude battery_status gps_longitude gps_speed address rn
187 776831 0 2016-01-28 09:19:03.000 31447.52 28.5323161666667 NULL 77.1954345 0 NULL 1
187 1162360 0 2016-01-29 02:29:35.000 31499.55 28.5661058333333 NULL 77.1982733333333 0 NULL 2
187 1165135 0 2016-01-29 02:29:55.000 31499.55 28.5661058333333 NULL 77.1982733333333 0 NULL 3
187 1216990 0 2016-01-29 02:38:48.000 0 28.5661058333333 NULL 77.1982733333333 0 NULL 4
187 1266251 0 2016-01-29 02:47:49.000 31499.55 28.5661058333333 NULL 77.1982733333333 0 NULL 5
187 1275160 0 2016-01-29 02:47:59.000 0 28.5661058333333 NULL 77.1982733333333 0 NULL 6
187 1275161 0 2016-01-29 02:48:09.000 0 28.5661058333333 NULL 77.1982733333333 0 NULL 7
187 1200569 0 2016-01-29 10:02:13.000 31540.73 28.5224913333333 NULL 77.1897368333333 0 NULL 8
187 1200571 0 2016-01-29 10:02:23.000 31540.73 28.5224913333333 NULL 77.1897368333333 0 NULL 9
187 1200732 0 2016-01-29 10:03:23.000 0 28.5224913333333 NULL 77.1897368333333 0 NULL 10
187 1202969 0 2016-01-29 10:03:33.000 31540.76 28.5231915 NULL 77.1899621666667 19.43 NULL 11
187 1202973 0 2016-01-29 10:03:43.000 31540.81 28.523434 NULL 77.1903845 21.78 NULL 12
187 1202975 0 2016-01-29 10:03:53.000 31540.86 28.5232998333333 NULL 77.1908483333333 7.54 NULL 13
187 1205573 0 2016-01-29 10:04:33.000 31541.07 28.5227615 NULL 77.1929533333333 29.45 NULL 14
187 1208171 0 2016-01-29 10:04:43.000 31541.17 28.5224975 NULL 77.193854 37.33 NULL 15
187 1208172 0 2016-01-29 10:04:53.000 31541.28 28.5224188333333 NULL 77.1949643333333 39.61 NULL 16
187 1208173 0 2016-01-29 10:05:03.000 31541.32 28.5224543333333 NULL 77.1954191666667 0 NULL 17
187 1216988 0 2016-01-29 10:05:13.000 0 28.5224555 NULL 77.1955133333333 2.5 NULL 18
187 1227854 0 2016-01-29 10:06:33.000 31541.68 28.521525 NULL 77.1998155 39.33 NULL 19
187 1227855 0 2016-01-29 10:06:43.000 31541.77 28.5210993333333 NULL 77.2006486666667 30.15 NULL 20
187 1227856 0 2016-01-29 10:06:53.000 31541.84 28.5209246666667 NULL 77.2012883333333 24.93 NULL 21
187 1227858 0 2016-01-29 10:07:03.000 31541.89 28.5207745 NULL 77.2018141666667 11.56 NULL 22
187 1239586 0 2016-01-29 10:07:24.000 31541.95 28.5206436666667 NULL 77.2023345 8.35 NULL 23
187 1242455 0 2016-01-29 10:07:34.000 31541.97 28.5205956666667 NULL 77.2025825 10.88 NULL 24
187 1242456 0 2016-01-29 10:07:44.000 31542.01 28.5205186666667 NULL 77.2029935 15.3 NULL 25
187 1242457 0 2016-01-29 10:07:54.000 31542.06 28.5203995 NULL 77.203514 20 NULL 26
187 1242459 0 2016-01-29 10:08:05.000 31542.11 28.5202876666667 NULL 77.203901 18.8 NULL 27
187 1247308 0 2016-01-29 10:08:15.000 31542.17 28.5201331666667 NULL 77.2045686666667 28.75 NULL 28
187 1247309 0 2016-01-29 10:08:35.000 31542.3 28.5199448333333 NULL 77.2058623333333 0 NULL 29
187 1256324 0 2016-01-29 10:08:55.000 0 28.5199115 NULL 77.2059908333333 12.29 NULL 30
187 1256325 0 2016-01-29 10:09:05.000 0 28.5197651666667 NULL 77.206503 25.96 NULL 31
187 1261179 0 2016-01-29 10:09:15.000 31542.43 28.5195988333333 NULL 77.2072233333333 27.37 NULL 32
187 1266243 0 2016-01-29 10:10:15.000 31542.7 28.518989 NULL 77.209445 13.43 NULL 33
187 1266245 0 2016-01-29 10:10:25.000 31542.77 28.5191075 NULL 77.208751 31.62 NULL 34
187 1160161 0 2016-01-30 02:00:58.000 0 28.5416395 NULL 77.1997955 34.08 NULL 35
187 1162355 0 2016-01-30 02:01:08.000 31576.9 28.5424846666667 NULL 77.2002015 33.51 NULL 36
187 1165136 0 2016-01-30 02:01:18.000 31577 28.5432931666667 NULL 77.2006223333333 32.64 NULL 37
187 1165139 0 2016-01-30 02:01:58.000 31577.15 28.5445936666667 NULL 77.2011903333333 23.12 NULL 38
187 1200572 0 2016-01-30 02:03:18.000 31577.88 28.5506603333333 NULL 77.2038925 16.69 NULL 39
187 1200573 0 2016-01-30 02:03:28.000 31577.89 28.5507463333333 NULL 77.2039323333333 0 NULL 40
187 1200728 0 2016-01-30 02:03:38.000 0 28.5507463333333 NULL 77.2039323333333 0 NULL 41
187 1200730 0 2016-01-30 02:03:48.000 0 28.5507463333333 NULL 77.2039323333333 0 NULL 42
187 1205574 0 2016-01-30 02:04:08.000 31577.89 28.5507463333333 NULL 77.2039323333333 0 NULL 43
187 1216991 0 2016-01-30 02:04:18.000 0 28.5507463333333 NULL 77.2039323333333 0 NULL 44
187 1216992 0 2016-01-30 02:04:28.000 0 28.5507463333333 NULL 77.2039323333333 0 NULL 45
187 1219597 0 2016-01-30 02:04:38.000 31577.89 28.5507463333333 NULL 77.2039323333333 0 NULL 46
187 1224870 0 2016-01-30 02:05:45.000 31577.89 28.5507463333333 NULL 77.2039323333333 0 NULL 47
187 1245292 0 2016-01-30 02:06:35.000 31578.12 28.5544076666667 NULL 77.2019291666667 28.12 NULL 48
187 1245293 0 2016-01-30 02:06:45.000 31578.18 28.554946 NULL 77.2020888333333 24.92 NULL 49
187 1247307 0 2016-01-30 02:06:55.000 31578.27 28.5557333333333 NULL 77.2022473333333 30.91 NULL 50
187 1261180 0 2016-01-30 02:07:05.000 31578.33 28.5562463333333 NULL 77.2023843333333 21.31 NULL 51
187 1261181 0 2016-01-30 02:07:15.000 31578.41 28.5569486666667 NULL 77.202562 21.41 NULL 52
187 1263698 0 2016-01-30 02:07:25.000 31578.46 28.557329 NULL 77.2026453333333 21.58 NULL 53
187 1263700 0 2016-01-30 02:07:35.000 31578.54 28.5580351666667 NULL 77.2028293333333 31.57 NULL 54
187 1263701 0 2016-01-30 02:08:15.000 31578.81 28.5603058333333 NULL 77.2031536666667 18.65 NULL 55
187 1275163 0 2016-01-30 02:08:25.000 0 28.5604791666667 NULL 77.2023698333333 32.51 NULL 56
187 1275165 0 2016-01-30 02:08:35.000 0 28.5606373333333 NULL 77.2015286666667 30.22 NULL 57
187 1278875 0 2016-01-30 02:16:33.000 31579.63 28.5659835 NULL 77.1982708333333 0 NULL 58
187 1278877 0 2016-01-30 02:16:43.000 31579.63 28.5659835 NULL 77.1982708333333 0 NULL 59
187 1278878 0 2016-01-30 02:17:03.000 31579.63 28.5659835 NULL 77.1982708333333 0 NULL 60
187 1278880 0 2016-01-30 02:17:33.000 31579.63 28.5659835 NULL 77.1982708333333 0 NULL 61
187 47088 0 2016-01-30 03:20:28.000 0 28.5661671666667 NULL 77.198272 0 NULL 62
187 47089 0 2016-01-30 03:21:28.000 0 28.5661671666667 NULL 77.198272 0 NULL 63
187 53515 0 2016-01-30 03:21:38.000 31585.63 28.5661671666667 NULL 77.198272 0 NULL 64
187 516016 0 2016-01-30 04:41:54.000 0 28.5661671666667 NULL 77.198272 0 NULL 65
187 519092 0 2016-01-30 04:42:04.000 31585.63 28.5661671666667 NULL 77.198272 0 NULL 66
187 519093 0 2016-01-30 04:43:04.000 31585.63 28.5661671666667 NULL 77.198272 0 NULL 67
187 519094 0 2016-01-30 04:43:14.000 31585.63 28.5661671666667 NULL 77.198272 0 NULL 68
187 523564 0 2016-01-30 04:43:24.000 31585.63 28.5661671666667 NULL 77.198272 0 NULL 69
187 557138 0 2016-01-30 04:50:04.000 0 28.5661671666667 NULL 77.198272 0 NULL 70
187 557139 0 2016-01-30 04:50:14.000 0 28.5661671666667 NULL 77.198272 0 NULL 71
187 559663 0 2016-01-30 04:50:24.000 31585.63 28.5661671666667 NULL 77.198272 0 NULL 72
187 592022 0 2016-01-30 04:50:34.000 0 28.5661671666667 NULL 77.198272 0 NULL 73
187 592023 0 2016-01-30 04:50:44.000 0 28.5661671666667 NULL 77.198272 0 NULL 74
187 594158 0 2016-01-30 04:57:05.000 0 28.5661671666667 NULL 77.198272 0 NULL 75
187 594159 0 2016-01-30 04:57:18.000 0 28.5661671666667 NULL 77.198272 0 NULL 76
187 596957 0 2016-01-30 04:57:28.000 31585.63 28.5661671666667 NULL 77.198272 0 NULL 77
187 596958 0 2016-01-30 04:58:18.000 31585.63 28.5661671666667 NULL 77.198272 0 NULL 78
187 596959 0 2016-01-30 04:58:28.000 31585.63 28.5661671666667 NULL 77.198272 0 NULL 79
187 637983 0 2016-01-30 05:04:51.000 0 28.5661671666667 NULL 77.198272 0 NULL 80
187 645480 0 2016-01-30 05:05:01.000 31585.63 28.5661671666667 NULL 77.198272 0 NULL 81
187 645483 0 2016-01-30 05:05:21.000 31585.63 28.5661671666667 NULL 77.198272 0 NULL 82
187 657122 0 2016-01-30 05:06:01.000 0 28.5661671666667 NULL 77.198272 0 NULL 83
187 657123 0 2016-01-30 05:06:11.000 0 28.5661671666667 NULL 77.198272 0 NULL 84
187 736295 0 2016-01-30 05:13:38.000 0 28.5661671666667 NULL 77.198272 0 NULL 85
187 767780 0 2016-01-30 05:16:09.000 31585.63 28.5661671666667 NULL 77.198272 0 NULL 86
187 767782 0 2016-01-30 05:17:12.000 31585.63 28.5661671666667 NULL 77.198272 0 NULL 87
187 774095 0 2016-01-30 05:17:22.000 0 28.5661671666667 NULL 77.198272 0 NULL 88
187 774096 0 2016-01-30 05:17:33.000 0 28.5661671666667 NULL 77.198272 0 NULL 89
187 776830 0 2016-01-30 05:19:13.000 31585.63 28.5661671666667 NULL 77.198272 0 NULL 90
187 778977 0 2016-01-30 05:23:43.000 31585.63 28.5661671666667 NULL 77.198272 0 NULL 91
187 781334 0 2016-01-30 05:23:53.000 31585.63 28.5661671666667 NULL 77.198272 0 NULL 92
187 794422 0 2016-01-30 05:24:03.000 0 28.5661671666667 NULL 77.198272 0 NULL 93
187 826120 0 2016-01-30 05:25:03.000 31585.63 28.5661671666667 NULL 77.198272 0 NULL 94
187 826804 0 2016-01-30 05:25:13.000 0 28.5661671666667 NULL 77.198272 0 NULL 95
187 826805 0 2016-01-30 05:25:23.000 0 28.5661671666667 NULL 77.198272 0 NULL 96
187 826806 0 2016-01-30 05:25:33.000 0 28.5661671666667 NULL 77.198272 0 NULL 97
187 826807 0 2016-01-30 05:27:23.000 0 28.5661671666667 NULL 77.198272 0 NULL 98
187 937913 0 2016-01-30 05:29:38.000 31585.63 28.5661671666667 NULL 77.198272 0 NULL 99
187 940162 0 2016-01-30 05:30:08.000 31585.63 28.5661671666667 NULL 77.198272 0 NULL 100

So, i want the output according to that interval gap. like if a pass interval 70 seconds then difference between the gps_time column of first row and second row is greater than or equal to 70 seconds.. If a pass 30 seconds in interval then it is in the difference of 30 seconds..
so, help me to short out this problem You can see the data and my query.
also here it is..

select distinct sys_service_id,id,I2,
gps_time as gps_time,tel_odometer as odometer,
gps_latitude,battery_status as battery_status,
gps_longitude,
round(gps_speed,2) as gps_speed,address_from_device as address,
rn=ROW_NUMBER() OVER(ORDER BY gps_time Asc)
from telemetry where sys_service_id='187'
and gps_time between '2016-01-01 00:00:00' and '2016-01-31 00:00:00'

/forum/fa/13027/0/

[mod-edit: imaged inserted into message body by bb]

Attachment: SQL SERVER_DATE.jpg
(Size: 139.51KB, Downloaded 1149 times)

[Updated on: Tue, 16 February 2016 15:34] by Moderator

Report message to a moderator

Re: Getting Desire Data According to the interval... [message #648070 is a reply to message #648069]

Tue, 16 February 2016 00:43

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

From your previous topic:

Michel Cadot wrote on Mon, 15 February 2016 07:47

Welcome to the forum.

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Also always post your Oracle version, with 4 decimals.

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Report message to a moderator

Re: Getting Desire Data According to the interval... [message #648073 is a reply to message #648070]

Tue, 16 February 2016 01:11

Dips99
Messages: 6
Registered: February 2016
Location: Dehli

Junior Member

Hi Michel,

Please help me to short out this query..

Thanks

Report message to a moderator

Re: Getting Desire Data According to the interval... [message #648076 is a reply to message #648073]

Tue, 16 February 2016 01:16

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

First follow the rules and provide a test case, then I'll try to help you.
For the moment I have nothing to work with.

[Updated on: Tue, 16 February 2016 01:17]

Report message to a moderator

Re: Getting Desire Data According to the interval... [message #648132 is a reply to message #648076]

Wed, 17 February 2016 03:00

Dips99
Messages: 6
Registered: February 2016
Location: Dehli

Junior Member

Hello,

Could you just give me the hint regarding this query. So, that i can do it.

And for future i will take care of everything what you said. but for now it is important for me. So, help me out.

Thanks

Report message to a moderator

Re: Getting Desire Data According to the interval... [message #648133 is a reply to message #648132]

Wed, 17 February 2016 03:05

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

And in the future I will then help you.

If this is so important for you then you should envisage to post what we require to help you.
For me, it is not important you solve your problem or not

Report message to a moderator

Previous Topic:	using index and cursor
Next Topic:	View tailoring

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 19 18:40:07 CDT 2024