How to stop week hours to continue to next week

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 2008 Forums

Transact-SQL (2008)

How to stop week hours to continue to next week

Author

Topic

VeeranjaneyuluAnnapureddy
Posting Yak Master

169 Posts

Posted - 2014-01-29 : 01:11:01

Hi all,

I am adding week hrs using while loop , but it can continue next week hrs also . please help

I can get every week start hrs while update @tem1 table

employeeid reportdate reportat leftat toaccount wrh rn
1290 2014-01-29 00:00:00.000 09:30 19:15 560 0 1
1290 2014-01-28 00:00:00.000 09:00 18:45 558 0 2
1290 2014-01-27 00:00:00.000 09:00 18:45 558 558 3
1290 2014-01-25 00:00:00.000 08:00 10:00 120 0 4 -- week end
1290 2014-01-24 00:00:00.000 09:17 18:45 541 0 5
1290 2014-01-23 00:00:00.000 09:19 18:46 546 0 6
1290 2014-01-22 00:00:00.000 09:17 18:47 545 0 7
1290 2014-01-21 00:00:00.000 09:16 18:35 526 0 8
1290 2014-01-20 00:00:00.000 09:18 18:55 543 543 9

My loop statement

while(select MAX(wrh) from @tem1 where wrh = 0) < 1
begin
update @tem1
set wrh = (select toaccount from @tem1
where reportdate = (select min(reportdate) from @tem1 where wrh = 0))+(select max(wrh) from @tem1)
where wrh = (select max(wrh) from @tem1 where wrh = 0 )
and reportdate = (select min(reportdate) from @tem1 where wrh = 0)
end

this is the result while executing loop statement .

employeeid reportdate reportat leftat deh drh weh wrh
1290 29 Jan 2014 09:30 19:15 008:00 09:20 024:00 065:54
1290 28 Jan 2014 09:00 18:45 008:00 09:18 016:00 056:34
1290 27 Jan 2014 09:00 18:45 008:00 09:18 008:00 09:18
1290 25 Jan 2014 08:00 10:00 005:00 02:00 045:00 047:16 -- week end
1290 24 Jan 2014 09:17 18:45 008:00 09:01 040:00 045:16
1290 23 Jan 2014 09:19 18:46 008:00 09:06 032:00 036:15
1290 22 Jan 2014 09:17 18:47 008:00 09:05 024:00 027:09
1290 21 Jan 2014 09:16 18:35 008:00 08:46 016:00 018:04
1290 20 Jan 2014 09:18 18:55 008:00 09:03 008:00 09:03

how to update only that week hrs , don't continue next week

sorry , my poor english

Veera

VeeranjaneyuluAnnapureddy
Posting Yak Master

169 Posts

Posted - 2014-01-29 : 04:51:19

