Stored Proc - Hours banding 1

[crystalnewbie]

I need to create an hourly labor report that looks like:

Hour | # of hours worked
8am - 9am | 2.0
9am - 10am | 1.0
10am - 11am | .5
etc.

given that I have the following information:

EmployeeName | PunchIn | PunchOut
John Doe | 3/1/04 8am | 3/1/04 11:30am
Jane Doe | 3/1/04 8am | 3/1/04 8:59am


I think the solution would be to create a stored proc to transform the data above to look like the one below.

Employee Name 8am-9am 9am-10am 10am-11am ..
John Doe 1 1 0.5

My attempts to create this proc has been futile. Can someone please point me to the right direction. THanks.

 

[markVII]

You can re-write it as a stored procedure with OUT prameter.
---------------------------------------
create function format_data() RETURN VARCHAR(100)
as
BEGIN
declare @return_string VARCHAR(100)
select @return_string = EmployeeName + ' :' + DATEPART((PunchOut - PunchIn),HOUR) + ':' + GetDate() from TABLE
RETURN @return_string
END
-------------------------------------
NB: Check for syntactical correctness; maybe you'd need type conversion etc.

 

[mwolf00]

Does that really work?

I did it like this...

create table #myTable(
	empName		varchar(20)
	,inTime		smalldatetime
	,outTime	smallDatetime
)
INSERT #myTable VALUES ('johnDoe', '4/1/04 9 AM','4/1/04 11:30 AM')
INSERT #myTable VALUES ('johnDoe', '4/1/04 12:30 PM','4/1/04 5:00 PM')


