Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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)
 generate a number of days - 2 each week
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

2revup
Posting Yak Master

112 Posts

Posted - 03/17/2013 :  23:26:40  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
2241 Posts

Posted - 03/18/2013 :  01:13:39  Show Profile  Reply with Quote
--This is for excluding 2 days per week
select datediff(dd, @d1, @d2) - (datediff(wk, @d1, @d2) * 2)

--
Chandu
Go to Top of Page

2revup
Posting Yak Master

112 Posts

Posted - 03/18/2013 :  01:55:43  Show Profile  Reply with Quote
Perfect thanks for the quick response!
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2241 Posts

Posted - 03/18/2013 :  02:25:52  Show Profile  Reply with Quote
quote:
Originally posted by 2revup

Perfect thanks for the quick response!


Welcome...


--
Chandu
Go to Top of Page
  Previous Topic Topic Next 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.06 seconds. Powered By: Snitz Forums 2000