Updates based on date ranges

[dukeslater]

I have a table variable populated with teams, weeks, and start/end dates:

Team Location Week Start End Calls
113 New York 1 1/1 1/7
113 New York 2 1/8 1/14
113 New York 3 1/15 1/21
114 Boston 1 1/3 1/9
114 Boston 2 1/10 1/16

and so on.

I need to update the calls field with the sum of all calls during the range of start/end dates. These values are stored by individual date and employee:

Employee Date Calls
1000 1/1 123
1000 1/2 99
1001 1/3 141
1003 1/10 87

I need to use a third table to define the employees that are on each team

Location Team Emp
New York 113 1000
New York 113 1001
Boston 114 1002


Pretty straight forward, it seems, but I'm not figuring out a method for updating the call totals based on the date ranges - at least not one that isn't brutally slow. I'm a semi-newbie, but can anyone tell me the best way to handle this?

Thanks,

Duke

 

[amrita418]

Check this out:

update TableA 
set calls = P.TotalCalls
from TableA T,
(select    A.team 'Team' , A.week 'Week' , sum(B.calls) 'TotalCalls'
             from      TableA A,
                       TableB B,
                       TableC C
             where     A.teamid = C.team
                       and B.employee = C.emp
             group by  A.team, A.week) P
where P.team = T.team
      and P.week = T.week

PS: Code not tested.

Regards,
AA

 

[dukeslater]

Thanks for your prompt response - this runs fast, but we still have the problem of distributing those calls by the date ranges associated with each week. Currently each team has the exact same number of calls for each week - in other words, the total is right but it needs to be distributed across the date ranges:

Now:

Team Location Week Start End Calls
110 New York 1 1/1 1/6 1000
110 New York 2 1/7 1/13 1000
110 New York 3 1/14 1/20 1000
111 Boston 1 1/10 1/16 900

Needs to be:

Team Location Week Start End Calls
110 New York 1 1/1 1/6 400
110 New York 2 1/7 1/13 250
110 New York 3 1/14 1/20 350
111 Boston 1 1/10 1/16 900


I can't see how this can be done without some type of cursor or loops, but maybe it can. If not, what would be the best bet to accomplish this performance-wise?

Thanks,

Duke

 

[vongrunt]

Wednesday is not good day for cursors . So:

update X
set Calls = TotalCalls
from TableA X
inner join
(	select A.Team, A.Location, A.Week, sum(B.Calls) as TotalCalls
	from TableA A
	inner join TableB B on B.[Date] between A.Start and A.[End]
	inner join TableC C on C.Emp = B.Employee and C.Team=A.Team and C.Location=A.Location
	group by A.Team, A.Location, A.Week
) Y on X.Team=Y.Team and X.Location=Y.Location and X.Week=Y.week

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[dukeslater]

Thank you Mr. VonGrunt!

I was able to get it down to under 30 seconds by consolidating the table B and C data into another temp table, then joining it to table A.

However, when time allows I'll try your suggestion for comparison sake - it's a little cleaner, and I'll probably use it in the future.

Duke