Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2009
    Posts
    3

    Unanswered: How can I create a Query based on a Table?

    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

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    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
    George
    Home | Blog

  3. #3
    Join Date
    Jun 2009
    Posts
    3
    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

  4. #4
    Join Date
    Jun 2009
    Posts
    3
    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
    Attached Files Attached Files

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •