locked
Updating table A for each row in table B RRS feed

  • Question

  • User-2108997513 posted

     I have two tables:

    Table - FCount:

    failtask        startd              FalseCount
     AA        09/28/2009           1
     AA        10/05/2009           2
     AA        10/06/2009           4
     AA        10/07/2009           1
     AA        10/12/2009           1

    Table - FinalResult

    Task      StartDate            EndDate           FalseCount   SuccessCount
     AA         2009-09-27       2009-10-04               0                   8
     AA         2009-10-14       2009-10-11               0                   10
     AA         2009-10-11       2009-10-18               0                   20
     AA         2009-10-25       2009-11-01               0                   3

    What I want to do is update column FalseCount in FinalResult with the FalseCount from FCount table.  Thus, FalseCount in FinalResult table should contain the sum of all the false counts from FCount table that fall within that date range (which is a weekly date range).

    Right now, I have something like this:

    update

    #FinalResult set falsecount = (falsecount + ffcount)
    from (select failtask, convert(varchar,startd,101) as startd, count(*) as ffcount
           
    from #FCount 
            
    group by failtask, convert(varchar,startd,101)) as groupedquery
    where failtask = task and startd >= convert(varchar,startdate,101) and startd < convert(varchar,enddate,101)

     

    However, problem is that my count in the resulting table is incorrect.  Esentially, I want to iterate over each row in FCount and verify where it fits in FinalResult, and update FinalResult.falsecount appropriately.

    Any help would be appreciated!

    Thanks!

    Friday, October 30, 2009 4:57 PM

Answers

  • User2011918074 posted

    Table - FCount:

    failtask        startd              FalseCount
     AA        09/28/2009           1
     AA        10/05/2009           2
     AA        10/06/2009           4
     AA        10/07/2009           1
     AA        10/12/2009           1

    Table - FinalResult

    Task      StartDate            EndDate           FalseCount   SuccessCount
     AA         2009-09-27       2009-10-04               0                   8
     AA         2009-10-14       2009-10-11               0                   10
     AA         2009-10-11       2009-10-18               0                   20
     AA         2009-10-25       2009-11-01               0                   3

    falsecount does not exist in #FCount table. it must be created with counts -> which gives me #FCount table above.

    Since FalseCount does not exist in Fcount table, I think you gave an incorrect table structure.

    What I want to do is update column FalseCount in FinalResult with the FalseCount from FCount table.  Thus, FalseCount in FinalResult table should contain the sum of all the false counts from FCount table that fall within that date range (which is a weekly date range).

    Please refer to the following sample:

    CREATE TABLE #FCOUNT (
      FAILTASK VARCHAR(10),
      STARTD   DATETIME)
    
    CREATE TABLE #FINALRESULT (
      TASK       VARCHAR(10),
      STARTDATE  DATETIME,
      ENDDATE    DATETIME,
      FALSECOUNT INT)
    
    INSERT INTO #FCOUNT
    VALUES     ('AA',
                '2009-09-28')
    
    INSERT INTO #FCOUNT
    VALUES     ('AA',
                '2009-10-05')
    
    INSERT INTO #FCOUNT
    VALUES     ('AA',
                '2009-10-06')
    
    INSERT INTO #FCOUNT
    VALUES     ('AA',
                '2009-10-07')
    
    INSERT INTO #FCOUNT
    VALUES     ('AA',
                '2009-10-12')
    
    INSERT INTO #FINALRESULT
               (TASK,
                STARTDATE,
                ENDDATE)
    VALUES     ('AA',
                '2009-09-27',
                '2009-10-04')
    
    INSERT INTO #FINALRESULT
               (TASK,
                STARTDATE,
                ENDDATE)
    VALUES     ('AA',
                '2009-10-04',
                '2009-10-11')
    
    INSERT INTO #FINALRESULT
               (TASK,
                STARTDATE,
                ENDDATE)
    VALUES     ('AA',
                '2009-10-11',
                '2009-10-18')
    
    UPDATE #FINALRESULT
    SET    FALSECOUNT = (SELECT COUNT(* )
                         FROM   #FCOUNT
                         WHERE  FAILTASK = TASK
                                AND (STARTD >= STARTDATE
                                     AND STARTD < ENDDATE)) 

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, November 4, 2009 2:03 AM