This is my table data
EmployeeId ReportDate Direction PunchTime
1290 2014-01-20 00:00:00.000 1 2014-01-20 09:18:00.000
1290 2014-01-20 00:00:00.000 2 2014-01-20 12:59:00.000
1290 2014-01-20 00:00:00.000 1 2014-01-20 13:34:00.000
1290 2014-01-20 00:00:00.000 2 2014-01-20 16:31:00.000
1290 2014-01-20 00:00:00.000 1 2014-01-20 16:57:00.000
1290 2014-01-20 00:00:00.000 2 2014-01-20 18:52:00.000
1290 2014-01-20 00:00:00.000 1 2014-01-20 18:55:00.000
1290 2014-01-20 00:00:00.000 2 2014-01-20 18:55:00.000
1290 2014-01-21 00:00:00.000 1 2014-01-21 09:16:00.000
1290 2014-01-21 00:00:00.000 2 2014-01-21 12:59:00.000
1290 2014-01-21 00:00:00.000 1 2014-01-21 13:34:00.000
1290 2014-01-21 00:00:00.000 2 2014-01-21 16:33:00.000
1290 2014-01-21 00:00:00.000 1 2014-01-21 17:01:00.000
1290 2014-01-21 00:00:00.000 2 2014-01-21 18:35:00.000
1290 2014-01-22 00:00:00.000 1 2014-01-22 09:17:00.000
1290 2014-01-22 00:00:00.000 2 2014-01-22 13:09:00.000
1290 2014-01-22 00:00:00.000 1 2014-01-22 13:35:00.000
1290 2014-01-22 00:00:00.000 2 2014-01-22 16:40:00.000
1290 2014-01-22 00:00:00.000 1 2014-01-22 17:09:00.000
1290 2014-01-22 00:00:00.000 2 2014-01-22 18:47:00.000
1290 2014-01-23 00:00:00.000 1 2014-01-23 09:19:00.000
1290 2014-01-23 00:00:00.000 2 2014-01-23 13:08:00.000
1290 2014-01-23 00:00:00.000 1 2014-01-23 13:36:00.000
1290 2014-01-23 00:00:00.000 2 2014-01-23 16:22:00.000
1290 2014-01-23 00:00:00.000 1 2014-01-23 16:45:00.000
1290 2014-01-23 00:00:00.000 2 2014-01-23 18:46:00.000
1290 2014-01-24 00:00:00.000 1 2014-01-24 09:17:00.000
1290 2014-01-24 00:00:00.000 2 2014-01-24 13:01:00.000
1290 2014-01-24 00:00:00.000 1 2014-01-24 13:33:00.000
1290 2014-01-24 00:00:00.000 2 2014-01-24 16:28:00.000
1290 2014-01-24 00:00:00.000 1 2014-01-24 16:53:00.000
1290 2014-01-24 00:00:00.000 2 2014-01-24 18:50:00.000
1290 2014-01-25 00:00:00.000 1 2014-01-25 07:58:00.000
1290 2014-01-25 00:00:00.000 2 2014-01-25 10:04:00.000
1290 2014-01-27 00:00:00.000 1 2014-01-27 09:30:00.000
1290 2014-01-27 00:00:00.000 2 2014-01-27 12:59:00.000
1290 2014-01-27 00:00:00.000 1 2014-01-27 13:29:00.000
1290 2014-01-27 00:00:00.000 2 2014-01-27 16:15:00.000
1290 2014-01-27 00:00:00.000 1 2014-01-27 16:40:00.000
1290 2014-01-27 00:00:00.000 2 2014-01-27 18:39:00.000
1290 2014-01-28 00:00:00.000 1 2014-01-28 09:36:00.000
1290 2014-01-28 00:00:00.000 2 2014-01-28 11:17:00.000
1290 2014-01-28 00:00:00.000 1 2014-01-28 11:49:00.000
1290 2014-01-28 00:00:00.000 2 2014-01-28 12:53:00.000
1290 2014-01-28 00:00:00.000 1 2014-01-28 13:19:00.000
1290 2014-01-28 00:00:00.000 2 2014-01-28 16:22:00.000
1290 2014-01-28 00:00:00.000 1 2014-01-28 16:35:00.000
1290 2014-01-28 00:00:00.000 2 2014-01-28 18:45:00.000
1290 2014-01-29 00:00:00.000 1 2014-01-29 09:30:00.000
1290 2014-01-29 00:00:00.000 2 2014-01-29 13:01:00.000
1290 2014-01-29 00:00:00.000 1 2014-01-29 13:33:00.000
1290 2014-01-29 00:00:00.000 2 2014-01-29 16:30:00.000
1290 2014-01-29 00:00:00.000 1 2014-01-29 16:53:00.000
1290 2014-01-29 00:00:00.000 2 2014-01-29 19:15:00.000
1290 2014-01-30 00:00:00.000 1 2014-01-30 09:15:00.000
1290 2014-01-30 00:00:00.000 2 2014-01-30 13:01:00.000
1290 2014-01-30 00:00:00.000 1 2014-01-30 13:23:00.000
1290 2014-01-30 00:00:00.000 2 2014-01-30 16:40:00.000
1290 2014-01-30 00:00:00.000 1 2014-01-30 17:53:00.000
1290 2014-01-30 00:00:00.000 2 2014-01-30 18:55:00.000
1290 2014-01-31 00:00:00.000 1 2014-01-31 09:24:00.000
1290 2014-01-31 00:00:00.000 2 2014-01-31 12:58:00.000
1290 2014-01-31 00:00:00.000 1 2014-01-31 13:27:00.000
1290 2014-01-31 00:00:00.000 2 2014-01-31 16:35:00.000
1290 2014-01-31 00:00:00.000 1 2014-01-31 16:58:00.000
1290 2014-01-31 00:00:00.000 2 2014-01-31 18:45:00.000
1290 2014-02-01 00:00:00.000 1 2014-02-01 09:40:00.000
1290 2014-02-01 00:00:00.000 2 2014-02-01 13:15:00.000
1290 2014-02-03 00:00:00.000 1 2014-02-03 09:15:00.000
1290 2014-02-03 00:00:00.000 2 2014-02-03 12:59:00.000
1290 2014-02-03 00:00:00.000 1 2014-02-03 13:25:00.000
1290 2014-02-03 00:00:00.000 2 2014-02-03 16:30:00.000
1290 2014-02-03 00:00:00.000 1 2014-02-03 16:56:00.000
1290 2014-02-03 00:00:00.000 2 2014-02-03 18:50:00.000

