Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2004

    Unanswered: Help needed on Sql query with Time functions

    Hello everyone,
    can somone help me out with my problem? I have a table (named TblSensorRTData) with fields (sensorId -> char (3), dataTimeStamp -> datatime, readValue -> float) in MSDE 2000 (desktop version of MS SQL Server 2000).

    There are 60 sensors at present and all of them are scanned exactly once every second. So, for every 1 second, a new set of 60 rows (corresponding to the 60 sensors) are populated into the table by the Acquisition program.

    Phase II is that I have to display the data to the user at an interval he chooses (in another program).

    For eg: if the user chooses the refresh rate as 10 seconds, I have to display information like this:

    Time Value1 Value2 Value3 Value4 Value5

    10:30:00 xx.xx xx.xx xx.xx xx.xx xx.xx
    10:30:10 xx.xx xx.xx xx.xx xx.xx xx.xx
    10:30:20 xx.xx xx.xx xx.xx xx.xx xx.xx
    10:30:30 xx.xx xx.xx xx.xx xx.xx xx.xx
    ..... ..... ... .... .... ....
    ..... ..... ... .... .... ....
    ..... ..... ... .... .... ....


    The values correspond to the values in the table at the time specified by the column Time. Also, the user selects which, out of the 60 sensors, are to be displayed.

    Hence I need to select the values of only those sensors (which are selected) and display their values at exactly those time (as specified in the interval). I have no clue how to do this. Hence I request you to help me out.


    PS: Please do not mistake my intensions. I am basically an Embedded Systems developer and I left Database programming 6-7 years ago. So, I can do it provided I have some time, but my schedule demands the results fast enough and hence I posted this query.

  2. #2
    Join Date
    Sep 2003
    The extremely Royal borough of Kensington, London
    Select columns
    from table
    where DATEPART(ss, date_column)%10 = 0

    Should you wish to test the output,

    create table test
    date_field DATETIME,
    pos INTEGER

    Declare @pos As Integer
    Set @pos = 0
    While @pos < 250000
    insert into test values (GETDATE(), @pos)
    Set @pos = @pos + 1
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  3. #3
    Join Date
    Jan 2004

    Question Noo

    Dear Sir,

    perhaps my question lacked the clarity it should have... Well, I have to display the updated fields every 'n' seconds (where n is the number chosen by the user). After the first n seconds, the values with time (seconds) n are to be selected and displayed. Also, a maximum of only 10 rows are to be displayed at any time. So after the 11th 'n' seconds, the screen has to scroll (im doing it in VB using the MS Flex Grid control) by one. i.e. all the rows should be shifted up by 1 and the last row should contain the latest extract.

    So, at any given time when i am executing the query, I have to take the following into consideration:

    1. Get only the values (of the required sensor ids) for time that is exactly 'n' seconds after the previous extract.
    NOT ALL Values beginning some time and 'n' seconds downwards.

    Warm Regards,

Posting Permissions

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