DTR Query - 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)

DTR Query

Author

Topic

jonasalbert20
Constraint Violating Yak Guru

300 Posts

Posted - 2005-02-16 : 19:16:54

Hello guys! i Have this problem....

I have this data on my tblDTR

flgReg---emp_id---dtr_date-----login_am--------------logout_am------------HrsRendered------dummyOTHrs
----------------------------------------------------------------------------------------------------

1--------11-------2/14/2005----2/14/2005 6:40:03 AM--2/14/2005 5:44:03 PM-------8----------1:30:0---
1--------11-------2/15/2005----2/15/2005 6:44:03 AM--2/15/2005 11:30:00 AM------3:30:0-----0:0:0----
1--------11-------2/16/2005----2/16/2005 6:46:03 AM--2/16/2005 5:34:06 PM-------8----------1:30:0---
1--------11-------2/17/2005----2/17/2005 6:41:03 AM--2/17/2005 2:30:00 PM-------6:30-------0:0:0----
1--------11-------2/18/2005----2/18/2005 6:39:03 AM--2/18/2005 6:03:00 PM-------8----------2:0:0----
1--------11-------2/19/2005----    N U L L         --    N U L L         ---N U L L--------N U L L--

1--------22-------2/14/2005----2/14/2005 6:38:03 AM--2/14/2005 5:40:03 PM-------8----------1:30:0---
1--------22-------2/15/2005----2/15/2005 6:35:03 AM--2/15/2005 5:44:03 PM-------8----------1:30:0---
1--------22-------2/16/2005----2/16/2005 6:33:03 AM--2/16/2005 5:38:03 PM-------8----------1:30:0---
1--------22-------2/17/2005----2/17/2005 6:34:03 AM--2/17/2005 5:34:03 PM-------8----------1:30:0---
1--------22-------2/18/2005----2/18/2005 6:31:03 AM--2/18/2005 6:19:03 PM-------8----------2:0:0----
1--------22-------2/19/2005----    N U L L         --    N U L L         ---N U L L--------N U L L--

First let me elaborate this specs...

1. The constant working hours for every employee is 8 hours below it is an undertime of course.

2. Working office hours is from 8:00:00 AM to 5:00:00 PM With 1 hour lunch break from 12:00:00 PM
to 1:00:00 PM. Lunch break is not included in the computation for total hours rendered although an employee
is being payed for it.

3. The fields HrsRendered and dummyOTHrs are virtuals it doesn't appear actually on the table its for illustration
purposes only.

4. Note: The company set a new DTR working hrs from 7:00 AM to 5:30 PM from Monday upto Thursday, 7:00 AM to 6:00 PM
for Fridays. However, the 8 hours constant working is not being affected. Notice that the employee renders a 1:30 hrs
overtime (please refer to dummyOTHrs), but it will not be carried as overtime. Instead, the extra hrs rendered by an
employee from monday to friday will be credited for saturday's ("2/19/2005") dtr's date. Please don't forget to add the
1 hr lunch break on the DTR if the employee's total dummyOTHrs is greater than 4 hrs which means he is present in the morning.
If an employee had undertime? there is no dummyOTHrs computation, and only those records with flgReg equals to 1 are affected.

RESULT SHOULD LOOK LIKE THIS...

flgReg---emp_id---dtr_date-----login_am--------------logout_am------------HrsRendered------dummyOTHrs
----------------------------------------------------------------------------------------------------

1--------11-------2/14/2005----2/14/2005 6:40:03 AM--2/14/2005 5:44:03 PM-------8----------1:30:0---
1--------11-------2/15/2005----2/15/2005 6:44:03 AM--2/15/2005 11:30:00 AM------3:30:0-----0:0:0----
1--------11-------2/16/2005----2/16/2005 6:46:03 AM--2/16/2005 5:34:06 PM-------8----------1:30:0---
1--------11-------2/17/2005----2/17/2005 6:41:03 AM--2/17/2005 2:30:00 PM-------6:30-------0:0:0----
1--------11-------2/18/2005----2/18/2005 6:39:03 AM--2/18/2005 6:03:00 PM-------8----------2:0:0----
1--------11-------2/19/2005----2/19/2005 8:00:00 AM--2/19/2005 2:00:00 PM-------6----------0:0:0----