This is my query

declare @startdate datetime = '2014-01-27',@enddate datetime = '2014-02-01'
--declare @startdate datetime = '2014-01-20',@enddate datetime = '2014-01-25'

declare @table1 table(employeeid int,reportdate datetime,direction int,punchtime datetime,rownumber int)
insert @table1
select *,row_number() over(partition by reportdate order by punchtime) from empbreaktimes where direction = 1

declare @table2 table(employeeid int,reportdate datetime,direction int,punchtime datetime,rownumber int)
insert @table2
select *,row_number() over(partition by reportdate order by punchtime) from empbreaktimes where direction = 2

declare @outtime table(employeeid int,reportdate datetime,direction int,outtime datetime,rownumber int)
insert @outtime
select employeeid,reportdate,direction,punchtime,rownumber+1 from @table2

IF OBJECT_ID(N'tempdb..#temp', N'U') IS NOT NULL
DROP TABLE #TEMP;

;with cte (employeeid,reportdate,category,datasource,begintime,endtime,toaccount,notoaccount,wrh)
as
(
select t1.employeeid
, convert(varchar,t1.reportdate,106)as 'reportdate'
, 'inoffice' as 'category'
, 'access data' as 'datasource'
, left( right(convert(varchar,t1.punchtime,108),8),5)as begintime
, left( right(convert(varchar,t2.punchtime,108),8),5)as endtime
, sum(datediff(minute,t1.punchtime,t2.punchtime)) as 'toaccount'
, 0
, 0
from @table1 as t1
inner join @table2 as t2
on t1.rownumber=t2.rownumber
and t1.reportdate = t2.reportdate
where t1.reportdate between @startdate and @enddate
--and left(right(convert(varchar,t1.punchtime,108),8),5) < '13:30'
group by t1.employeeid,t1.reportdate,t1.punchtime,t2.punchtime
union all
select inti.employeeid
, convert(varchar,inti.reportdate,106)as 'reportdate'
, 'break' as 'category'
, 'break' as 'datasource'
, left( right(convert(varchar,outi.outtime,108),8),5)as begintime
, left( right(convert(varchar,inti.punchtime,108),8),5) as endtime
, case when datediff(minute,outi.outtime,inti.punchtime) > 15 then 15 else 0 end as 'toaccount'
, case when datediff(minute,outi.outtime,inti.punchtime) > 15 then datediff(minute,outi.outtime,inti.punchtime) -15
else datediff(minute,outi.outtime,inti.punchtime) end as 'nottoaccount'
, 0
from @table1 as inti
inner join @outtime as outi
on inti.rownumber = outi.rownumber
and inti.reportdate = outi.reportdate
where inti.reportdate between @startdate and @enddate
--and left(right(convert(varchar,outi.outtime,108),8),5) < '13:30'
group by inti.reportdate,inti.employeeid,inti.punchtime,outi.outtime
)
select employeeid
, reportdate
--, category
--, datasource
, min(begintime) as 'reportat'
, max(endtime) as 'leftat'
, sum(toaccount)as 'toaccount'
, wrh
into #temp
from cte group by employeeid,reportdate,wrh
order by reportdate desc

