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

8 Years
Discussion Span
Last Post by 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?


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

/* 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.


This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.