Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2003
    Posts
    41

    Unanswered: Update of Fact Table- Time value

    Hi
    I have two tables, the first table contains the actual time value, and the second table contains the time master. The time master is a dimension table and it has the records of all the date with 1 hour time interval. In other words, the for a given day, there would be 24 rows with one hour time interval. I want to update the timeid in table1 with the corresponding timeid in table2

    how to write an update statement to acheive this. i have given the sample two tables.

    Regards
    Jtamil

    Table 1
    RID LogTime Type TimeID
    1 2006-10-14 11:13:42.297 Success 6876
    2 2006-10-14 11:14:00.530 Error 6876
    3 2006-10-14 11:14:00.530 Success 6876
    4 2006-10-14 11:14:23.670 Success 6876
    5 2006-10-14 11:14:29.467 Error 6876
    6 2006-10-14 11:14:50.420 Error 6876
    7 2006-10-14 11:14:50.480 Success 6876
    8 2006-10-14 11:25:39.950 Success 6876
    9 2006-10-14 11:25:42.560 Error 6876
    10 2006-10-14 11:34:58.357 Error 6876

    Table 2
    TimeID the_date the_day
    6875 2005-10-14 11:00:00.000 Friday
    6876 2005-10-14 12:00:00.000 Friday

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    for RID=1, which one is the corresponding one? 6875 or 6876? what about for RID=10?

    and why do you want to update a more accurate time with a fuzzier time?

    is it because you want to count them by time interval? you don't have to destroy data in order to do aggregations by table2
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2003
    Posts
    41
    In this case for RID =1 to RID = 10 all the valus are 6876. The Table 1 is a fact table and table2 is a dimension table and I want the fact table with a value that is equivalent in dimension table

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    well, that explanation argues for a simple JOIN, not an UPDATE
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    Table 2 dates are one year removed from table one dates. Is this intentional?

    -- This is all just a Figment of my Imagination --

  6. #6
    Join Date
    Jan 2003
    Posts
    41
    Sorry, In Table 2 the value of year column should be 2006 instead of 2005. How to do a simple join. I tried the following way, but it is not retreiving any rows

    select logtime from table1 inner join table2 on logtime between the_date and dateadd(hh,-1,the_date)

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    try this --
    Code:
    select table1.logtime 
         , table2.the_date
      from table1 
    inner 
      join table2 as t2
        on table1.logtime >= t2.the_date
       and table1.logtime <
           ( select min(the_date)
               from table2
              where the_date > t2.the_date )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Quote Originally Posted by jtamil2001
    I tried the following way, but it is not retrieving any rows

    select logtime from table1 inner join table2 on logtime between the_date and dateadd(hh,-1,the_date)
    Specify the small amount first. Since you subtract an hour it should be specified 1st.
    Also note that between will include the boundaries. Subtract milliseconds instead.

    Edit: Added update statement
    update table1
    set TimeID = b.TimeID
    from table1 a, table2 b
    where a.LogTime between dateadd(ms,-3599997,b.the_date) and b.the_date
    Last edited by pdreyer; 11-30-06 at 05:42.

Posting Permissions

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