i am facing problem in filtering data in sqldatabase so i need appropriate query in sql to solve the issue. i am giving my sample data below

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

A_ID         TIME_STAMP        SENSOR1_1 
940136  2012-11-25 09:38:30.447   1
940137  2012-11-25 09:37:30.327   1
940138  2012-11-25 09:36:30.207   1
940139  2012-11-25 09:35:30.860   1
940039  2012-11-25 09:34:30.290   1
940040  2012-11-25 09:33:29.907   1
940041  2012-11-25 09:32:29.787   1
940042  2012-11-25 09:31:29.670   1
940043  2012-11-25 09:30:29.550   1
940044  2012-11-25 09:29:29.430   1
939974  2012-11-25 09:28:29.310   1
939975  2012-11-25 09:27:29.190   1
939976  2012-11-25 09:26:29.720   1
939977  2012-11-25 09:25:28.953   0
939978  2012-11-25 09:24:28.833   0
939870  2012-11-25 09:23:28.773   0
939865  2012-11-25 09:22:28.653   0
939866  2012-11-25 09:21:28.537   0
939867  2012-11-25 09:20:28.417   0
939868  2012-11-25 09:19:28.297   1
939869  2012-11-25 09:18:28.177   1
939778  2012-11-25 09:17:28.580   0 
939779  2012-11-25 09:16:27.937   0
939780  2012-11-25 09:15:27.820   1
939781  2012-11-25 09:14:27.700   1
939782  2012-11-25 09:13:27.580   1
939702  2012-11-25 09:12:27.520   1
939703  2012-11-25 09:11:27.403   1
939704  2012-11-25 09:10:27.283   1
939705  2012-11-25 09:09:27.163   1
939706  2012-11-25 09:08:27.430   1
939707  2012-11-25 09:07:26.923   1
939617  2012-11-25 09:06:26.807   1
939618  2012-11-25 09:05:26.687   1
939619  2012-11-25 09:04:26.567   1
939620  2012-11-25 09:03:26.447   1
939621  2012-11-25 09:02:26.327   1
939519  2012-11-25 09:01:26.207   1
939520  2012-11-25 09:00:26.880   1
939521  2012-11-25 08:59:25.970   1
939522  2012-11-25 08:58:25.850   1
939523  2012-11-25 08:57:25.730   1
939524  2012-11-25 08:56:25.610   1
939394  2012-11-25 08:55:25.493   0
939395  2012-11-25 08:54:25.373   0
939396  2012-11-25 08:53:25.253   1
939397  2012-11-25 08:52:25.133   1
939398  2012-11-25 08:51:24.993   1
939334  2012-11-25 08:50:24.877   0
939335  2012-11-25 08:49:24.757   0
939336  2012-11-25 08:48:24.637   0
939337  2012-11-25 08:47:24.517   0
939338  2012-11-25 08:46:24.397   0
939339  2012-11-25 08:45:24.277   0
939270  2012-11-25 08:44:24.157   0
939271  2012-11-25 08:43:24.390   0
939272  2012-11-25 08:42:23.920   0
939273  2012-11-25 08:41:23.800   0
939274  2012-11-25 08:40:23.680   0
939275  2012-11-25 08:39:23.560   0
939180  2012-11-25 08:38:23.443   0
939181  2012-11-25 08:37:23.323   0 
939182  2012-11-25 08:36:23.203   0
939183  2012-11-25 08:35:23.840   0
938998  2012-11-25 08:34:23.150   0
938997  2012-11-25 08:33:22.897   0
938986  2012-11-25 08:32:22.777   0
938987  2012-11-25 08:31:22.657   0
938988  2012-11-25 08:30:22.537   0
938989  2012-11-25 08:29:22.417   0
938990  2012-11-25 08:28:22.297   0
938913  2012-11-25 08:27:22.180   0
938914  2012-11-25 08:26:22.590   0
938915  2012-11-25 08:25:21.940   0
938916  2012-11-25 08:24:21.820   0
938917  2012-11-25 08:23:21.670   0
938918  2012-11-25 08:22:21.550   0
938834  2012-11-25 08:21:21.433   0
938835  2012-11-25 08:20:21.313   0
938836  2012-11-25 08:19:21.193   0
938837  2012-11-25 08:18:21.730   0
938809  2012-11-25 08:17:20.953   0
938810  2012-11-25 08:16:20.837   0
938774  2012-11-25 08:15:20.697   0
938775  2012-11-25 08:14:20.577   0
938776  2012-11-25 08:13:20.457   0
938777  2012-11-25 08:12:20.337   0
938778  2012-11-25 08:11:20.217   0
938706  2012-11-25 08:10:20.980   0
938701  2012-11-25 08:09:19.977   0
938702  2012-11-25 08:08:19.860   0
938703  2012-11-25 08:07:19.740   0
938704  2012-11-25 08:06:19.620   0
938705  2012-11-25 08:05:19.500   0
938654  2012-11-25 08:04:19.380   0
938655  2012-11-25 08:03:19.263   0
938656  2012-11-25 08:02:19.143   0
938580  2012-11-25 08:01:19.230   0
938581  2012-11-25 08:00:18.903   0
938582  2012-11-25 07:59:18.783   0
938533  2012-11-25 07:58:18.667   0
938534  2012-11-25 07:57:18.547   0
938535  2012-11-25 07:56:18.427   0
938536  2012-11-25 07:55:18.307   0
938537  2012-11-25 07:54:18.187   0
938489  2012-11-25 07:53:18.127   0
938484  2012-11-25 07:52:18.800   0
938485  2012-11-25 07:51:17.890   0
938486  2012-11-25 07:50:17.770   0
938487  2012-11-25 07:49:17.650   0
938488  2012-11-25 07:48:17.530   0
938409  2012-11-25 07:47:17.410   0
938410  2012-11-25 07:46:17.290   0
938396  2012-11-25 07:45:17.233   0
938388  2012-11-25 07:44:17.113   0
938389  2012-11-25 07:43:16.993   0
938390  2012-11-25 07:42:16.873   0
938348  2012-11-25 07:41:16.813   0
938349  2012-11-25 07:40:16.697   0
938350  2012-11-25 07:39:16.577   0
938351  2012-11-25 07:38:16.457   0
938352  2012-11-25 07:37:16.337   0
938353  2012-11-25 07:36:16.217   0
938306  2012-11-25 07:35:16.970   1
938307  2012-11-25 07:34:15.977   1
938308  2012-11-25 07:33:15.860   1
938309  2012-11-25 07:32:15.740   1
938310  2012-11-25 07:31:15.620   1
938259  2012-11-25 07:30:15.560   1
938260  2012-11-25 07:29:15.440   1
938240  2012-11-25 07:28:15.320   1
938241  2012-11-25 07:27:15.203   1
938242  2012-11-25 07:26:15.820   1