ALTER PROC a_timeTest AS
SET NOCOUNT ON
	create table #timeCount(
		h8	float
		, h9	float
		, h10	float
		, h11	float
		, h12 	float
		, h13	float
		, h14	float
		, h15	float
		, h16	float
	)
	INSERT #timeCount VALUES(0,0,0,0,0,0,0,0,0)
	
	-- need an ID seed to count through table
	CREATE TABLE #record (
		empName		varchar(20)
		,inTime		smalldatetime
		,outTime	smallDatetime
		,idSeed		INT	IDENTITY
	)
	
	DECLARE @rowNum INT, @maxRow INT, @sHour INT, @eHour INT, @sMin float, @eMin float
	INSERT #record SELECT * FROM #myTable
	SELECT @maxRow = max(idSeed) FROM #record
	SELECT @rowNum = 1
	
	WHILE @rowNum <= @maxRow
	BEGIN
		SELECT @sHour = datePart(hh,inTime)
			,@eHour = datePart(hh,outTime)
			,@sMin = datePart(n,inTime)
			,@eMin = datePart(n,outTime)
		FROM #record WHERE idSeed = @rowNum
		IF (@sHour < 8 OR (@sHour = 8 AND @sMin = 0))  AND @eHour > 8 UPDATE #timeCount SET h8 = h8 + 1
		IF (@sHour < 9 OR (@sHour = 9 AND @sMin = 0))  AND @eHour > 9 UPDATE #timeCount SET h9 = h9 + 1
		IF (@sHour < 10 OR (@sHour = 10 AND @sMin = 0))  AND @eHour > 10 UPDATE #timeCount SET h10 = h10 + 1
		IF (@sHour < 11 OR (@sHour = 11 AND @sMin = 0))  AND @eHour > 11 UPDATE #timeCount SET h11 = h11 + 1
		IF (@sHour < 12 OR (@sHour = 12 AND @sMin = 0))  AND @eHour > 12 UPDATE #timeCount SET h12 = h12 + 1
		IF (@sHour < 13 OR (@sHour = 13 AND @sMin = 0))  AND @eHour > 13 UPDATE #timeCount SET h13 = h13 + 1
		IF (@sHour < 14 OR (@sHour = 14 AND @sMin = 0))  AND @eHour > 14 UPDATE #timeCount SET h14 = h14 + 1
		IF (@sHour < 15 OR (@sHour = 15 AND @sMin = 0))  AND @eHour > 15 UPDATE #timeCount SET h15 = h15 + 1
		IF (@sHour < 16 OR (@sHour = 16 AND @sMin = 0))  AND @eHour > 16 UPDATE #timeCount SET h16 = h16 + 1

		-- now you have to handle the hours where minutes count...		
		IF (@sHour = 8 AND @sMin > 0) AND @eHour > 8  UPDATE #timeCount SET h8 = h8 + (@sMin / 60)
		IF @sHour = 8 AND @eHour = 8 UPDATE #timeCount SET h8 = h8 + ((@eMin - @sMin) / 60)
		IF @eHour = 8 AND @eMin > 0 UPDATE #timeCount SET h8 = h8 + (@eMin/60)
		
		IF (@sHour = 9 AND @sMin > 0) AND @eHour > 9  UPDATE #timeCount SET h9 = h9 + (@sMin / 60)
		IF @sHour = 9 AND @eHour = 9 UPDATE #timeCount SET h9 = h9 + ((@eMin - @sMin) / 60)
		IF @eHour = 9 AND @eMin > 0 UPDATE #timeCount SET h9 = h9 + (@eMin/60)
		
		IF (@sHour = 10 AND @sMin > 0) AND @eHour > 10  UPDATE #timeCount SET h10 = h10 + (@sMin / 60)
		IF @sHour = 10 AND @eHour = 10 UPDATE #timeCount SET h10 = h10 + ((@eMin - @sMin) / 60)
		IF @eHour = 10 AND @eMin > 0 UPDATE #timeCount SET h10 = h10 + (@eMin/60)
		
		IF (@sHour = 11 AND @sMin > 0) AND @eHour > 11  UPDATE #timeCount SET h11 = h11 + (@sMin / 60)
		IF @sHour = 11 AND @eHour = 11 UPDATE #timeCount SET h11 = h11 + ((@eMin - @sMin) / 60)
		IF @eHour = 11 AND @eMin > 0 UPDATE #timeCount SET h11 = h11 + (@eMin/60)

		IF (@sHour = 12 AND @sMin > 0) AND @eHour > 12  UPDATE #timeCount SET h12 = h12 + (@sMin / 60)
		IF @sHour = 12 AND @eHour = 12 UPDATE #timeCount SET h12 = h12 + ((@eMin - @sMin) / 60)
		IF @eHour = 12 AND @eMin > 0 UPDATE #timeCount SET h12 = h12 + (@eMin/60)
		
		IF (@sHour = 13 AND @sMin > 0) AND @eHour > 13  UPDATE #timeCount SET h13 = h13 + (@sMin / 60)
		IF @sHour = 13 AND @eHour = 13 UPDATE #timeCount SET h13 = h13 + ((@eMin - @sMin) / 60)
		IF @eHour = 13 AND @eMin > 0 UPDATE #timeCount SET h13 = h13 + (@eMin/60)
		
		IF (@sHour = 14 AND @sMin > 0) AND @eHour > 14  UPDATE #timeCount SET h14 = h14 + (@sMin / 60)
		IF @sHour = 14 AND @eHour = 14 UPDATE #timeCount SET h14 = h14 + ((@eMin - @sMin) / 60)
		IF @eHour = 14 AND @eMin > 0 UPDATE #timeCount SET h14 = h14 + (@eMin/60)
		
		IF (@sHour = 15 AND @sMin > 0) AND @eHour > 15  UPDATE #timeCount SET h15 = h15 + (@sMin / 60)
		IF @sHour = 15 AND @eHour = 15 UPDATE #timeCount SET h15 = h15 + ((@eMin - @sMin) / 60)
		IF @eHour = 15 AND @eMin > 0 UPDATE #timeCount SET h15 = h15 + (@eMin/60)
		
		IF (@sHour = 16 AND @sMin > 0) AND @eHour > 16  UPDATE #timeCount SET h16 = h16 + (@sMin / 60)
		IF @sHour = 16 AND @eHour = 16 UPDATE #timeCount SET h16 = h16 + ((@eMin - @sMin) / 60)
		IF @eHour = 16 AND @eMin > 0 UPDATE #timeCount SET h16 = h16 + (@eMin/60)


		SELECT @rowNum = @rowNum + 1
	END
	SELECT * FROM #timeCount
	drop table #timeCount
	drop table #record
GO


EXEC a_timetest

I'll feel pretty dumb if the answer was that simple...

Programming today is a race between software engineers striving to build better and bigger idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning. - Rick Cook (No, I'm not Rick)

 

[TJRTech]

Hey, crystalnewbie, a general question?

Is this a custom app that gives the punch in, punch out entries in pairs? The reason I ask is because I worked on a similar project a year or so ago and all I had to work with were person, punch_date_time records, and had to inferr punch_ins and punch_outs.

A MAJOR pain that was.

TR

 

[tektipdjango]

Create a dayTime table
PHour integer
PTime dateTime

eg
PHour PTime
0 3/1/04 00:00
0 3/1/04 00:01
...
0 3/1/04 00:59
1 3/1/04 01:0O
...
24 3/1/04 23:59

then do

select
hour,
count(*)/60
from dayTime
inner join
PunchTable
on (PTime>punchIn and PTime<=PunchOut)
group by PHour

--------------------------------------------------
[highlight]Django[/highlight]
bug exterminator
tips'n tricks addict​

 

[crystalnewbie]

It's not a custom app and thankfully contains info as to what the punch ins and outs was for.

Thanks for all responses by the way.