Updating table A for each row in table B

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!

Answer 1

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

Answer 2

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.

Answer 3

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))