Performance Tuning

I have a query that i'm trying to performance tune a little better.
i'm stuck on one thing. i have a table that i join on mulitple times
that selects the max date for a particular status per id.
the table looks as follows:
CREATE TABLE [dbo].[TABLE_STATUS] (
[TableStatusID] [int] NOT NULL ,
[TableID] [int] NOT NULL ,
[StatusTypeID] [int] NOT NULL ,
[StatusDate] [datetime] NOT NULL ,
[CreateDate] [datetime] NOT NULL ,
[StageTypeID] [int] NULL
) ON [PRIMARY]
GO
The query i'm using looks something like this:
SELECT field1, field2, field3
(SELECT TOP 1 StatusDate
FROM dbo.TABLE_STATUS LS
WHERE LS.TableID= SR.TableIDAND StatusTypeID = 'xyx'
ORDER BY StatusDate DESC) AS someDate,
(SELECT TOP 1 StatusDate
FROM dbo.TABLE_STATUS LS
WHERE LS.TableID= SR.TableIDAND StatusTypeID = 'xxy'
ORDER BY StatusDate DESC) AS someDate,
(SELECT TOP 1 StatusDate
FROM dbo.TABLE_STATUS LS
WHERE LS.TableID= SR.TableIDAND StatusTypeID = 'yxx'
ORDER BY StatusDate DESC) AS someDate
FROM dbo.someTable SR
WHERE etc.
I was wondering if there is a better to way to select the max date for
each loan by date desc. some queries use up too 25 different
max(dates) per select statement. There just has to be a better way
performance wise.
Thanks ahead of time.On 17 May 2005 11:01:01 -0700, GlennThomas5 wrote:
>I have a query that i'm trying to performance tune a little better.
>i'm stuck on one thing. i have a table that i join on mulitple times
>that selects the max date for a particular status per id.
>the table looks as follows:
>CREATE TABLE [dbo].[TABLE_STATUS] (
> [TableStatusID] [int] NOT NULL ,
> [TableID] [int] NOT NULL ,
> [StatusTypeID] [int] NOT NULL ,
> [StatusDate] [datetime] NOT NULL ,
> [CreateDate] [datetime] NOT NULL ,
> [StageTypeID] [int] NULL
>) ON [PRIMARY]
>GO
>The query i'm using looks something like this:
>SELECT field1, field2, field3
> (SELECT TOP 1 StatusDate
> FROM dbo.TABLE_STATUS LS
> WHERE LS.TableID= SR.TableIDAND StatusTypeID = 'xyx'
> ORDER BY StatusDate DESC) AS someDate,
> (SELECT TOP 1 StatusDate
> FROM dbo.TABLE_STATUS LS
> WHERE LS.TableID= SR.TableIDAND StatusTypeID = 'xxy'
> ORDER BY StatusDate DESC) AS someDate,
> (SELECT TOP 1 StatusDate
> FROM dbo.TABLE_STATUS LS
> WHERE LS.TableID= SR.TableIDAND StatusTypeID = 'yxx'
> ORDER BY StatusDate DESC) AS someDate
> FROM dbo.someTable SR
> WHERE etc.
>I was wondering if there is a better to way to select the max date for
>each loan by date desc. some queries use up too 25 different
>max(dates) per select statement. There just has to be a better way
>performance wise.
Hi Glenn,
Try if this works for you:
SELECT field1, field2, field3,
MAX(CASE WHEN LS.StatusTypeID = 'xyx' THEN LS.StatusDate END),
MAX(CASE WHEN LS.StatusTypeID = 'xxy' THEN LS.StatusDate END),
MAX(CASE WHEN LS.StatusTypeID = 'yxx' THEN LS.StatusDate END)
FROM dbo.someTable SR
JOIN dbo.TABLE_STATUS LS
ON LS.TableID= SR.TableID
WHERE ...
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thanks i'm going to check it out right now. =)|||well i check it out and that seems to be pulling the 1 max(date) across
all the typeid's. i need the max(date) for each typeid.|||tweaking a few more things. i think i might have it.|||this worked perfect. thanks again.sql