1--------22-------2/14/2005----2/14/2005 6:38:03 AM--2/14/2005 5:40:03 PM-------8----------1:30:0---
1--------22-------2/15/2005----2/15/2005 6:35:03 AM--2/15/2005 5:44:03 PM-------8----------1:30:0---
1--------22-------2/16/2005----2/16/2005 6:33:03 AM--2/16/2005 5:38:03 PM-------8----------1:30:0---
1--------22-------2/17/2005----2/17/2005 6:34:03 AM--2/17/2005 5:34:03 PM-------8----------1:30:0---
1--------22-------2/18/2005----2/18/2005 6:31:03 AM--2/18/2005 5:39:03 PM-------8----------2:0:0----
1--------22-------2/19/2005----2/19/2005 8:00:00 AM--2/19/2005 5:00:00 PM-------8----------0:0:0----

TNX IN ADVANCE...

Want Philippines to become 1st World COuntry? Go for World War 3...

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-02-16 : 20:27:44

If you posted the DDL for your table, and sample data with INSERT INTO statements, then we'd be able to help you.

Tara

jonasalbert20
Constraint Violating Yak Guru

300 Posts

Posted - 2005-02-16 : 20:56:34

tnx tara!


DECLARE @tblDTR TABLE ([flgReg] bit,
			[emp_id] varchar(50),
			[dtr_date] smalldatetime,
			[login_am] datetime,
			[logout_am] datetime)		

INSERT into @tblDTR 
select 1, '11', '2/14/2005', '2/14/2005 6:40:03 AM', '2/14/2005 5:44:03 PM'
union
select 1, '11', '2/15/2005', '2/15/2005 6:44:03 AM', '2/15/2005 11:30:00 AM'
union
select 1, '11', '2/16/2005', '2/16/2005 6:46:03 AM', '2/16/2005 5:34:06 PM'
union
select 1, '11', '2/17/2005', '2/17/2005 6:41:03 AM', '2/17/2005 2:30:00 PM'
union
select 1, '11', '2/18/2005', '2/18/2005 6:39:03 AM', '2/18/2005 6:03:00 PM'
union
select 1, '11', '2/19/2005', null,null
union
select 1, '22', '2/14/2005', '2/14/2005 6:38:03 AM', '2/14/2005 5:40:03 PM'
union
select 1, '22', '2/15/2005', '2/15/2005 6:35:03 AM', '2/15/2005 5:44:03 PM'
union
select 1, '22', '2/16/2005', '2/16/2005 6:33:03 AM', '2/16/2005 5:38:03 PM'
union
select 1, '22', '2/17/2005', '2/17/2005 6:34:03 AM', '2/17/2005 5:34:03 PM'
union
select 1, '22', '2/18/2005', '2/18/2005 6:31:03 AM', '2/18/2005 6:00:00 PM'
union
select 1, '22', '2/19/2005', null, null

:D

Want Philippines to become 1st World COuntry? Go for World War 3...

jonasalbert20
Constraint Violating Yak Guru

300 Posts

Posted - 2005-02-16 : 22:19:13

Let me show my code...

I'am ashamed to show my statement but im just finding ways to add those totals hours for saturdays.

----------------------------------------------------------------------------------------------



declare @constLogTime			as smalldatetime,
	@constLogOutMonThu		as smalldatetime,
	@constLogOutFri			as smalldatetime,
	@constHrLunchBreak		as smalldatetime,
	@constNoonBreak			as smalldatetime,
	@constTimeBackAfterNNBrk	as smalldatetime,
	@constWorkingHrs		as smalldatetime,
	@constZeroTime			as smalldatetime
	

SET @constLogTime = '7:00:00 AM'
SET @constLogOutMonThu = '5:30:00 PM'
SET @constLogOutFri = '6:00:00 PM'

SET @constNoonBreak = '12:00:00 PM'
SET @constTimeBackAfterNNBrk = '1:00:00 PM'
SET @constHrLunchBreak = '01:00:00.000'

SET @constWorkingHrs = '08:00:00.000'
set @constZeroTime = '00:00:00.000' 

DECLARE @tblDTR1 TABLE ([flgReg] bit,
			[emp_id] varchar(50),
			[dtr_date] smalldatetime,
			[login_am] datetime,
			[logout_am] datetime)		
