Hey folks, I'm looking at making the following query more efficient
potentially using the ranking functions and I'd like some advice from
the gurus. The purpose of the following is to have a status for a
person, and also have a historical background as to what they've done,
status wise. This was the best way I could come up with to do this a
few years back, but I'm wondering if there's a better way with Sql
2005.
Here's a toned down structure and my query. Any help/critique would
be greatly appreciated.
CREATE TABLE #Status(
StatusID int NOT NULL,
StatusName VARCHAR(50) NOT NULL,
StatusCategoryI D int NOT NULL
) ON [PRIMARY]
CREATE TABLE #RegStatus(
[RegistrationID] [uniqueidentifie r] NOT NULL,
[StatusID] [int] NOT NULL,
[StatusTimeStamp] [datetime] NOT NULL,
[UniqueRowID] [int] IDENTITY(1,1) NOT NULL
) ON [PRIMARY]
SET NOCOUNT on
INSERT INTO #Status VALUES(200, 'StatusA', 1)
INSERT INTO #Status VALUES(210, 'StatusB', 1)
INSERT INTO #Status VALUES(115, 'StatusC', 1)
INSERT INTO #Status VALUES(112, 'StatusD', 1)
INSERT INTO #Status VALUES(314, 'StatusE', 1)
INSERT INTO #Status VALUES(15, 'StatusF', 1)
INSERT INTO #Status VALUES(22, 'StatusG', 1)
INSERT INTO #Status VALUES(300, 'StatusX', 2)
INSERT INTO #Status VALUES(310, 'StatusY', 2)
INSERT INTO #Status VALUES(320, 'StatusZ', 2)
INSERT INTO #RegStatus VALUES('7A6058D 0-06CB-4E83-A8C4-B1AFC74B11F0',
200, GETDATE())
INSERT INTO #RegStatus VALUES('7A6058D 0-06CB-4E83-A8C4-B1AFC74B11F0',
210, GETDATE())
INSERT INTO #RegStatus VALUES('7A6058D 0-06CB-4E83-A8C4-B1AFC74B11F0',
115, GETDATE())
INSERT INTO #RegStatus VALUES('8B94A66 6-A3DD-4CB1-89A0-9910047AE7A0',
112, GETDATE())
INSERT INTO #RegStatus VALUES('8B94A66 6-A3DD-4CB1-89A0-9910047AE7A0',
314, GETDATE())
INSERT INTO #RegStatus VALUES('8B94A66 6-A3DD-4CB1-89A0-9910047AE7A0',
200, GETDATE())
INSERT INTO #RegStatus VALUES('8B94A66 6-A3DD-4CB1-89A0-9910047AE7A0',
22, GETDATE())
INSERT INTO #RegStatus VALUES('58AF0FC 6-C900-4BD0-B3F7-F9D62701F021',
15, GETDATE())
INSERT INTO #RegStatus VALUES('58AF0FC 6-C900-4BD0-B3F7-F9D62701F021',
115, GETDATE())
INSERT INTO #RegStatus VALUES('58AF0FC 6-C900-4BD0-B3F7-F9D62701F021',
200, GETDATE())
INSERT INTO #RegStatus VALUES('58AF0FC 6-C900-4BD0-B3F7-F9D62701F021',
115, GETDATE())
SET NOCOUNT Off
/*
This is a query from within a function that I use to not only get the
latest
status for one registrant, but I can use it to get the latest status
for everyone as well.
*/
DECLARE @RegStatusCatID int,
@RegID UNIQUEIDENTIFIE R
SET @RegStatusCatID = 1
SET @RegID = null
select LS.*, S.StatusName, S.StatusCategor yID
from #Status S
join(
select RS.Registration ID, RS.StatusID, RS.StatusTimeSt amp
from #RegStatus RS
join
(
SELECT RS.Registration ID , max(RS.UniqueRo wID) UniqueRowID
FROM #RegStatus RS
join #Status S
on RS.StatusID = S.StatusID
and S.StatusCategor yID = @RegStatusCatID
and (
@RegID is null
or (@RegID is not null
and RS.Registration ID = @RegID)
)
group by RS.Registration ID
)LS
on RS.UniqueRowID = LS.UniqueRowID
) LS
on S.StatusID = LS.StatusID
--SELECT * FROM #RegStatus
DROP TABLE #RegStatus
DROP TABLE #Status