Anyway to use Having in this SQL STMT?

[Link82]

Hey everyone,

I'm working on something ridiculously convoluted right now and am so close to finishing, hopefully I can get some proper help here.

I have my base table, as so:

-- Insert statements for procedure here
	CREATE TABLE	#starter
	(	
		

		emplid char(6),
		acct_code char(128),
		div	char(3),
		payroll_sect char(3),
		name varchar(128),
		work_loc int,
		week int,
		hours_worked_in_a_week float, 
		hours_worked float,
		mins_worked float, 
		work_day_percentage float, 
		[B]time_code char(1), [/B]
		addtl_money float,
		city_code char(1),
		std_hrs float, --what their stand. hrs are
		
		std_hrs_base float, --at what rate they'll start getting ot
		date_worked datetime
	)

Yeah kind of big. This is pretty much raw data in here. Depending on the time_code, a user gets different types of time: 1.0 hrs, 1.5 hrs (overtime 'O'), 2.0 (double 'D'), and Differential ('I') and so on.

So depending on the time code, in a new temp table, I've been keeping track of what each employee has been earning (for a particular pay period).

Things get tricky with the part-timers. With part-timers, you have to see what their std hours are... they could be 10, 20, whatever (under 40). The hours over which they start earning OT (1.5 hrs) is 40. Each raw entry is also tagged to an acct_code. So again, if a part-timer's std hrs are 30 and he puts in for 5 hours as 'Over time', sorry buddy, but you gotta hit 40 before earning 1.5 hrs. So my system should take time_code like 'O', sum it, and put it under 1.0 hours. Now if they mark 15 hours as 'O', then my system should put 10 under 1.0 hrs (now he's earned 40), so 5 hours goes under 1.5.

For full-timers, it's not nearly as bad, I don't have to do the weekly check, because their standard hours are 40.

Back to part-timers. So the final table that holds the table is something like this:

CREATE TABLE #by_code
(
	name varchar(128),
	emplid int,
	div int,
	work_loc int,
	payroll_sect int,
	acct_code varchar(100),
	_1_0_hrs float,
	_1_5_hrs float,
	_2_0_hrs float, 
	_diff_hrs float,
	_eper_hrs float,
	_addtl_amt float
)

So far, the full-time cases work great. I have to group by acct_code, because they need to see what type of hours are coming out of what type of acct_codes.

Here is example sql code for a full-timer:

--step 1 1: for 40 hr custodians, 1.0 and 1.5, diff, eper, addtl amt is really easy!
INSERT INTO	#by_code (	_1_0_hrs, _1_5_hrs, _eper_hrs,
						div, emplid, name, payroll_sect, acct_code	)
SELECT	0, SUM(CONVERT(decimal, hours_worked) + (CONVERT(decimal, mins_worked)/60)
				+ (dbo.workDayPercentageStringToDecimalUsingDivision(work_day_percentage) * (std_hrs_base/5))),
		0, div, emplid, name, payroll_sect, acct_code
FROM	#starter
WHERE	std_hrs = std_hrs_base
AND		time_code like 'O'
GROUP BY	acct_code, emplid, name, div, payroll_sect

My job is to do something similar for the part-timer. I have to spit out proper 1.0 hrs and 1.5 hrs, depending on what they put in for the week. My problem is, if I do the weekly calculation, and two or more time_codes belong within a week, how do I keep track?

I was doing something CRAZY like this:

INSERT INTO	#by_code(	_1_0_hrs, _1_5_hrs, _2_0_hrs, _diff_hrs, _eper_hrs, _addtl_amt,
						div, emplid, name, payroll_sect, acct_code )
SELECT		SUM(CONVERT(decimal, a.hours_worked) + (CONVERT(decimal, a.mins_worked)/60)
			+ (dbo.workDayPercentageStringToDecimalUsingDivision(a.work_day_percentage) * (a.std_hrs_base/5))),
			0, 0, 0, 0, 0, a.div, a.emplid, a.name, a.payroll_sect, a.acct_code
FROM		#starter a

INNER JOIN	#starter b
ON			a.emplid = b.emplid
AND			a.week = b.week
HAVING		(
			(SUM(CONVERT(decimal, a.hours_worked) + (CONVERT(decimal, a.mins_worked)/60)
			+ (dbo.workDayPercentageStringToDecimalUsingDivision(a.work_day_percentage) * (a.std_hrs_base/5)))
			< (a.std_hrs_base - a.std_hrs))
			)
			


WHERE		a.std_hrs < a.std_hrs_base
and			(a.time_code like 'R' or a.time_code like 'O')

GROUP BY	a.acct_code, a.emplid, a.name, a.div, a.payroll_sect

I have never used the having clause. I have no idea how to and need to look it up some more. Basically if part-timer (std_hrs < std_hrs_base) and time_code is marked 'R' or 'O', given that in a week they've earned less than their std_hours_base (usually 40 hrs), then put all those hours in 1.0 and 0 for 1.5.

Can someone help me with this beast-like SQL (or it feels that way to me)? I'm having a hard time breaking it up...

Thanks. Oh and feel free to ask any questions, it's kind of my bed time and I tried to put it all together, but sometimes that just isn't the case!

[sknake]

Are you open to suggestions for another design or does it have to be this way? That is going to be a mess to maintain....

[Link82]

sknake--for part-timers? Sure, if the other way is a bit cleaner and easier to understand, please go for it. This way is kind of messy, but I'd be interested in k nowing how to make it work, for my own sql knowledge.

Whatever you have time for, tia.