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)
 generate a number of days - 2 each week
 New Topic  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
2223 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
2223 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  
 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.05 seconds. Powered By: Snitz Forums 2000