Results 1 to 3 of 3

Thread: Tough Query

  1. #1
    Join Date
    Jun 2003
    Location
    Montreal
    Posts
    38

    Unanswered: Tough Query

    Hi,

    I have a table with a couple of million rows. Each row as its datetime field spilt between numerous columns (year, month, day, hour... it's a datawarehouse fact table).

    Here's what I'd like to do in a query:

    If a value is missing (in the column value) it's set to -999. What I'd like to do is to set this column to the value of the hour before this one. For example, here's some data before and after an update:

    BEFORE:
    MONTH DAY HOUR VALUE
    ==================
    01 31 21 33.5
    01 31 22 -999
    03 07 24 87.6
    03 08 01 -999

    AFTER:
    MONTH DAY HOUR VALUE
    ==================
    01 31 21 33.5
    01 31 22 33.5
    03 07 24 87.6
    03 08 01 87.6

    Here's the complete design of my table
    IDENT nvarchar 50
    THEDATE datetime
    THEDAY int
    THEMONTH int
    THEYEAR int
    THEVALUE real
    SOURCE nvarchar 255

    How can I do this?

    Thanks,

    Skip.

  2. #2
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    Try this idea:

    drop table #test
    create table #test(tmonth int, tday int,thour int,tvalue real)
    insert #test values(1, 31, 21, 33.5)
    insert #test values(1, 31, 22,-999)
    insert #test values(3, 7, 21, 87.5)
    insert #test values(3, 8, 23,-999)
    go
    CREATE FUNCTION todate(@month int, @day int,@hour int)
    RETURNS datetime
    AS
    BEGIN
    RETURN cast(cast(@month as varchar)+'/'+cast(@day as varchar)+'/2004 '+cast(@hour as varchar)+':00:00.000' as datetime)
    END
    go
    update #test set tvalue=(select tvalue from #test where dbo.todate(tmonth,tday,thour)=
    (select max(dbo.todate(t2.tmonth,t2.tday,t2.thour))
    from #test t2
    where dbo.todate(t2.tmonth,t2.tday,t2.thour)<dbo.todate( t1.tmonth,t1.tday,t1.thour)
    and t2.tvalue<>-999))
    from #test t1
    where tvalue=-999
    go
    select * from #test

  3. #3
    Join Date
    Feb 2004
    Location
    Rhode Island
    Posts
    69

    Re: Tough Query

    Originally posted by Skippy_sc
    Hi,

    I have a table with a couple of million rows. Each row as its datetime field spilt between numerous columns (year, month, day, hour... it's a datawarehouse fact table).

    Here's what I'd like to do in a query:

    If a value is missing (in the column value) it's set to -999. What I'd like to do is to set this column to the value of the hour before this one. For example, here's some data before and after an update:

    BEFORE:
    MONTH DAY HOUR VALUE
    ==================
    01 31 21 33.5
    01 31 22 -999
    03 07 24 87.6
    03 08 01 -999

    AFTER:
    MONTH DAY HOUR VALUE
    ==================
    01 31 21 33.5
    01 31 22 33.5
    03 07 24 87.6
    03 08 01 87.6

    Here's the complete design of my table
    IDENT nvarchar 50
    THEDATE datetime
    THEDAY int
    THEMONTH int
    THEYEAR int
    THEVALUE real
    SOURCE nvarchar 255

    How can I do this?

    Thanks,

    Skip.
    Your test data does not include the year. Do you like assume the current year.?

Posting Permissions

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