Answered by:
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 3What 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 1Table - 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 3falsecount 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 1Table - 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 3falsecount 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