Date of Last 1000 Hours? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Date of Last 1000 Hours?

Author

Topic

ASP_DRUG_DEALER
Yak Posting Veteran

61 Posts

Posted - 2005-01-18 : 11:56:10

Hey all-
I've got a tricky question or at least to me it is. My boss has asked me to query the employee time database and find out what the date was when each employee began accumulating his\her last 1000 hours?

Example:
Doug, from today began accumulating his last 1000 hours july 14th, 2004 (he worked 8 hour days).
Billy, from today began accumulating his last 1000 hours august 15th 2004 (he worked 10 hour days).

It just for the last 1000 hours.

data sample
EMPID, HOURS, DATE
DD123, 8, 1/16/04
DD123, 8, 1/17/04
BB123, 8, 1/16/04
ETC...

Any ideas or help would be great.
Thanks,
Doug

nr
SQLTeam MVY

12543 Posts

Posted - 2005-01-18 : 12:08:58

something like (could be more efficient but first thought)
select *
from tbl t
where date >= (select max(date) from tbl t1 where t1.empid = t.empid and (select sum(hours) from tbl t2 where t2.date <= t1.date and t1.empid = t2.empid) >= 1000)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

ASP_DRUG_DEALER
Yak Posting Veteran

61 Posts

Posted - 2005-01-19 : 09:34:18

hmmmm....Way over my head

Easy on the self joins. I am only good for about one. Simple stuff like parent\child.

Should there be some type of order by clause on there also?

I really would like to use something slick like this, but I am afraid I may use a cursor.

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-01-19 : 13:21:24

Something like:

SELECT EMPID, MAX([DATE]) AS DT_START_ACC_1000
FROM
(
	SELECT
		e1.EMPID
		,e1.[DATE]
		--,SUM(e2.HOURS)
	FROM
		EMPLOYEES e1
		JOIN EMPLOYEES e2
			ON e1.EMPID = e2.EMPID
			AND e1.[DATE] <= e2.[DATE]
	GROUP BY
		e1.EMPID,
		e1.[DATE]
	HAVING
		SUM(e2.HOURS) <= 1000
	--ORDER BY
	--	d1.EMPID,
	--	d1.[DATE] DESC
) emp_accumulated_hours
GROUP BY
	EMPID

Uncomment and just run the inner query to get a feeling how it works.
If it works

rockmoose

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources