|
|
Posted by Alex Kuznetsov on 05/14/07 15:35
On May 14, 10:02 am, Mark <mark.ru...@gmail.com> wrote:
> 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
Read in BOL about
ROW_NUMBER() OVER(PARTITION BY RegistrationID ORDER BY StatusTimeStamp
DESC) = 1
Navigation:
[Reply to this message]
|