declare @tem1 table(employeeid int,reportdate datetime,reportat varchar(20),leftat varchar(20),toaccount int,wrh int,rn int)
insert @tem1
select *,row_number() over(order by reportdate desc) from #temp

declare @tem2 table(employeeid int,reportdate datetime,reportat varchar(20),leftat varchar(20),toaccount int,wrh int,rn int)
insert @tem2
select *,row_number() over(order by reportdate desc)from #temp

update tm
set tm.wrh = tm.toaccount
from @tem1 as tm
inner join @tem2 as tp
on tp.employeeid = tm.employeeid
where tm.rn in (select max(rn) from @tem2 group by datepart(wk,reportdate))

IF OBJECT_ID(N'tempdb..#temp1', N'U') IS NOT NULL
DROP TABLE #TEMP1;

while(select distinct MAX(wrh) from @tem1 where wrh = 0 group by DATEPART(wk,reportdate)) < 1
begin
update @tem1
set wrh = (select toaccount from @tem1
where reportdate = (select min(reportdate) from @tem1 where wrh = 0))+(select max(wrh) from @tem1)
where wrh = (select max(wrh) from @tem1 where wrh = 0)
and reportdate = (select min(reportdate) from @tem1 where wrh = 0)

end

select employeeid
, reportdate
, reportat
, leftat
, cast(wrh/60 as varchar(5))+ ':'+ right('0' + cast(wrh%60 as varchar(2)), 2) as 'wrh'
into #temp1
from @tem1

select tp1.employeeid
, tp.reportdate
, tp.reportat
, tp.leftat
, case when (select dbo.udf_dayofweek(tp.reportdate)) <> 'sat' then '008:00'
when (select dbo.udf_dayofweek(tp.reportdate)) = 'sat' then '005:00'
when (select dbo.udf_dayofweek(tp.reportdate)) = 'sun' then '000:00'
else '00:00' end as 'deh'
, right('0'+cast(tp.toaccount/60 as varchar(5)),2)+ ':'+ right('0' + cast(tp.toaccount%60 as varchar(2)), 2) as 'drh'
, case when (select dbo.udf_dayofweek(tp.reportdate)) = 'Mon' then '008:00'
when (select dbo.udf_dayofweek(tp.reportdate)) = 'Tue' then '016:00'
when (select dbo.udf_dayofweek(tp.reportdate)) = 'Wed' then '024:00'
when (select dbo.udf_dayofweek(tp.reportdate)) = 'Thu' then '032:00'
when (select dbo.udf_dayofweek(tp.reportdate)) = 'Fri' then '040:00'
when (select dbo.udf_dayofweek(tp.reportdate)) = 'Sat' then '045:00'
else '000:00' end as 'weh'
, left('0'+tp1.wrh,10) as 'wrh'
from #temp as tp
inner join #temp1 as tp1
on tp.employeeid = tp1.employeeid
and tp.reportdate = tp1.reportdate

drop table #temp1

output like
--------------

employeeid reportdate reportat leftat deh drh weh wrh
1290 01 Feb 2014 09:40 13:15 005:00 03:35 045:00 047:41
1290 31 Jan 2014 09:24 18:45 008:00 08:59 040:00 044:06
1290 30 Jan 2014 09:15 18:55 008:00 08:35 032:00 035:07
1290 29 Jan 2014 09:30 19:15 008:00 09:20 024:00 026:32
1290 28 Jan 2014 09:36 18:45 008:00 08:28 016:00 017:12
1290 27 Jan 2014 09:30 18:39 008:00 08:44 008:00 08:44
1290 25 Jan 2014 07:58 10:04 005:00 02:06 045:00 047:12
1290 24 Jan 2014 09:17 18:50 008:00 09:06 040:00 045:06
1290 23 Jan 2014 09:19 18:46 008:00 09:06 032:00 036:00
1290 22 Jan 2014 09:17 18:47 008:00 09:05 024:00 026:54
1290 21 Jan 2014 09:16 18:35 008:00 08:46 016:00 017:49
1290 20 Jan 2014 09:18 18:55 008:00 09:03 008:00 09:03

Veera

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-29 : 05:16:41

can you explain us in words how you calculate those column values?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources