Help with an SQL query

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 ws):
<pre>
wbegin 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?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 ws):
><pre>
> wbegin 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, the
average 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 wbegin = 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(wday, d.thedate) = 'Sunday') AS x
This 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 ws):
><pre>
>wbegin 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 wbbegin,
DATEADD(day, (7 * N.Number) - 1, @.startdate) AS wend,
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 ws start on Sundays.
If you were to issue the command SET DATEFIRT 1, to state that your
ws begin on Mondays, the query would then give you ws Monday to
Sunday.
And, yes, dw is day of w. This is covered in Books Online, for the
datetime 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 ws may
not 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

Help with a Query

Here is some drastically stripped down DDL for a Help Desk system I
wrote. I only left the relevant columns, and didn't script any of the
relationships, etc.
CREATE TABLE [HelpDesk_Issue] ([Id] [int])
GO
INSERT INTO HelpDesk_Issue ([Id]) VALUES (1)
GO
CREATE TABLE [HelpDesk_IssueHistory] (
[Id] [int],
[IssueId] [int],
[UserIdEnteredBy] [int],
[DateEntered] [datetime]
GO
INSERT INTO HelpDesk_IssueHistory ([Id], [IssueId], [UserIdEnteredBy],
[DateEntered]) VALUES (1, 1, 1, '2004-10-27 14:41:58.980')
GO
INSERT INTO HelpDesk_IssueHistory ([Id], [IssueId], [UserIdEnteredBy],
[DateEntered]) VALUES (2, 1, 1, '2004-10-28 16:25:38.103')
GO
INSERT INTO HelpDesk_IssueHistory ([Id], [IssueId], [UserIdEnteredBy],
[DateEntered]) VALUES (3, 1, 3, '2004-11-05 15:25:18.120')
GO
HelpDesk_Issue is a table containing Help Desk issue entries, and
HelpDesk_IssueHistory is a table containing modification history
records for the Help Desk issues.
I want to write a query to retrieve values for LastUpdated, and
LastUpdatedBy.
LastUpdated is pretty easy. I might simply be brainfarting on not
knowing how to do a HAVING properly, but the only way I can retrieve
LastUpdatedBy is:
SELECT
LastUpdated =(Select MAX(H.DateEntered) From HelpDesk_IssueHistory H
Where IssueId = I.[Id]),
LastUpdatedBy =
(
Select
UserIdEnteredBy
From
HelpDesk_IssueHistory
Where
Id =
(
Select
MAX(H.Id)
From
HelpDesk_IssueHistory H
Where
IssueId = I.[Id]
)
)
FROM
HelpDesk_Issue I
My result set should be:
Date Entered UserIdEnteredBy
2004-11-05 15:25:18.120 3
This query works, but is unacceptably slow, and there's got to be a
cleaner way of doing it.
Thank you!Hi
Maybe something like:
SELECT I.id, I.DateEntered, I.UserIdEnteredBy AS LastUpdatedBy
FROM HelpDesk_Issue I
JOIN (Select Id, MAX(DateEntered) AS LatestDateEntered From
HelpDesk_IssueHistory GROUP BY Id ) L ON I.id = L.id and I.DateEntered =
L.LatestDateEntered
Assuming that DateEntered is unique!
John
<george.durzi@.gmail.com> wrote in message
news:1122230790.079548.152910@.g44g2000cwa.googlegroups.com...
> Here is some drastically stripped down DDL for a Help Desk system I
> wrote. I only left the relevant columns, and didn't script any of the
> relationships, etc.
> CREATE TABLE [HelpDesk_Issue] ([Id] [int])
> GO
> INSERT INTO HelpDesk_Issue ([Id]) VALUES (1)
> GO
> CREATE TABLE [HelpDesk_IssueHistory] (
> [Id] [int],
> [IssueId] [int],
> [UserIdEnteredBy] [int],
> [DateEntered] [datetime]
> GO
> INSERT INTO HelpDesk_IssueHistory ([Id], [IssueId], [UserIdEnteredBy],
> [DateEntered]) VALUES (1, 1, 1, '2004-10-27 14:41:58.980')
> GO
> INSERT INTO HelpDesk_IssueHistory ([Id], [IssueId], [UserIdEnteredBy],
> [DateEntered]) VALUES (2, 1, 1, '2004-10-28 16:25:38.103')
> GO
> INSERT INTO HelpDesk_IssueHistory ([Id], [IssueId], [UserIdEnteredBy],
> [DateEntered]) VALUES (3, 1, 3, '2004-11-05 15:25:18.120')
> GO
> HelpDesk_Issue is a table containing Help Desk issue entries, and
> HelpDesk_IssueHistory is a table containing modification history
> records for the Help Desk issues.
> I want to write a query to retrieve values for LastUpdated, and
> LastUpdatedBy.
> LastUpdated is pretty easy. I might simply be brainfarting on not
> knowing how to do a HAVING properly, but the only way I can retrieve
> LastUpdatedBy is:
> SELECT
> LastUpdated =(Select MAX(H.DateEntered) From HelpDesk_IssueHistory H
> Where IssueId = I.[Id]),
> LastUpdatedBy =
> (
> Select
> UserIdEnteredBy
> From
> HelpDesk_IssueHistory
> Where
> Id =
> (
> Select
> MAX(H.Id)
> From
> HelpDesk_IssueHistory H
> Where
> IssueId = I.[Id]
> )
> )
> FROM
> HelpDesk_Issue I
> My result set should be:
> Date Entered UserIdEnteredBy
> 2004-11-05 15:25:18.120 3
> This query works, but is unacceptably slow, and there's got to be a
> cleaner way of doing it.
> Thank you!
>|||george.durzi@.gmail.com wrote:
> Here is some drastically stripped down DDL for a Help Desk system I
> wrote. I only left the relevant columns, and didn't script any of the
> relationships, etc.
> CREATE TABLE [HelpDesk_Issue] ([Id] [int])
> GO
> INSERT INTO HelpDesk_Issue ([Id]) VALUES (1)
> GO
> CREATE TABLE [HelpDesk_IssueHistory] (
> [Id] [int],
> [IssueId] [int],
> [UserIdEnteredBy] [int],
> [DateEntered] [datetime]
> GO
> INSERT INTO HelpDesk_IssueHistory ([Id], [IssueId], [UserIdEnteredBy],
> [DateEntered]) VALUES (1, 1, 1, '2004-10-27 14:41:58.980')
> GO
> INSERT INTO HelpDesk_IssueHistory ([Id], [IssueId], [UserIdEnteredBy],
> [DateEntered]) VALUES (2, 1, 1, '2004-10-28 16:25:38.103')
> GO
> INSERT INTO HelpDesk_IssueHistory ([Id], [IssueId], [UserIdEnteredBy],
> [DateEntered]) VALUES (3, 1, 3, '2004-11-05 15:25:18.120')
> GO
> HelpDesk_Issue is a table containing Help Desk issue entries, and
> HelpDesk_IssueHistory is a table containing modification history
> records for the Help Desk issues.
> I want to write a query to retrieve values for LastUpdated, and
> LastUpdatedBy.
> LastUpdated is pretty easy. I might simply be brainfarting on not
> knowing how to do a HAVING properly, but the only way I can retrieve
> LastUpdatedBy is:
> SELECT
> LastUpdated =(Select MAX(H.DateEntered) From HelpDesk_IssueHistory H
> Where IssueId = I.[Id]),
> LastUpdatedBy =
> (
> Select
> UserIdEnteredBy
> From
> HelpDesk_IssueHistory
> Where
> Id =
> (
> Select
> MAX(H.Id)
> From
> HelpDesk_IssueHistory H
> Where
> IssueId = I.[Id]
> )
> )
> FROM
> HelpDesk_Issue I
> My result set should be:
> Date Entered UserIdEnteredBy
> 2004-11-05 15:25:18.120 3
> This query works, but is unacceptably slow, and there's got to be a
> cleaner way of doing it.
--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
Hmm..., Your query seems to be "saying" get the row w/ the latest date
and then get the user ID associated w/ the highest IssueHistory ID
number, which doesn't make much sense. From your limited DDL the
HelpDesk_IssueHistory ID column seems to be unnecessary (how is it an
attribute of the entity HelpDesk_IssueHistory?); therefore, that's why
your query doesn't make much sense to me.
If you just want to find the users who entered the last history item on
each issue try:
SELECT DateEntered, UserIDEnteredBy
FROM HelpDesk_IssueHistory As H
WHERE DateEntered = (SELECT MAX(DateEntered)
FROM HelpDesk_IssueHistory
WHERE IssueID = H.IssueID)
If you wanted the last entry of a specific issue use the above query as
the SQL statement in a stored procedure w/ a parameter of @.issue_id INT
and change the subquery's select clause to:
WHERE IssueID = @.issue_id
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/ AwUBQuPwQIechKqOuFEgEQKoIgCgyRRhsqCibrj+
zwfoQQYrlPTLkWcAoJmJ
50uIn26qiIk4AFnDVinfq+CN
=OEk/
--END PGP SIGNATURE--|||Thank you both for taking the time to reply on a Sunday.
"MGFoster" wrote:

> george.durzi@.gmail.com wrote:
> --BEGIN PGP SIGNED MESSAGE--
> Hash: SHA1
> Hmm..., Your query seems to be "saying" get the row w/ the latest date
> and then get the user ID associated w/ the highest IssueHistory ID
> number, which doesn't make much sense. From your limited DDL the
> HelpDesk_IssueHistory ID column seems to be unnecessary (how is it an
> attribute of the entity HelpDesk_IssueHistory?); therefore, that's why
> your query doesn't make much sense to me.
> If you just want to find the users who entered the last history item on
> each issue try:
> SELECT DateEntered, UserIDEnteredBy
> FROM HelpDesk_IssueHistory As H
> WHERE DateEntered = (SELECT MAX(DateEntered)
> FROM HelpDesk_IssueHistory
> WHERE IssueID = H.IssueID)
> If you wanted the last entry of a specific issue use the above query as
> the SQL statement in a stored procedure w/ a parameter of @.issue_id INT
> and change the subquery's select clause to:
> WHERE IssueID = @.issue_id
> --
> MGFoster:::mgf00 <at> earthlink <decimal-point> net
> Oakland, CA (USA)
> --BEGIN PGP SIGNATURE--
> Version: PGP for Personal Privacy 5.0
> Charset: noconv
> iQA/ AwUBQuPwQIechKqOuFEgEQKoIgCgyRRhsqCibrj+
zwfoQQYrlPTLkWcAoJmJ
> 50uIn26qiIk4AFnDVinfq+CN
> =OEk/
> --END PGP SIGNATURE--
>|||Hey guys, sorry, still having a little trouble with this.
How would you tackle this if you couldn't guarantee that DateEntered was
unique. That's why I included the Id column in HelpDesk_IssueHistory. It's a
n
identity column, I forgot to note that on my DDL.
The query I wrote fetches the id of the latest history record, then uses
that to fetch the User who entered the records. However, it's unacceptably
slow.
Thank you
"george.durzi@.gmail.com" wrote:

> Here is some drastically stripped down DDL for a Help Desk system I
> wrote. I only left the relevant columns, and didn't script any of the
> relationships, etc.
> CREATE TABLE [HelpDesk_Issue] ([Id] [int])
> GO
> INSERT INTO HelpDesk_Issue ([Id]) VALUES (1)
> GO
> CREATE TABLE [HelpDesk_IssueHistory] (
> [Id] [int],
> [IssueId] [int],
> [UserIdEnteredBy] [int],
> [DateEntered] [datetime]
> GO
> INSERT INTO HelpDesk_IssueHistory ([Id], [IssueId], [UserIdEnteredBy],
> [DateEntered]) VALUES (1, 1, 1, '2004-10-27 14:41:58.980')
> GO
> INSERT INTO HelpDesk_IssueHistory ([Id], [IssueId], [UserIdEnteredBy],
> [DateEntered]) VALUES (2, 1, 1, '2004-10-28 16:25:38.103')
> GO
> INSERT INTO HelpDesk_IssueHistory ([Id], [IssueId], [UserIdEnteredBy],
> [DateEntered]) VALUES (3, 1, 3, '2004-11-05 15:25:18.120')
> GO
> HelpDesk_Issue is a table containing Help Desk issue entries, and
> HelpDesk_IssueHistory is a table containing modification history
> records for the Help Desk issues.
> I want to write a query to retrieve values for LastUpdated, and
> LastUpdatedBy.
> LastUpdated is pretty easy. I might simply be brainfarting on not
> knowing how to do a HAVING properly, but the only way I can retrieve
> LastUpdatedBy is:
> SELECT
> LastUpdated =(Select MAX(H.DateEntered) From HelpDesk_IssueHistory H
> Where IssueId = I.[Id]),
> LastUpdatedBy =
> (
> Select
> UserIdEnteredBy
> From
> HelpDesk_IssueHistory
> Where
> Id =
> (
> Select
> MAX(H.Id)
> From
> HelpDesk_IssueHistory H
> Where
> IssueId = I.[Id]
> )
> )
> FROM
> HelpDesk_Issue I
> My result set should be:
> Date Entered UserIdEnteredBy
> 2004-11-05 15:25:18.120 3
> This query works, but is unacceptably slow, and there's got to be a
> cleaner way of doing it.
> Thank you!
>|||The reason DateEntered isn't unique is that even though I am inserting two
history records right after each other in separate db calls, I'm still
getting consecutive history records with the same datetime value.
I'm using GETDATE() within the insert sp. This isn't happening all the time,
only on about 40 of my 8000 records, but thus causing the queries you
recommended to break.
Perhaps I can handle for the server being too fast, by not using getdate,
and instead handling it on the presentation layer, and adding a time tick to
the next insert, in order to guarantee uniqueness
"George Durzi" wrote:
> Hey guys, sorry, still having a little trouble with this.
> How would you tackle this if you couldn't guarantee that DateEntered was
> unique. That's why I included the Id column in HelpDesk_IssueHistory. It's
an
> identity column, I forgot to note that on my DDL.
> The query I wrote fetches the id of the latest history record, then uses
> that to fetch the User who entered the records. However, it's unacceptably
> slow.
> Thank you
> "george.durzi@.gmail.com" wrote:
>|||Hi
Datatime is accurate one three-hundredth of a second, therefore it is
possible to get duplicates under a heavy load, although your identity
will be unique and you can (probably) use that instead and ignore the
datetime column.
e.g.
SELECT I.IssueId, I.DateEntered AS LastUpdatedBy, I.UserIdEnteredBy AS
LastUpdatedBy
FROM HelpDesk_Issue I
JOIN (Select IssueId, MAX(Id) AS LatestId From
HelpDesk_IssueHistory GROUP BY IssueId ) L ON I.IssueId = L.IssueId and
I.Id =
L.LatestId
OR
SELECT H.IssueId, H.DateEntered, H.UserIDEnteredBy
FROM HelpDesk_IssueHistory As H
WHERE H.Id = (SELECT MAX(Id)
FROM HelpDesk_IssueHistory S
WHERE S.IssueID = H.IssueID)
John|||Thanks again John, works perfectly
"John Bell" wrote:

> Hi
> Datatime is accurate one three-hundredth of a second, therefore it is
> possible to get duplicates under a heavy load, although your identity
> will be unique and you can (probably) use that instead and ignore the
> datetime column.
> e.g.
> SELECT I.IssueId, I.DateEntered AS LastUpdatedBy, I.UserIdEnteredBy AS
> LastUpdatedBy
> FROM HelpDesk_Issue I
> JOIN (Select IssueId, MAX(Id) AS LatestId From
> HelpDesk_IssueHistory GROUP BY IssueId ) L ON I.IssueId = L.IssueId and
> I.Id =
> L.LatestId
> OR
> SELECT H.IssueId, H.DateEntered, H.UserIDEnteredBy
> FROM HelpDesk_IssueHistory As H
> WHERE H.Id = (SELECT MAX(Id)
> FROM HelpDesk_IssueHistory S
> WHERE S.IssueID = H.IssueID)
> John
>