Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2013
    Posts
    2

    Unanswered: SQL- Updating one table from another table

    Hi,
    I am fairly new to SQL and am not sure the best way to tackle this problem. I have two tables. One table "Seg" divides the day into 3 times segments for each studyID (SID). Each time segment start and ends at different times for each study ID.
    Code:
    SID	Seg1	        Seg2	         Seg3
    1	09:00:00	11:10:00	14:00:00
    2	09:30:00	12:00:00	16:00:00

    Table 2 "Data" holds data collected at every minute for each study id. I would like to update this table (Data) so it specifies which time segment each minute falls into based on table “Seg”. So it would have to read in the times from “Seg” and update the column Segment in my “data” table to reflect which segment category it falls under. I would like my “data” table to look like this after the query:
    Code:
    SID	Time	        Segment
    1	09:01:00	Seg1
    1	09:02:00	Seg1
    1	15:00:00	Seg3
    2	09:30:01	Seg1
    2	12:01:00	Seg2
    I know I can do this in Python using cursors, but can this be done in SQL?

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Because you've mixed data with metadata, this is more cumbersome in SQL than it would be if you had stored all of the data as data.

    One solution would be:
    Code:
    --  ptp  20131203  See http://www.dbforums.com/microsoft-sql-server/1698636-sql-updating-one-table-another-table.html
    
    CREATE TABLE #Seg (
       SID          INT         NOT NULL
       PRIMARY KEY (SID)
    ,  Seg1         TIME        NOT NULL
    ,  Seg2         TIME        NOT NULL
    ,  Seg3         TIME        NOT NULL
       )
    
    INSERT INTO #Seg
       VALUES
          (1, '09:00:00', '11:10:00', '14:00:00')
    ,     (2, '09:30:00', '12:00:00', '16:00:00')
    
    CREATE TABLE #Data (
       SID          INT         NOT NULL
       FOREIGN KEY (SID) REFERENCES #Seg (SID)
    ,  [Time]       TIME        NOT NULL
       PRIMARY KEY (SID, [Time])
       )
    
    INSERT INTO #Data
       VALUES
          (1, '09:01:00') --	Seg1
    ,     (1, '09:02:00') --	Seg1
    ,     (1, '15:00:00') --	Seg3
    ,     (2, '09:30:01') --	Seg1
    ,     (2, '12:01:00') --	Seg2
    
    SELECT d.SID, d.[Time]
    ,  CASE  --  Reverse engineer the metadata from the schema
          WHEN s.Seg1 <= d.[Time] AND d.[Time] < s.Seg2 THEN 'Seg1'
          WHEN s.Seg2 <= d.[Time] AND d.[Time] < s.Seg3 THEN 'Seg2'
    	  ELSE 'Seg3'
       END AS Segment
       FROM #Data AS d
       INNER JOIN #Seg AS s
          ON (s.SID = d.SID)
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Dec 2013
    Posts
    2
    This will be helpful- much appreciated!! thanks, I'll give it a try.
    I am curious as to how else I could have set this table up to be more efficient?? Although I only provided an example of 2 SID's and 3 segments; there are in fact hundred's of SID's, 20 different segments and millions of data points collected at every minute. If you have any suggestions.. I'm open to them!

    thanks again!

  4. #4
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1

    ISO temporal model

    The nature of time in the ISO temporal model is durations. These durations are half-open intervals. The means two times, the included start point and the missing terminal point.

    CREATE TABLE Segments
    (segment_id INTEGER NOT NULL,
    segment_seq INTEGER NOT NULL
    CHECK (segment_seq BETWEEN 1 AND 3),
    PRIMARY KEY (segment_id, segment_seq),
    seq_start_time TIME(1) NOT NULL,
    seq_end_time TIME (1) NOT NULL,
    CHECK (seq_start_time < seq_end_time));

    INSERT INTO Segments
    VALUES
    (1, 1, '09:00:00.0', '11:09:59.9'),
    (1, 2, '11:10:00.0', '13:59.59.9'),
    (1, 3, '14:00:00.0', '23:59:59.9'),

    (2, 1, '09:30:00.0', '11:59:59.9'),
    (2, 2, '12:00:00.0', '15:59.59.9'),
    (2, 3, '16:00:00.0', '23:59:59.9');

    Time values kept to TIME(0) will work fine with a simple BETWEEN preicate

Tags for this Thread

Posting Permissions

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