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
>
Kind regards
Umachandar Jayachandran - MS wrote: