|
|
Posted by Mark on 05/14/07 15:02
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,
StatusCategoryID int NOT NULL
) ON [PRIMARY]
CREATE TABLE #RegStatus(
[RegistrationID] [uniqueidentifier] 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('7A6058D0-06CB-4E83-A8C4-B1AFC74B11F0',
200, GETDATE())
INSERT INTO #RegStatus VALUES('7A6058D0-06CB-4E83-A8C4-B1AFC74B11F0',
210, GETDATE())
INSERT INTO #RegStatus VALUES('7A6058D0-06CB-4E83-A8C4-B1AFC74B11F0',
115, GETDATE())
INSERT INTO #RegStatus VALUES('8B94A666-A3DD-4CB1-89A0-9910047AE7A0',
112, GETDATE())
INSERT INTO #RegStatus VALUES('8B94A666-A3DD-4CB1-89A0-9910047AE7A0',
314, GETDATE())
INSERT INTO #RegStatus VALUES('8B94A666-A3DD-4CB1-89A0-9910047AE7A0',
200, GETDATE())
INSERT INTO #RegStatus VALUES('8B94A666-A3DD-4CB1-89A0-9910047AE7A0',
22, GETDATE())
INSERT INTO #RegStatus VALUES('58AF0FC6-C900-4BD0-B3F7-F9D62701F021',
15, GETDATE())
INSERT INTO #RegStatus VALUES('58AF0FC6-C900-4BD0-B3F7-F9D62701F021',
115, GETDATE())
INSERT INTO #RegStatus VALUES('58AF0FC6-C900-4BD0-B3F7-F9D62701F021',
200, GETDATE())
INSERT INTO #RegStatus VALUES('58AF0FC6-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 UNIQUEIDENTIFIER
SET @RegStatusCatID = 1
SET @RegID = null
select LS.*, S.StatusName, S.StatusCategoryID
from #Status S
join(
select RS.RegistrationID, RS.StatusID, RS.StatusTimeStamp
from #RegStatus RS
join
(
SELECT RS.RegistrationID , max(RS.UniqueRowID) UniqueRowID
FROM #RegStatus RS
join #Status S
on RS.StatusID = S.StatusID
and S.StatusCategoryID = @RegStatusCatID
and (
@RegID is null
or (@RegID is not null
and RS.RegistrationID = @RegID)
)
group by RS.RegistrationID
)LS
on RS.UniqueRowID = LS.UniqueRowID
) LS
on S.StatusID = LS.StatusID
--SELECT * FROM #RegStatus
DROP TABLE #RegStatus
DROP TABLE #Status
Navigation:
[Reply to this message]
|