INSERT into @tblDTR1 
select 1, '11', '2/14/2005', '2/14/2005 6:40:03 AM', '2/14/2005 5:44:03 PM'
union
select 1, '11', '2/15/2005', '2/15/2005 6:44:03 AM', '2/15/2005 11:30:00 AM'
union
select 1, '11', '2/16/2005', '2/16/2005 6:46:03 AM', '2/16/2005 5:34:06 PM'
union
select 1, '11', '2/17/2005', '2/17/2005 6:41:03 AM', '2/17/2005 2:30:00 PM'
union
select 1, '11', '2/18/2005', '2/18/2005 6:39:03 AM', '2/18/2005 6:03:00 PM'
union
select 1, '11', '2/19/2005', null,null
union
select 1, '22', '2/14/2005', '2/14/2005 6:38:03 AM', '2/14/2005 5:40:03 PM'
union
select 1, '22', '2/15/2005', '2/15/2005 6:35:03 AM', '2/15/2005 5:44:03 PM'
union
select 1, '22', '2/16/2005', '2/16/2005 6:33:03 AM', '2/16/2005 5:38:03 PM'
union
select 1, '22', '2/17/2005', '2/17/2005 6:34:03 AM', '2/17/2005 5:34:03 PM'
union
select 1, '22', '2/18/2005', '2/18/2005 6:31:03 AM', '2/18/2005 6:00:00 PM'
union
select 1, '22', '2/19/2005', null, null

