June 3, 2009 at 6:12 am
Hi Guys,
This is my first post on anything about SQL 🙂 I've actually only been using SQL Server 2005 for the past 3 days and would really appreciate it if someone could help me out with a query that I am sure you guys are going to find easy.
What I basically am trying to do is to show a table that has the following entities; Date, Interval Start Time, ACD Calls, Abandoned Calls, Calls Offered, and (the one that eludes me) Percentage Distribution with the attributes for my date range.
This is what my query looks like so far
-------------------------------------------------------------------------------------------
USE "Can't display database name"
SELECTdbo.formatdate(Date, 'dd/mm/yy') as "Date",
dbo.formatdate(Interval_Start_Time, 'hh:00') as "Interval_Start_Time",
ACD_Calls,
Aban_Calls,
(sum(ACD_calls) + sum(Aban_Calls)) as "Calls Offered"
FROM dbo."Can't display table name"
WHERE date between '2009/05/01' and '2009/05/31'
group by date, Interval_Start_Time, ACD_Calls, Aban_Calls
--------------------------------------------------------------------------------------------
The column that is missing that is arguably the most important, is the percentage distribution. This column shows the percentage of calls answered in that interval based on the total number of calls received for the day. My problem is, is that the syntax I use to try and get this column sums the calls up for the entire date range i.e. 31 days.
Please, if you reading this, be a champ and sort out my issue before I lose it :crazy:
Thanks,
Byron
June 3, 2009 at 7:36 am
Hi Byron, and welcome to the forum.
First of all see this article on how to get the best help
http://www.sqlservercentral.com/articles/Best+Practices/61537/
I've tried to understand your problem, and I'm probably oversimplifying here - take a look at my code here and if is not what you want (I'm guessing it isn't) - then post some table structures and sample data, and any formula on the calculations you need to generate the percentages
CREATE TABLE Calls (
Date DateTime,
Interval_Start_time DateTime,
ACD_Calls int,
Aban_calls int );
INSERT INTO Calls
SELECT '2009-05-01', '2009-05-01 01:00:00', 1, 1 UNION ALL
SELECT '2009-05-01', '2009-05-01 01:00:00', 2, 3 UNION ALL
SELECT '2009-05-01', '2009-05-01 01:00:00', 4, 5 UNION ALL
SELECT '2009-05-01', '2009-05-01 02:00:00', 10, 2 UNION ALL
SELECT '2009-05-01', '2009-05-01 02:00:00', 12, 12 UNION ALL
SELECT '2009-05-01', '2009-05-01 03:00:00', 12, 3 UNION ALL
SELECT '2009-05-01', '2009-05-01 03:00:00', 13, 7 UNION ALL
SELECT '2009-05-01', '2009-05-01 04:00:00', 14, 12;
SELECT CONVERT(varchar(8),Date,10) AS Date,
Substring(Convert(varchar(20),Interval_Start_time, 120),11,10) AS Interval_Start_Time,
SUM(ACD_Calls) AS Total_ACD,
SUM(Aban_calls) AS Total_Aban,
SUM(ACD_Calls) + SUM(Aban_calls) AS Total_Offered,
CAST(SUM(ACD_Calls) AS FLOAT) * 100.0 / CAST( (SUM(ACD_Calls) + SUM(Aban_calls)) AS FLOAT) AS Percent_Answered
FROM Calls
WHERE Date BETWEEN '2009-05-01' AND '2009-05-31'
GROUP BY Date, Interval_start_time;
DROP TABLE Calls;
June 3, 2009 at 7:52 am
Hi Tom,
Thanks very much for your reply. Unfortunately 🙂 the result is not what I am looking for. Firstly, I'll go check out the link you provided (maybe it will show me how to add code in a nice table like your reply had 🙂 )
June 3, 2009 at 7:58 am
My bad,
Hit the enter button before I finished typing. Below is a table I did in excel with what I am looking for
DateInterval_Start_TimeACD_CallsAban_CallsPercentage Distribution
01-May07:30000.0%
01-May08:00803.6%
01-May08:301105.0%
01-May09:001808.1%
01-May09:30904.1%
01-May10:002109.5%
01-May10:3025011.3%
01-May11:001908.6%
01-May11:301808.1%
01-May12:001808.1%
01-May12:3032014.5%
01-May13:0030013.6%
01-May13:301004.5%
01-May14:00200.9%
02-May07:30000.0%
02-May08:001207.4%
02-May08:30704.3%
02-May09:001308.0%
02-May09:301006.1%
02-May10:001609.8%
02-May10:301207.4%
02-May11:001207.4%
02-May11:301308.0%
02-May12:0019011.7%
02-May12:3024014.7%
02-May13:00603.7%
02-May13:301408.6%
02-May14:00503.1%
03-May07:30000.0%
03-May08:00213.4%
03-May08:3010011.2%
03-May09:00606.7%
03-May09:30606.7%
(Sorry about the shit display, haven't figured out how you did what you did LOL)
I used the excel formula = SUM(C709:D709)/SUMIF($A$2:$A$735,$A709,$C$2:$D$735)
Hope that helps with understanding what I am trying to do. Thanks once again for your quick response
Byron
Editor's Note: Removed some sample data, appeared to be killing the page.
June 3, 2009 at 7:59 am
The code is in a nice box, because its enclosed in
[ code ] ... your code here ... [ /code ] blocks - I have put extra spaces so these print, otherwise they are invisible.
See the IFCode Shortcuts on the left of the reply box.
Edit: And post some sample data using the advice in the best practises article.
June 3, 2009 at 8:09 am
Thanks Tom,
Was just reading the best practises and saw that what I did is the first thing they discourage uses from doing LOL
OK lets try that again here we go
DateInterval_Start_TimeACD_CallsAban_CallsPercentage Distribution
01-May07:30000.0%
01-May08:00803.6%
01-May08:301105.0%
01-May09:001808.1%
01-May09:30904.1%
01-May10:002109.5%
01-May10:3025011.3%
01-May11:001908.6%
01-May11:301808.1%
01-May12:001808.1%
01-May12:3032014.5%
01-May13:0030013.6%
01-May13:301004.5%
01-May14:00200.9%
02-May07:30000.0%
02-May08:001207.4%
02-May08:30704.3%
02-May09:001308.0%
02-May09:301006.1%
02-May10:001609.8%
02-May10:301207.4%
02-May11:001207.4%
02-May11:301308.0%
02-May12:0019011.7%
02-May12:3024014.7%
02-May13:00603.7%
02-May13:301408.6%
02-May14:00503.1%
03-May07:30000.0%
03-May08:00213.4%
03-May08:3010011.2%
03-May09:00606.7%
03-May09:30606.7%
03-May10:00809.0%
03-May10:3010011.2%
03-May11:00606.7%
03-May11:309010.1%
03-May12:00505.6%
03-May12:3010011.2%
03-May13:009010.1%
03-May13:30707.9%
03-May14:00101.1%
04-May07:00400.6%
04-May07:30400.6%
04-May08:001402.2%
04-May08:301802.9%
04-May09:002403.8%
04-May09:304006.4%
04-May10:003014.9%
04-May10:304006.4%
04-May11:003104.9%
04-May11:303305.2%
04-May12:004407.0%
04-May12:303906.2%
04-May13:001402.2%
04-May13:304306.8%
04-May14:004306.8%
04-May14:303104.9%
04-May15:003104.9%
04-May15:302403.8%
04-May16:002604.1%
04-May16:302644.8%
04-May17:009123.3%
04-May17:302274.6%
04-May18:00911.6%
04-May18:301011.7%
04-May19:00621.3%
04-May19:30741.7%
04-May20:00000.0%
04-May20:30701.1%
04-May21:00000.0%
05-May07:00200.3%
June 3, 2009 at 8:14 am
Sorry Tom,
I have to be off now. I will be on later tonight and will post properly as per best practises. Thanks for taking the time and being patient with this newbie 🙂
Cheers,
Byron
June 3, 2009 at 8:32 am
Less haste please ...
1) See my first reply - Is that CREATE TABLE statement correct? - if not can you post a correctef version.
2) Can you format your sample data so I can easily insert the data into the table - using the SELECT ... UNION ALL construct.
I used the excel formula = SUM(C709:D709)/SUMIF($A$2:$A$735,$A709,$C$2:$D$735)
3)I've no idea how to apply this formula can you re-write it in terms of the Columns in the sample table
4)I dont need that much sample data - just 10 rows from each of two or three days worth will do - And you can attach a large file of the data if required. - What happens is my poor IE6 browser dies for about 5 minutes while it digests all that formatting.
5) You can edit your posts after they have been posted.
June 3, 2009 at 8:58 am
Byron, what you need (I think) is the % answered to apply to the cumulative total for the calendar day, and not just the interval? i.e. if I had this:
answered abandoned offered
1 2 3
2 1 3
Then I really want my % handled to be 1/3 for the first interval, and 3/6 for the second interval?
Is that true?
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
June 3, 2009 at 9:30 am
In the absence of a full set of sample date, I used part of your array as follows
create table #t (date datetime,
time char(5),
acd_calls int,
aban_calls int,
expect_percent float)
insert #t values('01-may-2009', '07:30', '0', '0', '0.0')
insert #t values('01-may-2009', '08:00', '8', '0', '3.6')
insert #t values('01-may-2009', '08:30', '11', '0', '5.0')
insert #t values('01-may-2009', '09:00', '18', '0', '8.1')
insert #t values('01-may-2009', '09:30', '9', '0', '4.1')
insert #t values('01-may-2009', '10:00', '21', '0', '9.5')
insert #t values('01-may-2009', '10:30', '25', '0', '11.3')
insert #t values('01-may-2009', '11:00', '19', '0', '8.6')
insert #t values('01-may-2009', '11:30', '18', '0', '8.1')
insert #t values('01-may-2009', '12:00', '18', '0', '8.1')
insert #t values('01-may-2009', '12:30', '32', '0', '14.5')
insert #t values('01-may-2009', '13:00', '30', '0', '13.6')
insert #t values('01-may-2009', '13:30', '10', '0', '4.5')
insert #t values('01-may-2009', '14:00', '2', '0', '0.9')
insert #t values('02-may-2009', '07:30', '0', '0', '0.0')
insert #t values('02-may-2009', '08:00', '12', '0', '7.4')
insert #t values('02-may-2009', '08:30', '7', '0', '4.3')
insert #t values('02-may-2009', '09:00', '13', '0', '8.0')
insert #t values('02-may-2009', '09:30', '10', '0', '6.1')
insert #t values('02-may-2009', '10:00', '16', '0', '9.8')
insert #t values('02-may-2009', '10:30', '12', '0', '7.4')
insert #t values('02-may-2009', '11:00', '12', '0', '7.4')
insert #t values('02-may-2009', '11:30', '13', '0', '8.0')
insert #t values('02-may-2009', '12:00', '19', '0', '11.7')
insert #t values('02-may-2009', '12:30', '24', '0', '14.7')
insert #t values('02-may-2009', '13:00', '6', '0', '3.7')
insert #t values('02-may-2009', '13:30', '14', '0', '8.6')
insert #t values('02-may-2009', '14:00', '5', '0', '3.1')
insert #t values('03-may-2009', '07:30', '0', '0', '0.0')
insert #t values('03-may-2009', '08:00', '2', '1', '3.4')
insert #t values('03-may-2009', '08:30', '10', '0', '11.2')
insert #t values('03-may-2009', '09:00', '6', '0', '6.7')
insert #t values('03-may-2009', '09:30', '6', '0', '6.7')
insert #t values('03-may-2009', '10:00', '8', '0', '9.0')
insert #t values('03-may-2009', '10:30', '10', '0', '11.2')
insert #t values('03-may-2009', '11:00', '6', '0', '6.7')
insert #t values('03-may-2009', '11:30', '9', '0', '10.1')
insert #t values('03-may-2009', '12:00', '5', '0', '5.6')
insert #t values('03-may-2009', '12:30', '10', '0', '11.2')
insert #t values('03-may-2009', '13:00', '9', '0', '10.1')
insert #t values('03-may-2009', '13:30', '7', '0', '7.9')
insert #t values('03-may-2009', '14:00', '1', '0', '1.1')
insert #t values('04-may-2009', '07:00', '4', '0', '0.6')
insert #t values('04-may-2009', '07:30', '4', '0', '0.6')
insert #t values('04-may-2009', '08:00', '14', '0', '2.2')
insert #t values('04-may-2009', '08:30', '18', '0', '2.9')
insert #t values('04-may-2009', '09:00', '24', '0', '3.8')
insert #t values('04-may-2009', '09:30', '40', '0', '6.4')
insert #t values('04-may-2009', '10:00', '30', '1', '4.9')
insert #t values('04-may-2009', '10:30', '40', '0', '6.4')
insert #t values('04-may-2009', '11:00', '31', '0', '4.9')
insert #t values('04-may-2009', '11:30', '33', '0', '5.2')
insert #t values('04-may-2009', '12:00', '44', '0', '7.0')
insert #t values('04-may-2009', '12:30', '39', '0', '6.2')
insert #t values('04-may-2009', '13:00', '14', '0', '2.2')
insert #t values('04-may-2009', '13:30', '43', '0', '6.8')
insert #t values('04-may-2009', '14:00', '43', '0', '6.8')
insert #t values('04-may-2009', '14:30', '31', '0', '4.9')
insert #t values('04-may-2009', '15:00', '31', '0', '4.9')
insert #t values('04-may-2009', '15:30', '24', '0', '3.8')
insert #t values('04-may-2009', '16:00', '26', '0', '4.1')
insert #t values('04-may-2009', '16:30', '26', '4', '4.8')
insert #t values('04-may-2009', '17:00', '9', '12', '3.3')
insert #t values('04-may-2009', '17:30', '22', '7', '4.6')
insert #t values('04-may-2009', '18:00', '9', '1', '1.6')
insert #t values('04-may-2009', '18:30', '10', '1', '1.7')
insert #t values('04-may-2009', '19:00', '6', '2', '1.3')
insert #t values('04-may-2009', '19:30', '7', '4', '1.7')
insert #t values('04-may-2009', '20:00', '0', '0', '0.0')
insert #t values('04-may-2009', '20:30', '7', '0', '1.1')
insert #t values('04-may-2009', '21:00', '0', '0', '0.0')
insert #t values('05-may-2009', '07:00', '2', '0', '0.3')
insert #t values('05-may-2009', '07:30', '8', '0', '1.2')
insert #t values('05-may-2009', '08:00', '14', '0', '2.2')
insert #t values('05-may-2009', '08:30', '23', '0', '3.6')
insert #t values('05-may-2009', '09:00', '35', '1', '5.6')
insert #t values('05-may-2009', '09:30', '32', '0', '5.0')
insert #t values('05-may-2009', '10:00', '34', '1', '5.4')
insert #t values('05-may-2009', '10:30', '40', '0', '6.2')
insert #t values('05-may-2009', '11:00', '48', '1', '7.6')
insert #t values('05-may-2009', '11:30', '39', '0', '6.1')
insert #t values('05-may-2009', '12:00', '26', '0', '4.0')
insert #t values('05-may-2009', '12:30', '25', '0', '3.9')
insert #t values('05-may-2009', '13:00', '36', '0', '5.6')
insert #t values('05-may-2009', '13:30', '39', '0', '6.1')
insert #t values('05-may-2009', '14:00', '36', '0', '5.6')
insert #t values('05-may-2009', '14:30', '31', '0', '4.8')
insert #t values('05-may-2009', '15:00', '38', '2', '6.2')
insert #t values('05-may-2009', '15:30', '35', '1', '5.6')
insert #t values('05-may-2009', '16:00', '26', '1', '4.2')
insert #t values('05-may-2009', '16:30', '20', '20', '6.2')
insert #t values('05-may-2009', '17:00', '10', '7', '2.6')
insert #t values('05-may-2009', '17:30', '11', '7', '2.8')
insert #t values('05-may-2009', '18:00', '12', '0', '1.9')
insert #t values('05-may-2009', '18:30', '7', '2', '1.4')
insert #t values('05-may-2009', '19:00', '6', '1', '1.1')
insert #t values('05-may-2009', '19:30', '8', '1', '1.4')
insert #t values('05-may-2009', '20:00', '1', '0', '0.2')
insert #t values('05-may-2009', '20:30', '1', '0', '0.2')
insert #t values('05-may-2009', '21:00', '0', '0', '0.0')
insert #t values('06-may-2009', '07:00', '3', '0', '0.5')
insert #t values('06-may-2009', '07:30', '13', '0', '2.0')
insert #t values('06-may-2009', '08:00', '16', '2', '2.8')
insert #t values('06-may-2009', '08:30', '12', '3', '2.3')
insert #t values('06-may-2009', '09:00', '33', '6', '6.0')
insert #t values('06-may-2009', '09:30', '31', '0', '4.8')
insert #t values('06-may-2009', '10:00', '22', '8', '4.7')
insert #t values('06-may-2009', '10:30', '45', '3', '7.4')
insert #t values('06-may-2009', '11:00', '40', '1', '6.4')
insert #t values('06-may-2009', '11:30', '33', '12', '7.0')
insert #t values('06-may-2009', '12:00', '28', '7', '5.4')
insert #t values('06-may-2009', '12:30', '27', '2', '4.5')
insert #t values('06-may-2009', '13:00', '42', '1', '6.7')
insert #t values('06-may-2009', '13:30', '27', '0', '4.2')
insert #t values('06-may-2009', '14:00', '42', '1', '6.7')
insert #t values('06-may-2009', '14:30', '36', '1', '5.7')
insert #t values('06-may-2009', '15:00', '39', '1', '6.2')
insert #t values('06-may-2009', '15:30', '35', '0', '5.4')
insert #t values('06-may-2009', '16:00', '22', '1', '3.6')
insert #t values('06-may-2009', '16:30', '25', '3', '4.3')
insert #t values('06-may-2009', '17:00', '20', '1', '3.3')
insert #t values('06-may-2009', '17:30', '12', '3', '2.3')
insert #t values('06-may-2009', '18:00', '11', '0', '1.7')
insert #t values('06-may-2009', '18:30', '7', '6', '2.0')
insert #t values('06-may-2009', '19:00', '4', '6', '1.6')
insert #t values('06-may-2009', '19:30', '5', '1', '0.9')
insert #t values('06-may-2009', '20:00', '8', '2', '1.6')
insert #t values('06-may-2009', '20:30', '5', '5', '1.6')
insert #t values('06-may-2009', '21:00', '2', '2', '0.6')
--
-- calculate percentages as distributions throughout each day
--
select
date,
time,
acd_calls,
aban_calls,
expect_percent,
100.0*convert(float,(aban_calls+acd_calls))/(sum(acd_calls) over(partition by date))
as 'computed percent 1',
100.0*convert(float,(aban_calls+acd_calls))/(sum(acd_calls+aban_calls) over(partition by date))
as 'computed percent 2'
from #t
As you can see, I've computed 2 percentages. Number 1 divides by just the sum of ACD_Calls and matches your Excel result. Number 2 uses (ACD_calls+Aban_Calls) and, obvously, produces a slightly different result.
Depending of how your raw data is actually formatted, you'll probably need to play with this a bit, but the key is to use the partitioning of aggregate functions to get the sum-over-date.
Hope this helps.
Derek
June 3, 2009 at 9:41 am
To show how to extend it, using the same test data, the following computes the distribution in hourly bins.
select
date,
datepart(hh,(convert(datetime,time,108))) as 'hr',
sum(acd_calls) as 'acd_calls per hr',
sum(aban_calls) as 'aban_calls per hr',
sum(sum(acd_calls)) over(partition by date) as 'total acd_calls per day',
100.0*convert(float,sum(aban_calls+acd_calls))/(sum(sum(acd_calls)) over(partition by date))
as 'percent per hr'
from #t
group by date,datepart(hh,(convert(datetime,time,108)))
Derek
June 3, 2009 at 1:07 pm
Derek you are the man 😀
I couldn't quite understand exactly what you did 🙂 but took some stuff you used and added it to my query. The result ..... exactly what I was looking for. So thanks a mil Tom and Derek
Just for your own interest. This is what I finally used
USE "For confidential reasons, can't display"
SELECTdbo.formatdate(Date, 'yyyy/mm/dd') as "Date",
dbo.formatdate(Interval_Start_Time, 'hh:00') as "Interval_Start_Time",
ACD_Calls,
Aban_Calls,
sum(sum(acd_calls)) over(partition by date) + sum(sum(aban_calls)) over(partition by date) as "Total_Calls_Per_Day",
100.0*convert(float,sum(aban_calls+acd_calls))/(sum(sum(acd_calls)) over(partition by date))
as "percent_per_hr"
FROM dbo."For confidential reasons, can't display"
WHERE date between '2009/05/01' and '2009/05/31'
group by date, Interval_Start_Time, ACD_Calls, Aban_Calls
Order by Date, Interval_Start_Time
June 3, 2009 at 6:25 pm
byron.vanwyk (6/3/2009)
Derek you are the man 😀I couldn't quite understand exactly what you did 🙂 but took some stuff you used and added it to my query. The result ..... exactly what I was looking for.
Byron,
It would be very irresponsible on your part to use code that you don't understand in any production system. Look it over, and try to figure it out. If it escapes you, come on back here and ask more questions until you understand it. If you can't explain it to others, you shouldn't be using it.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
June 4, 2009 at 12:27 am
Wayne you 100% correct. Fortunately, I am using just data and not actually implementing anything in the real world. All the databases I have are stored on my desktop and not on any server or affecting any real time systems at my work. This way I can make as many mistakes as I want without the risk of causing any financial or system loss.
As I said in my earlier post, I've only being using sql from Monday and so basically know nothing about what the system can do. However, through trial and error in a risk free environment, I am able to learn through real world problems and not just through theory.
Will definitely spend the time understanding what Derek did, as I am one of those people that needs to understand how something works as I am not content with just knowing that it does
Cheers mate,
Byron
June 5, 2009 at 8:54 am
byron.vanwyk (6/3/2009)
I couldn't quite understand exactly what you did 🙂 but took some stuff you used and added it to my query. The result ..... exactly what I was looking for. So thanks a mil Tom and Derek
Look up the "OVER clause (Transact-SQL)" in the Transact-SQL reference in BOL. This shows the syntax and gives a brief explanation of windowing functions, which is the bit I assume you don't understand.
Derek
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply