SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 How to stop week hours to continue to next week
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

VeeranjaneyuluAnnapureddy
Posting Yak Master

India
169 Posts

Posted - 01/29/2014 :  01:11:01  Show Profile  Reply with Quote
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

India
169 Posts

Posted - 01/29/2014 :  04:51:19  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 01/29/2014 :  05:16:41  Show Profile  Reply with Quote
can you explain us in words how you calculate those column values?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.25 seconds. Powered By: Snitz Forums 2000