SELECT  emp_id, dtr_date, 	
	login_am,
	logout_am,
	case when datename(weekday, dtr_date) = 'Friday' then
				case when logout_am >= convert(varchar,dtr_date, 101) + ' ' + @constLogOutFri then 
						cast(datepart(hh,(@constLogOutFri - @constLogTime) - @constHrLunchBreak) as varchar(2)) + ':' + 
						cast(datepart(mi,(@constLogOutFri - @constLogTime) - @constHrLunchBreak) as varchar(2)) + ':' + 
						cast(datepart(ss,(@constLogOutFri - @constLogTime) - @constHrLunchBreak) as varchar(2))
				    else
					case when logout_am >= @constNoonBreak and logout_am <= @constTimeBackAfterNNBrk then
						cast(datepart(hh,(logout_am - @constLogTime) ) as varchar(2)) + ':' + 
						cast(datepart(mi,(logout_am - @constLogTime) ) as varchar(2)) + ':' + 
						cast(datepart(ss,(logout_am - @constLogTime) ) as varchar(2))
					else
						cast(datepart(hh,(logout_am - @constLogTime) - @constHrLunchBreak) as varchar(2)) + ':' + 
						cast(datepart(mi,(logout_am - @constLogTime) - @constHrLunchBreak) as varchar(2)) + ':' + 
						cast(datepart(ss,(logout_am - @constLogTime) - @constHrLunchBreak) as varchar(2))		
					end
				end
	     else
				case when logout_am >= convert(varchar,dtr_date, 101) + ' ' + @constLogOutMonThu then
						cast(datepart(hh,(@constLogOutMonThu - @constLogTime) - @constHrLunchBreak) as varchar(2)) + ':' +
						cast(datepart(mi,(@constLogOutMonThu - @constLogTime) - @constHrLunchBreak) as varchar(2)) + ':' +
						cast(datepart(ss,(@constLogOutMonThu - @constLogTime) - @constHrLunchBreak) as varchar(2))
				    else
					case when logout_am >= @constNoonBreak and logout_am <= @constTimeBackAfterNNBrk then
						cast(datepart(hh,(logout_am - @constLogTime) ) as varchar(2)) + ':' + 
						cast(datepart(mi,(logout_am - @constLogTime) ) as varchar(2)) + ':' + 
						cast(datepart(ss,(logout_am - @constLogTime) ) as varchar(2))						
					else
						cast(datepart(hh,(logout_am - @constLogTime) - @constHrLunchBreak) as varchar(2)) + ':' + 
						cast(datepart(mi,(logout_am - @constLogTime) - @constHrLunchBreak) as varchar(2)) + ':' + 
						cast(datepart(ss,(logout_am - @constLogTime) - @constHrLunchBreak) as varchar(2))
					end
				end
	end as HrsRendered,
	case when datename(weekday, dtr_date) = 'Friday' then 
				case when logout_am >= convert(varchar,dtr_date, 101) + ' ' + @constLogOutFri then 
						cast(datepart(hh,((@constLogOutFri - @constLogTime) - @constHrLunchBreak) - @constWorkingHrs ) as varchar(2)) + ':' + 
						cast(datepart(mi,((@constLogOutFri - @constLogTime) - @constHrLunchBreak) - @constWorkingHrs) as varchar(2)) + ':' + 
						cast(datepart(ss,((@constLogOutFri - @constLogTime) - @constHrLunchBreak) - @constWorkingHrs) as varchar(2))
				else 				
				        case when logout_am >= @constNoonBreak and logout_am <= @constTimeBackAfterNNBrk then
						cast(datepart(hh,((logout_am - @constLogTime)) - @constWorkingHrs ) as varchar(2)) + ':' + 
						cast(datepart(mi,((logout_am - @constLogTime)) - @constWorkingHrs ) as varchar(2)) + ':' + 
						cast(datepart(ss,((logout_am - @constLogTime)) - @constWorkingHrs ) as varchar(2))

					else
						cast(datepart(hh,((logout_am - @constLogTime) - @constHrLunchBreak) - @constWorkingHrs) as varchar(2)) + ':' + 
						cast(datepart(mi,((logout_am - @constLogTime) - @constHrLunchBreak) - @constWorkingHrs) as varchar(2)) + ':' + 
						cast(datepart(ss,((logout_am - @constLogTime) - @constHrLunchBreak) - @constWorkingHrs) as varchar(2))		
					end							
				end
	     else
				case when logout_am >= convert(varchar,dtr_date, 101) + ' ' + @constLogOutMonThu then 
					cast(datepart(hh,((@constLogOutMonThu - @constLogTime) - @constHrLunchBreak) - @constWorkingHrs ) as varchar(2)) + ':' + 
					cast(datepart(mi,((@constLogOutMonThu - @constLogTime) - @constHrLunchBreak) - @constWorkingHrs ) as varchar(2)) + ':' + 
					cast(datepart(ss,((@constLogOutMonThu - @constLogTime) - @constHrLunchBreak) - @constWorkingHrs ) as varchar(2))
				else 
					case when logout_am >= @constNoonBreak and logout_am <= @constTimeBackAfterNNBrk then
						cast(datepart(hh,(logout_am - @constLogTime) - @constWorkingHrs ) as varchar(2)) + ':' + 
						cast(datepart(mi,(logout_am - @constLogTime) - @constWorkingHrs) as varchar(2)) + ':' + 
						cast(datepart(ss,(logout_am - @constLogTime) - @constWorkingHrs) as varchar(2))						
					else
						cast(datepart(hh,((logout_am - @constLogTime) - @constHrLunchBreak) - @constWorkingHrs)  as varchar(2)) + ':' + 
						cast(datepart(mi,((logout_am - @constLogTime) - @constHrLunchBreak) - @constWorkingHrs) as varchar(2)) + ':' + 
						cast(datepart(ss,((logout_am - @constLogTime) - @constHrLunchBreak) - @constWorkingHrs) as varchar(2))
					end
				end
	end as ExcessHrsForSaturdayCredit
	into #tempDTRformComputationFinal
FROM @tblDTR1

select emp_id, dtr_date, 
	login_am, logout_am,
	HrsRendered as TotalHrsRendered, 
	case when HrsRendered > @constWorkingHrs then ExcessHrsForSaturdayCredit
	else
		'0:0:0'
	end as newHrs4Saturday
from #tempDTRformComputationFinal


drop table #tempDTRformComputationFinal

Want Philippines to become 1st World COuntry? Go for World War 3...

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-02-16 : 22:19:17

Your sample results seem to be rounded though your specs didn't mention it. Also, I'm not clear on which rules are affected and how if the flgReg is not 1. But this should get you most of the way there. I added an actualHrs column just as a sanity check:


Select
	flgReg
	,emp_id
	,dtr_date = convert(varchar(15), dtr_date, 101)
	,login_am = convert(varchar(26), login_am, 0)
	,logout_am = convert(varchar(26), logout_am, 0)
	,HrsRendered = convert(varchar(3), RegMinutes  / 60) + ':' + right('00' + convert(varchar(2), RegMinutes % 60),2)
	,OTHrs = convert(varchar(3), OTMinutes  / 60) + ':' + right('00' + convert(varchar(2), OTMinutes % 60),2)
	,actualHrs = convert(varchar(3), datediff(minute,login_am,logout_am)/60) + ':' + right('00' + convert(varchar(2), datediff(minute,login_am,logout_am)%60),2)
