Professional Web Applications Themes

how to extract data filtering by a DateTime field (depending sample period time) ? - Microsoft SQL / MS SQL Server

Hi, I have the following table : Variable Date_Hour Value var1 12/06/03 06:00:00 18 var1 12/06/03 06:00:05 21 var1 12/06/03 06:00:17 23 var1 12/06/03 06:00:23 89 var1 12/06/03 06:00:28 56 var1 12/06/03 06:00:37 23 var1 12/06/03 06:00:50 23 I need to extract values depending of a sampling time period (ie 1 value every 10 seconds) Result should be (with sample period =10 sec) Variable Date_Hour Value var1 12/06/03 06:00:00 18 var1 12/06/03 06:00:17 23 var1 12/06/03 06:00:28 56 var1 12/06/03 06:00:37 23 var1 12/06/03 06:00:50 23 How can I do this ? Thanks for your help. H. MAILLARD...

  1. #1

    Default how to extract data filtering by a DateTime field (depending sample period time) ?

    Hi,

    I have the following table :
    Variable Date_Hour Value
    var1 12/06/03 06:00:00 18
    var1 12/06/03 06:00:05 21
    var1 12/06/03 06:00:17 23
    var1 12/06/03 06:00:23 89
    var1 12/06/03 06:00:28 56
    var1 12/06/03 06:00:37 23
    var1 12/06/03 06:00:50 23

    I need to extract values depending of a sampling time period (ie 1 value
    every 10 seconds)

    Result should be (with sample period =10 sec)

    Variable Date_Hour Value
    var1 12/06/03 06:00:00 18
    var1 12/06/03 06:00:17 23
    var1 12/06/03 06:00:28 56
    var1 12/06/03 06:00:37 23
    var1 12/06/03 06:00:50 23

    How can I do this ?

    Thanks for your help.

    H. MAILLARD





    herve maillard Guest

  2. #2

    Default Re: how to extract data filtering by a DateTime field (depending sample period time) ?

    CREATE TABLE Sometable (variable CHAR(4) NOT NULL, date_hour DATETIME NOT
    NULL, value INTEGER NOT NULL, PRIMARY KEY (variable,date_hour))

    INSERT INTO Sometable VALUES ('var1', '2003-06-12T06:00:00', 18)
    INSERT INTO Sometable VALUES ('var1', '2003-06-12T06:00:05', 21)
    INSERT INTO Sometable VALUES ('var1', '2003-06-12T06:00:17', 23)
    INSERT INTO Sometable VALUES ('var1', '2003-06-12T06:00:23', 89)
    INSERT INTO Sometable VALUES ('var1', '2003-06-12T06:00:28', 56)
    INSERT INTO Sometable VALUES ('var1', '2003-06-12T06:00:37', 23)
    INSERT INTO Sometable VALUES ('var1', '2003-06-12T06:00:50', 23)

    DECLARE samp_period INTEGER
    SET samp_period = 10

    SELECT S1.*
    FROM Sometable AS S1
    JOIN
    (SELECT variable, MIN(date_hour) AS date_hour
    FROM Sometable
    GROUP BY variable, FLOOR(DATEDIFF(SECOND, '20000101', date_hour) /
    samp_period)) AS S2
    ON S1.variable = S2.variable AND S1.date_hour = S2.date_hour

    --
    David Portas
    ------------
    Please reply only to the newsgroup
    --

    "herve maillard" <rvmaillardfree.fr> wrote in message
    news:3f054c62$0$5260$626a54cenews.free.fr...
    > Hi,
    >
    > I have the following table :
    > Variable Date_Hour Value
    > var1 12/06/03 06:00:00 18
    > var1 12/06/03 06:00:05 21
    > var1 12/06/03 06:00:17 23
    > var1 12/06/03 06:00:23 89
    > var1 12/06/03 06:00:28 56
    > var1 12/06/03 06:00:37 23
    > var1 12/06/03 06:00:50 23
    >
    > I need to extract values depending of a sampling time period (ie 1 value
    > every 10 seconds)
    >
    > Result should be (with sample period =10 sec)
    >
    > Variable Date_Hour Value
    > var1 12/06/03 06:00:00 18
    > var1 12/06/03 06:00:17 23
    > var1 12/06/03 06:00:28 56
    > var1 12/06/03 06:00:37 23
    > var1 12/06/03 06:00:50 23
    >
    > How can I do this ?
    >
    > Thanks for your help.
    >
    > H. MAILLARD
    >
    >
    >
    >
    >

    David Portas Guest

  3. #3

    Default Re: how to extract data filtering by a DateTime field (depending sample period time) ?

    Thanks David for your solution !

    The problem is that it take a very long time... (around 2 minutes).
    Do you think that it could be quicker to import in my C# App all the data
    and filter it in my App ?

    Thanks for help...

    H. MAILLARD

    "David Portas" <REMOVE_BEFORE_REPLYING_dportasacm.org> wrote in message
    news:eLMOXOhQDHA.2224TK2MSFTNGP12.phx.gbl...
    > CREATE TABLE Sometable (variable CHAR(4) NOT NULL, date_hour DATETIME NOT
    > NULL, value INTEGER NOT NULL, PRIMARY KEY (variable,date_hour))
    >
    > INSERT INTO Sometable VALUES ('var1', '2003-06-12T06:00:00', 18)
    > INSERT INTO Sometable VALUES ('var1', '2003-06-12T06:00:05', 21)
    > INSERT INTO Sometable VALUES ('var1', '2003-06-12T06:00:17', 23)
    > INSERT INTO Sometable VALUES ('var1', '2003-06-12T06:00:23', 89)
    > INSERT INTO Sometable VALUES ('var1', '2003-06-12T06:00:28', 56)
    > INSERT INTO Sometable VALUES ('var1', '2003-06-12T06:00:37', 23)
    > INSERT INTO Sometable VALUES ('var1', '2003-06-12T06:00:50', 23)
    >
    > DECLARE samp_period INTEGER
    > SET samp_period = 10
    >
    > SELECT S1.*
    > FROM Sometable AS S1
    > JOIN
    > (SELECT variable, MIN(date_hour) AS date_hour
    > FROM Sometable
    > GROUP BY variable, FLOOR(DATEDIFF(SECOND, '20000101', date_hour) /
    > samp_period)) AS S2
    > ON S1.variable = S2.variable AND S1.date_hour = S2.date_hour
    >
    > --
    > David Portas
    > ------------
    > Please reply only to the newsgroup
    > --
    >
    > "herve maillard" <rvmaillardfree.fr> wrote in message
    > news:3f054c62$0$5260$626a54cenews.free.fr...
    > > Hi,
    > >
    > > I have the following table :
    > > Variable Date_Hour Value
    > > var1 12/06/03 06:00:00 18
    > > var1 12/06/03 06:00:05 21
    > > var1 12/06/03 06:00:17 23
    > > var1 12/06/03 06:00:23 89
    > > var1 12/06/03 06:00:28 56
    > > var1 12/06/03 06:00:37 23
    > > var1 12/06/03 06:00:50 23
    > >
    > > I need to extract values depending of a sampling time period (ie 1 value
    > > every 10 seconds)
    > >
    > > Result should be (with sample period =10 sec)
    > >
    > > Variable Date_Hour Value
    > > var1 12/06/03 06:00:00 18
    > > var1 12/06/03 06:00:17 23
    > > var1 12/06/03 06:00:28 56
    > > var1 12/06/03 06:00:37 23
    > > var1 12/06/03 06:00:50 23
    > >
    > > How can I do this ?
    > >
    > > Thanks for your help.
    > >
    > > H. MAILLARD
    > >
    > >
    > >
    > >
    > >
    >
    >

    herve maillard Guest

  4. #4

    Default Re: how to extract data filtering by a DateTime field (depending sample period time) ?

    herve
    Based on David's DDL here is other approach
    SELECT
    variable,
    ISNULL(
    (SELECT MIN(date_hour)
    FROM Sometable AS S3
    WHERE S3.date_hour >= S1.date_hour
    AND ISNULL(
    DATEDIFF(
    SECOND,
    S3.date_hour,
    (SELECT MIN(date_hour)
    FROM Sometable AS S4
    WHERE S4.date_hour > S3.date_hour)), 10) <= 10),date_hour)
    AS endtime
    FROM Sometable AS S1
    WHERE ISNULL(
    DATEDIFF(
    SECOND,
    (SELECT MAX(date_hour)
    FROM Sometable AS S2
    WHERE S2.date_hour < S1.date_hour),
    S1.date_hour),
    10) <= 10



    "herve maillard" <rvmaillardfree.fr> wrote in message
    news:3f054c62$0$5260$626a54cenews.free.fr...
    > Hi,
    >
    > I have the following table :
    > Variable Date_Hour Value
    > var1 12/06/03 06:00:00 18
    > var1 12/06/03 06:00:05 21
    > var1 12/06/03 06:00:17 23
    > var1 12/06/03 06:00:23 89
    > var1 12/06/03 06:00:28 56
    > var1 12/06/03 06:00:37 23
    > var1 12/06/03 06:00:50 23
    >
    > I need to extract values depending of a sampling time period (ie 1 value
    > every 10 seconds)
    >
    > Result should be (with sample period =10 sec)
    >
    > Variable Date_Hour Value
    > var1 12/06/03 06:00:00 18
    > var1 12/06/03 06:00:17 23
    > var1 12/06/03 06:00:28 56
    > var1 12/06/03 06:00:37 23
    > var1 12/06/03 06:00:50 23
    >
    > How can I do this ?
    >
    > Thanks for your help.
    >
    > H. MAILLARD
    >
    >
    >
    >
    >

    Uri Dimant Guest

Similar Threads

  1. Question Sample code to extract images x, y, width and height from a PDF doent.
    By pprasanthk in forum Brainstorming Area
    Replies: 0
    Last Post: February 25th, 08:34 AM
  2. Template Column data depending on form data
    By John Mackerras in forum ASP.NET Data Grid Control
    Replies: 0
    Last Post: November 15th, 01:05 PM
  3. Separated date and time or one datetime field
    By Krzysztof Piotrowski in forum PHP Development
    Replies: 0
    Last Post: April 24th, 07:12 PM
  4. extract data from adobe form field
    By Marites_Tolentino@adobeforums.com in forum Adobe Acrobat Windows
    Replies: 1
    Last Post: April 23rd, 06:54 AM
  5. Different template depending on a field in the database???
    By Gigital in forum ASP.NET Building Controls
    Replies: 0
    Last Post: April 2nd, 09:45 AM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139