All replies

  • User1096912014 posted

    update #FinalResult set FalseCount = FalseCount + GroupQuery.FFCount from #FinalResult

    inner join (select FailTask, StartD, sum(FalseCount) as FFCount from #FCount group by  FailTask, StartD) GroupQuery on #FinalResult.Task = GroupQuery.FailTask and GroupQuery.StartD between #FinalResult.StartDate and #FinalResult.EndDate

    Friday, October 30, 2009 5:12 PM
  • User-2108997513 posted

    I'm still getting the same result in my #FinalResult table (i.e. the incorrect sum)

    I've changed your: "select FailTask, StartD, sum(FalseCount) as FFCount from #FCount group by  FailTask, StartD"     to   "select FailTask, StartD, count(*) as FFCount from #FCount group by  FailTask, StartD" since falsecount does not exist in #FCount table.. it must be created with counts -> which gives me #FCount table above.

    Rest, I've left as is, and result is the same as my original update query.

    Friday, October 30, 2009 6:02 PM
  • User2011918074 posted

    Table - FCount:

    failtask        startd              FalseCount
     AA        09/28/2009           1
     AA        10/05/2009           2
     AA        10/06/2009           4
     AA        10/07/2009           1
     AA        10/12/2009           1

    Table - FinalResult

    Task      StartDate            EndDate           FalseCount   SuccessCount
     AA         2009-09-27       2009-10-04               0                   8
     AA         2009-10-14       2009-10-11               0                   10
     AA         2009-10-11       2009-10-18               0                   20
     AA         2009-10-25       2009-11-01               0                   3

    falsecount does not exist in #FCount table. it must be created with counts -> which gives me #FCount table above.

    Since FalseCount does not exist in Fcount table, I think you gave an incorrect table structure.

    What I want to do is update column FalseCount in FinalResult with the FalseCount from FCount table.  Thus, FalseCount in FinalResult table should contain the sum of all the false counts from FCount table that fall within that date range (which is a weekly date range).

    Please refer to the following sample:

    CREATE TABLE #FCOUNT (
      FAILTASK VARCHAR(10),
      STARTD   DATETIME)
    
    CREATE TABLE #FINALRESULT (
      TASK       VARCHAR(10),
      STARTDATE  DATETIME,
      ENDDATE    DATETIME,
      FALSECOUNT INT)
    
    INSERT INTO #FCOUNT
    VALUES     ('AA',
                '2009-09-28')
    
    INSERT INTO #FCOUNT
    VALUES     ('AA',
                '2009-10-05')
    
    INSERT INTO #FCOUNT
    VALUES     ('AA',
                '2009-10-06')
    
    INSERT INTO #FCOUNT
    VALUES     ('AA',
                '2009-10-07')
    
    INSERT INTO #FCOUNT
    VALUES     ('AA',
                '2009-10-12')
    
    INSERT INTO #FINALRESULT
               (TASK,
                STARTDATE,
                ENDDATE)
    VALUES     ('AA',
                '2009-09-27',
                '2009-10-04')
    
    INSERT INTO #FINALRESULT
               (TASK,
                STARTDATE,
                ENDDATE)
    VALUES     ('AA',
                '2009-10-04',
                '2009-10-11')
    
    INSERT INTO #FINALRESULT
               (TASK,
                STARTDATE,
                ENDDATE)
    VALUES     ('AA',
                '2009-10-11',
                '2009-10-18')
    
    UPDATE #FINALRESULT
    SET    FALSECOUNT = (SELECT COUNT(* )
                         FROM   #FCOUNT
                         WHERE  FAILTASK = TASK
                                AND (STARTD >= STARTDATE
                                     AND STARTD < ENDDATE)) 

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, November 4, 2009 2:03 AM