From
	(
	Select	flgReg
		,emp_id
		,dtr_date
		,login_am
		,logout_am
		,case when datediff(minute,login_am,logout_am) > 540 then 480
			when datediff(minute,login_am,logout_am) > 240 then datediff(minute,login_am,logout_am) - 60
			else datediff(minute,login_am,logout_am)
		 end as RegMinutes
		,case when datediff(minute,login_am,logout_am) > 540 then datediff(minute,login_am,logout_am) - 540
		 else 0
		 end as OTMinutes
	from @tblDTR1
	) m
Order by 2,3

edit: looks like you posted your code right before me...let me take a look...

Ok, I see. I'm not too close yet. But I'm tired...it's time for a cold one...

Be One with the Optimizer
TG

jonasalbert20
Constraint Violating Yak Guru

300 Posts

Posted - 2005-02-16 : 22:30:24

thnx TG!

Could you show me how you would update the DTR for "02/19/2005" by adding does OTHrs to fill in the entry for "02/19/2005".

Tnx again... I've learned a lot of techniques in here...

Pls use the update statement...

jonasalbert20
Constraint Violating Yak Guru

300 Posts

Posted - 2005-02-16 : 22:43:29

Thnx TG! your statement is GOOD...

I also want to see how to handle it without being rounded.

Want Philippines to become 1st World COuntry? Go for World War 3...

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-02-17 : 10:20:11

Ok jonasalbert20,
I think this covers all the rules, including adding OT hours to Saturday's hours, taking out lunchhour, Friday workday hours, etc...


Select
	flgReg
	,emp_id
	,dtr_date = convert(varchar(15), dtr_date, 101)
	,login_am = convert(varchar(26), login_am, 0)
	,logout_am = convert(varchar(26), logout_am, 0)
	,HrsRendered = 
		case 
		--Saturday's hours - Add weekly OT hours 
		when datename(weekday,dtr_date) = 'Saturday' then convert(varchar(3), (isNull(RegMinutes,0)+AddToSaturday) / 60) + ':' + right('00' + convert(varchar(2), (isNull(RegMinutes,0)+AddToSaturday) % 60),2)

		--non-Saturday hours
		else convert(varchar(3), RegMinutes/60) + ':' + right('00' + convert(varchar(2), RegMinutes%60),2)

		end
	--,OTHrs = convert(varchar(3), AddToSaturday/60) + ':' + right('00' + convert(varchar(2), AddToSaturday%60),2)
	--,AddToSaturday
	,actualHrs = convert(varchar(3), datediff(minute,login_am,logout_am)/60) + ':' + right('00' + convert(varchar(2), datediff(minute,login_am,logout_am)%60),2)
From
	(
	Select	reg.flgReg
		,reg.emp_id
		,reg.dtr_date
		,reg.login_am
		,reg.logout_am
		,case
			when datediff(minute,login_am,logout_am) > 
				case 
				when datename(weekday,dtr_date)='Friday' then 660 
				else 630 
				end 
			then 
				case 
				when datename(weekday,dtr_date)='Friday' then 600 
				else 570 
				end 
			else datediff(minute,login_am,logout_am)
			end as RegMinutes
		,ot.AddToSaturday
	from	@tblDTR as reg

	JOIN	(--One record per employee/week with OT totals
		Select	flgReg
			,emp_id
			,datepart(week,dtr_date) as [week]
			,AddToSaturday = 
				sum(case 
				when datediff(minute,login_am,logout_am) > 
					case 
					when datename(weekday,dtr_date)='Friday' then 660 
					else 630 
					end 
				then datediff(minute,login_am,logout_am) - 
					case 
					when datename(weekday,dtr_date)='Friday' then 660 
					else 630 
					end 
				else 0
				end)
		From	@tblDTR
		group by flgReg
			,emp_id
			,datepart(week,dtr_date)
		) as ot

		ON reg.flgReg = ot.flgReg
		AND reg.emp_id = ot.emp_id
		AND datepart(week, reg.dtr_date) = ot.[week]
	) m
Order by 2,3

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources