How can I create a Query based on a Table?

jokboy · #1 (**permalink**) 06-30-09, 07:16

Dear Group,
Please forgive me if this is a simple question but I am not well versed in the Art of SQL.
I have two tables.

Table one contains the monitoring data at minute intervals. The table looks like this.

Code:

Time	                  Hs    	    Vw    	    Tw    
2009-04-01 00:00:00	    0.4    	    20    	    45    
2009-04-01 00:01:00	    0.5    	    30    	    20    
2009-04-01 00:02:00	    0.4    	    30    	    45    
...	    ...    	    ...    	    ...

Table two contains my reporting criteria

Code:

Start Time	end Time	    Hs    	    Vw    	    Tw    
2009-04-01 00:00:00	2009-04-01 01:00:00	    0.5    	    30    	    40    
2009-04-01 01:00:00	2009-04-01 02:00:00	    0.25    	    30    	    20    
2009-04-01 02:00:00	2009-04-01 03:00:00	    0.3    	    30    	    30    
...	...	...	...	...

For each Criteria Row, I want to count the number of entries that exceed the criteria, i.e. the results would be something like...

Code:

Start Time	End Time	Count
2009-04-01 00:00:00	2009-04-01 01:00:00	   35
2009-04-01 01:00:00	2009-04-01 02:00:00	   60   
2009-04-01 02:00:00	2009-04-01 03:00:00	   0   
   ...	   ...	   ...

I am not sure how to go about constructing this query, even if it was able to be done as a single query or not.
Any assistance would be greatly apreciated.

Regards

gvee · #2 (**permalink**) 06-30-09, 08:22

I don't understand how your sample data returns that result - can you elaborate?

I think your sample data is flawed also, as with the data provided, none of the rows returned exceed the criteria (based on my understanding of the criteria, that is).

Code:

DECLARE @samples table (
   time datetime
 , hs   decimal(2,1)
 , vw   int
 , tw   int
)

INSERT INTO @samples (time, hs, vw, tw)
          SELECT '2009-04-01 00:00:00',	0.4, 20, 45
UNION ALL SELECT '2009-04-01 00:01:00',	0.5, 30, 20
UNION ALL SELECT '2009-04-01 00:02:00',	0.4, 30, 45

DECLARE @criteria table (
   start_time datetime
 , end_time   datetime
 , hs         decimal(2,1)
 , vw         int
 , tw         int
)

INSERT INTO @criteria (start_time, end_time, hs, vw, tw)
          SELECT '2009-04-01 00:00:00', '2009-04-01 01:00:00', 0.5 , 30, 40
UNION ALL SELECT '2009-04-01 01:00:00', '2009-04-01 02:00:00', 0.25, 30, 20
UNION ALL SELECT '2009-04-01 02:00:00', '2009-04-01 03:00:00', 0.3 , 30, 30


SELECT c.start_time
     , c.end_time
     , Count(s.time)
FROM   @criteria As c
 LEFT
  JOIN @samples As s
    ON s.time >= c.start_time
   AND s.time < c.end_time
   AND s.hs >= c.hs
   AND s.vw >= c.vw
   AND s.tw >= c.tw
GROUP
    BY c.start_time
     , c.end_time

jokboy · #3 (**permalink**) 06-30-09, 09:20

George,

Thanks for the response. Regarding the data, I have only put in a couple of elements, the actual data table for this is 60 rows per hour, so I thought it was a bit big to post here.

I have just noticed that my table fields are a little bit ambiguous so I will add a '_crit' to the end of the criteria table fields, to make them a bit easier to read.

I will see how this pans out. Hopefully this will work for me, if not, I will post an actual selection of data with 'real' results, not dummy results as I have previously.

Regards

Andrew

jokboy · #4 (**permalink**) 07-01-09, 08:13

George,

I am using SQLite Expert as a test bench.

I have inserted the data into a table. My Data Table is called MOEData and my Criteria Table is called Criteria.

In addition to the criteria in the table (which can change per line) there is an additional criteria that applies to every line as follows.

I would like to count all cases where the Hs in the MOEData excees the Hs_Crit OR (where the Vw exceeds the Vw_Crit criteria AND (Twind is between 45 to 135 OR Twind is between 225 and 315)

Any further assistance would be appreciated.

Regards

Andrew

r937 · #5 (**permalink**) 07-01-09, 10:07

Quote:

Originally Posted by jokboy

Any further assistance would be appreciated.

you could show your data in plain text

not everybody who is good with SQL necessarily has the software to open your db3 file

besides, this is the ANSI SQL forum, not the SQLite forum

Internet Services

Web Development

Digital Marketing

Consumer Tech