Need help with an UPDATE stmt (that depends on result of two tables)

[Link82]

Hi All,

Re-doing a giant, old, crusty sp. So here's a questions for all of you. I have two temp tables: #temp_acct_codes and #temp_hour_tracker.

#temp_acct_codes contains these two columns in addition to a slew of others: week, hours, date_worked. #temp_hour_tracker contains week, hours_already_worked, 1_0_hrs, 1_5_hrs, 2_0_hrs, diff_hrs, eper_hrs, loc (different types of hours).

#temp_hour_tracker is suppose to hold the week (using date part) and sum any hours. The idea is to compare these two tables and
IF:
a) #temp_hour_tracker doesn't contain the week(s) that the other one does, then insert new row (insert week, hours, and other details).
b)if they have week in common, let's update hours_already_worked (sum it with existing number) in #temp_hour_tracker using cases.

That's not all for case b and gets more complicated, but I'm tackling case A. So for case A, I know I can do somethin glike the following:

--get case a entries (entries that don't exist in #temp_hour_tracker
select  *
from    #temp_acct_codes  a

outer left join #temp_hour_tracker b
on b.week = a.week

where b.week = null


--so collectively, would it be something like:
INSERT INTO #temp_hour_tracker
VALUES(_, _, _, and so on)
WHERE ( /* insert above query */

Ok, so I haven't an idea how to merge all this together. Would someone mind giving me a helping hand? TIA.

[sknake]

Can you post the create table statements with insert data?

[Link82]

Can you post the create table statements with insert data?

sknake, I'm not sure what you're asking...? Did you need me to post something additional?

[sknake]

I meant can you post the statements to create and populate the two tables you mentioned with sample data? I'm sure your issue will make more sense if I had visibility to the data you are working with

[Link82]

/* helper table, get all data we need (for one employee) from tblRecord and populate   #temp_acct_codes */
CREATE	TABLE #temp_acct_codes 
( 
	ibu char(2),
	mbu char(2),
	approp char(4),
	obj char(4),
	prog char(4),
	report_cat char(4),
	work_loc char(3),
	payroll_sect char(3),
	std_hrs int,
	date_worked datetime,
	hours_worked int,
	mins_worked int,
	work_day_percentage int,
	time_code char(3),
	week int
)

INSERT INTO		#temp_acct_codes(ibu, mbu, approp, obj, prog, report_cat, work_loc, pay_sect,
				date_worked, hours_worked, mins_worked, work_day_percentage, time_code, week)
SELECT DISTINCT IBU ,  MBU ,  Appropriation_Code ,  Object_Code , Program_Code , Report_Category_Code, 
				work_location, SUBSTRING( payroll_sect, 1, 1), Date_Worked, Hours_Worked, Mins_Worked,
				Work_Day_Percentage, Time_Code, DATEPART(week, Date_Worked)
FROM	tblRecord
WHERE	ssn = @SSN
AND		payroll_sect = @pay_sect
AND		Division = @div
AND		Finalized = 0
_______________________________

/* #temp_hour_tracker holds the sum of hours worked, along with micro details like the type of hours worked (1_0 hrs, diff hrs, eper hrs, etc */
CREATE	TABLE	#temp_hour_tracker
( 
	ssn char(9),
	division char(2), 
	pay_sect char(3), 
	currentPayrollDate datetime,
	week int,
	hours_already_worked float,
	_1_0_hrs float,
	_1_5_hrs float,
	_2_0_hrs float,
	_diff_hrs float,
	_eper_hrs float,
	addnl_money float,
	work_location int
)

/* At this point, #temp_hour_tracker holds the hours_already_worked from when an employee's time was "finalized".  Now, we want to take that unfinalized hours that are stored in #temp_acct_codes, and add them in to #temp_hour_tracker, and that's where I'm stuck */
/* I've created "week" in #temp_acct_codes to help.  Basically, all the 'week's that are stored in #temp_acct_codes need to be added to #temp_hour_tracker.  If #temp_hour_tracker  has that week, then take the hours worked from #temp_acct_codes and add them to what's already stored for that week in #temp_hour_tracker .  If not, INSERT INTO #temp_hour_tracker with the details */

I hope this all makes sense. If not, let me know and I can see what else I can do. This sucks because I'm re-vamping and old report which was written with FETCH/CURSOR/WHILE loops. Anyway, any help is appreciated.

Thanks.