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

General SQL Server Forums

New to SQL Server Programming

How to stop week hours to continue to next week

Author

Topic

VeeranjaneyuluAnnapureddy
Posting Yak Master

169 Posts

Posted - 2014-01-29 : 01:07:42

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-01-29 : 03:59:28

please do not cross post
Duplicates of http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=190988

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-29 : 03:59:43

can you post what will be your expected output?

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

VeeranjaneyuluAnnapureddy
Posting Yak Master

169 Posts

Posted - 2014-01-29 : 04:42:35

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 : 06:46:53

duplicate post
please dont cross post

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