So in d above sample A_ID 939868 and 939869 generated reading 1 and 1(sensor1_1)
so i need to eliminate those 1 and 1 to get fine report. for these i need sql query.Please help me regarding these issue

Posted 6-Dec-12 0:38am

kalyan10qwerty

Updated 6-Dec-12 0:44am

Aarti Meswania

v2

Add a Solution

Comments

Aarti Meswania 6-Dec-12 6:47am

not clear
put few records in input
and output for that input records

do not give lots of records
and mention desired output please

kalyan10qwerty 6-Dec-12 7:04am

i dint get what is input r what is output. if u c above sample a_id 939868 and 939869 generated 1 and 1 which is ubnormal...so those records should be eliminated. i need query to eliminate those records.those two 1's which i gave are just sample.i have huge data as same as those which is to b eliminated. so i need query to eliminate those type of records.

thank you

ZurdoDev 6-Dec-12 8:16am

I can't understand what you are asking.

joshrduncan2012 6-Dec-12 10:45am

You need to be more clear as to what you are looking for. I agree with Ryan, your question is still a little unclear.

kalyan10qwerty 7-Dec-12 0:48am

i need to eliminate 939868 and 939869 records completely from above sample data.

kalyan10qwerty 7-Dec-12 5:10am

so no solutions 4m any!!!!

Rohit Shrivastava 7-Dec-12 18:26pm

Do you want to delete/filter all the records which have value 1 in column "sensor1_1"? Just try to write the criteria on which you want to delete/filter the records, we got the samples but why only these two why not rest?

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)

Zaf Khan · Answer 1 · 2012-12-07T23:31:00

You need You need to use the TIMESTAMP field to isolate those two records from the others.
If you go by sensor1_1 field you cannot do it by the sensor1_1 field alone.

If you want JUST those two records then you query for the DATE + TIME of those records.......

If you want all the others and NOT those two records, then your query for everything which is NOT at/on those DATE/TIMES

Patel Shailendra · Answer 2 · 2012-12-09T03:10:00

if you not want (939868,939869) then this will help you and sensso1_1 is(1 or 0)
select * from Eli where A_ID not in (939868,939869)

if you want those which have sensor1_1=1
select * from Eli where A_ID not in (939868,939869)and sensso1_1=1

if you want those which have sensor1_1=0
select * from Eli where A_ID not in (939868,939869)and sensso1_1=0

i am facing problem in filtering data in sqldatabase so i need appropriate query in sql to solve the issue. i am giving my sample data below

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0