help desk software we're using and my CIO is asking for a report that I
can't sem to get.
Here's some sample data:
<pre>
refnumber assignee user problem open date
1 bob user1 text1 12/29/2005
2 sally user2 text2 12/29/2005
3 bob user3 text3 12/29/2005
4 sally user1 text4 12/29/2005
5 bob user2 text5 1/5/2006
6 bob user3 etc 1/5/2006
7 sally user5 1/5/2006
8 bob user4 1/15/2006
9 sally user5 1/15/2006
0 bob user4 1/15/2006
</pre>
And here's the report I need (average number of tickets opened per
analyst over the last 4 w
s):<pre>
w
begin wend avg tickets12/25/2005 12/31/2005 2
1/1/2006 1/7/2006 1.5
1/8/2006 1/14/2006 0
1/15/2006 1/21/2006 1.5
</pre>
Normally I just use MS Access to create the SQL for me and then I can
tweak it as I need to but this one is a little too complex for that.
The date calculations are just throwing me completely. Anyone done
something similar?wkwork (wkwork@.movieland.com) writes:
><pre>
> refnumber assignee user problem open date
> 1 bob user1 text1 12/29/2005
> 2 sally user2 text2 12/29/2005
> 3 bob user3 text3 12/29/2005
> 4 sally user1 text4 12/29/2005
> 5 bob user2 text5 1/5/2006
> 6 bob user3 etc 1/5/2006
> 7 sally user5 1/5/2006
> 8 bob user4 1/15/2006
> 9 sally user5 1/15/2006
> 0 bob user4 1/15/2006
></pre>
> And here's the report I need (average number of tickets opened per
> analyst over the last 4 w
s):><pre>
> w
begin wend avg tickets> 12/25/2005 12/31/2005 2
> 1/1/2006 1/7/2006 1.5
> 1/8/2006 1/14/2006 0
> 1/15/2006 1/21/2006 1.5
></pre>
I will first have to assume that you have a table with analysts
to which assignee refers. That is, I assume that if bob does not
open any ticket a certain w
, and Sally submits three, theaverage should be 1.5.
Next, I am introducing a table dates. This table simply has all
dates from 1990-01-01 to 2150-01-01 or whatever. You would have to
fill this table yourself.
SELECT w
begin = d.thedate, wend = dateadd(DAY, 6, d.thedate),avg(cnt)
FROM (SELECT d.thedate, a.assignee, cnt = COUNT(t.Sunday) * 1.0
FROM thedates d
CROSS JOIN analysts a
LEFT JOIN (SELECT dateadd(day, -datepart(dw, opendate) + 1,
opendate) AS Sunday,
assignee
FROM tickets) AS t ON d.thedate = t.Sunday
AND a.analyst = t.assignee
WHERE datename(w
day, d.thedate) = 'Sunday') AS xThis query is not tested.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Wow - thanks Erland. There's a lot in there that I've never seen before
so I'll have to take some time to digest it and try to fit it to my
tables.
Could I ask you to explain the innermost select:
SELECT dateadd(day, -datepart(dw, opendate) + 1,
opendate) AS Sunday,
assignee
Looks like datepart pulls the (day? day of the w
?), adds 1 to it,then dateadd subtracts that number of days from "opendate". Does that
give us the "Sunday" before the current w
?FROM tickets) AS t ON d.thedate = t.Sunday
AND a.analyst = t.assignee
Looks like it's pulling from "tickets" (my original table), calling the
whole array "t" and I'm not sure what the ON means.
Thanks and forgive my inexperience. :-)|||I just realized something else about your reply too:
The "user" and the "assignee" actually both refer to the same table
(the contact table: ctct). If I average over all names in that table,
it would be wrong. I just need to average over contacts of the
"analyst" or "administrator" type. In fact it might be easier to just
average those who have opened tickets and leave off all who have not
(in which case we can disregard the contact table and just count the
unique reference numbers in the ticket table).|||On 18 Jan 2006 14:30:50 -0800, wkwork wrote:
>I'm just starting to get involved with SQL queries as a result of some
>help desk software we're using and my CIO is asking for a report that I
>can't sem to get.
>Here's some sample data:
><pre>
>refnumber assignee user problem open date
> 1 bob user1 text1 12/29/2005
> 2 sally user2 text2 12/29/2005
> 3 bob user3 text3 12/29/2005
> 4 sally user1 text4 12/29/2005
> 5 bob user2 text5 1/5/2006
> 6 bob user3 etc 1/5/2006
> 7 sally user5 1/5/2006
> 8 bob user4 1/15/2006
> 9 sally user5 1/15/2006
> 0 bob user4 1/15/2006
></pre>
>And here's the report I need (average number of tickets opened per
>analyst over the last 4 w
s):><pre>
>w
begin wend avg tickets>12/25/2005 12/31/2005 2
>1/1/2006 1/7/2006 1.5
>1/8/2006 1/14/2006 0
>1/15/2006 1/21/2006 1.5
></pre>
>Normally I just use MS Access to create the SQL for me and then I can
>tweak it as I need to but this one is a little too complex for that.
>The date calculations are just throwing me completely. Anyone done
>something similar?
Hi wkwork,
Here's a different solution. Note that it requires a numbers table. See
http://www.aspfaq.com/show.asp?id=2516 for how to create one.
DECLARE @.startdate datetime, @.enddate datetime
SET @.startdate = '20051225'
SET @.enddate = '20060121'
SELECT DATEADD(day, 7 * (N.Number - 1), @.startdate) AS w
bbegin,DATEADD(day, (7 * N.Number) - 1, @.startdate) AS w
end,1.0 * COUNT(t.OpenDate) / (SELECT COUNT(DISTINCT assignee)
FROM TheTable)
FROM Numbers AS N
LEFT JOIN TheTable AS t
ON t.OpenDate >= DATEADD(day, 7 * (N.Number - 1), @.startdate)
AND t.OpenDate <= DATEADD(day, (7 * N.Number) - 1, @.startdate)
WHERE N.Number >= 1
AND N.Number <= DATEDIFF (day, @.startdate, @.enddate) / 7 + 1
GROUP BY N.Number
Hugo Kornelis, SQL Server MVP|||wkwork (wkwork@.movieland.com) writes:
> Wow - thanks Erland. There's a lot in there that I've never seen before
> so I'll have to take some time to digest it and try to fit it to my
> tables.
> Could I ask you to explain the innermost select:
> SELECT dateadd(day, -datepart(dw, opendate) + 1,
> opendate) AS Sunday,
> assignee
> Looks like datepart pulls the (day? day of the w
?), adds 1 to it,> then dateadd subtracts that number of days from "opendate". Does that
> give us the "Sunday" before the current w
?The intention is that the expression moves back opendate to the first
day of the w
. In the example, I have assumed that this is Sunday,as I noted that your formatted your dates according to the rules unique
to the US, and I am under the impression that in the US the convention
is that w
s start on Sundays.If you were to issue the command SET DATEFIRT 1, to state that your
w
s begin on Mondays, the query would then give you ws Monday toSunday.
And, yes, dw is day of w
. This is covered in Books Online, for thedatetime functions.
> FROM tickets) AS t ON d.thedate = t.Sunday
> AND a.analyst = t.assignee
> Looks like it's pulling from "tickets" (my original table), calling the
> whole array "t" and I'm not sure what the ON means.
The "t" is not an array. This is a derived table. A derived table is
conceptually a temp table within the query, but it is never materialised,
and SQL Server may recast the actual computation order as long as the
result is the same. Here the derived table is used to give us a table
where all opendates are on the first day of the w
.ON is part of the join operator:
SELECT ..
FROM tblA a
JOIN tblB b ON a.col = b.col
To some extent this is the same as a WHERE clause, and the above query
can also be written as
SELECT ...
FROM tblA a, tblB b
WHERE a.col = b.col
However, the query I posted had an outer join, for outer join it matters
whether a condition is in the ON clause or in the WHERE clause.
SELECT ..
FROM tblA a
LEFT JOIN tblB b ON a.col = b.col
AND b.col2 = 1
Here it is the intention is to get all rows in A, and attach the data from
tblB where there is a match with tblA and col2 is 1, else the rows from
tblB should have NULL in all columns.
I used LEFT JOIN in the query, to cover the case that some w
s maynot have any tickets opened at all.
> The "user" and the "assignee" actually both refer to the same table
> (the contact table: ctct). If I average over all names in that table,
> it would be wrong. I just need to average over contacts of the
> "analyst" or "administrator" type. In fact it might be easier to just
> average those who have opened tickets and leave off all who have not
> (in which case we can disregard the contact table and just count the
> unique reference numbers in the ticket table).
That's what you get for inclucing partial information. It sounds like
you would need to add a filter:
AND usertype IN ('analyst', 'administrator')
Finally, a standard recommendation for this type of questions is that
you include:
o CREATE TABLE statements for the tables involved.
o INSERT statements with sample data.
o The desired result given the sample.
If you do this, it is very easy to copy and past into a query window, and
you get and a reply with a query that has been tested.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
