generate a number of days - 2 each 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)

generate a number of days - 2 each week

Author

Topic

2revup
Posting Yak Master

112 Posts

Posted - 2013-03-17 : 23:26:40

I am trying to do some calulations and I need to factor in that there is only 5 days to a working week not 7. here is my query:

DECLARE @startdate datetime2 = '2013-01-01 00:00:00.0000000';
DECLARE @enddate datetime2 = GETDATE();

Select
agent_login,
Cast(sum(tot_talk_time) as decimal(38,0)) as Time, cast(sum(tot_talk_time)as decimal(10,2))/3600 as Hours,
DATEDIFF(day, @startdate, @enddate),
(cast(sum(tot_talk_time)as decimal(10,2))/3600) / DATEDIFF(day,@startdate, @enddate)
from PhoneAndChat
group by agent_login

What I am doing is creating a sum in hours and dividing this by a defined number of days. The problem is the day calculation is done on a 7 day basis I need this in a 5 day increment is this possible?

Tell me what further information you might need from me?

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-03-18 : 01:13:39

--This is for excluding 2 days per week
select datediff(dd, @d1, @d2) - (datediff(wk, @d1, @d2) * 2)

--
Chandu