Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Query to return the MIN(Date) record in a group

Status
Not open for further replies.

eo

MIS
Apr 3, 2003
809
SQL Server 2008

Hi

I created a query that returns the earliest record in a group, and links it as a new field back to the orig dataset, but it behaves very badly. Is there a more optimal version of such a query?

Code:
select a.UnifiedAcctApplNumber
,a.DIM_CO_Account_SSK
,a.StatusTo
,a.TransactionDate_DATETIME
,b.TransactionDate_DATETIME as InitAwaitingCOTDate_DATETIME

from @StatusAccount a

join 

	(select DIM_CO_Account_SSK
	 ,MIN(TransactionDate_DATETIME) as TransactionDate_DATETIME
	 from @StatusAccount
	 group by DIM_CO_Account_SSK) b

on a.DIM_CO_Account_SSK = b.DIM_CO_Account_SSK

where a.StatusTo = 'AwaitingCOT'

order by UnifiedAcctApplNumber, TransactionDate_DATETIME

EO
Hertfordshire, England
 
[tt]from @StatusAccount[/tt]

Can you show us the code where you create this table variable?

Also... Is the combination of DIM_CO_Account_SSK and TransactionDate_DATETIME unique?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
@George - the DIM_CO_Account_SSK and TransactionDate_DATETIME is most likely unique and logic for creating @StatusAccount is

Code:
DECLARE @StatusAccount TABLE (
 StatusTo_DWSK uniqueidentifier null
,StatusFrom_DWSK uniqueidentifier null
,DIM_CO_Account_DWSK uniqueidentifier null
,DIM_CO_Account_SSK varchar(50) null
,DIM_SE_Account_DWSK uniqueidentifier null
,DIM_SE_Account_SSK varchar(50) null
,UnifiedAcctApplNumber varchar(50) null
,AccountNumber varchar(50) null
,Transaction_DATE int null
,TransactionDate_DATETIME datetime null
,StatusFrom varchar(80) null
,StatusTo varchar(80) null
)

INSERT INTO @StatusAccount (
 StatusTo_DWSK
,StatusFrom_DWSK
,DIM_CO_Account_DWSK
,DIM_CO_Account_SSK
,DIM_SE_Account_DWSK
,DIM_SE_Account_SSK
,UnifiedAcctApplNumber
,AccountNumber
,Transaction_DATE
,TransactionDate_DATETIME
,StatusFrom
,StatusTo
)

select 
 STRX.StatusTo_DWSK
,STRX.StatusTo_DWSK
,STRX.DIM_CO_Account_DWSK
,DCA.DIM_CO_Account_SSK
,STRX.DIM_SE_Account_DWSK
,DSA.DIM_SE_Account_SSK
,DCA.UnifiedAcctApplNumber
,DSA.AccountNumber
,STRX.Transaction_DATE
,STRX.TransactionDate_DATETIME
,DCSfm.Status as StatusFrom
,DCSto.Status as StatusTo

from dbo.FACT_CO_StatusTrx STRX

join dbo.DIM_CO_Account DCA
on STRX.DIM_CO_Account_DWSK = DCA.DIM_CO_Account_DWSK

left join dbo.DIM_SE_Account DSA
on STRX.DIM_SE_Account_DWSK = DSA.DIM_SE_Account_DWSK

join dbo.DIM_CO_Status DCSfm
on STRX.StatusFrom_DWSK = DCSfm.DIM_CO_Status_DWSK

join dbo.DIM_CO_Status DCSto
on STRX.StatusTo_DWSK = DCSto.DIM_CO_Status_DWSK

...but I do not think the problem lies there as select * from @StatusAccount is lightning quick

@Markros, I will look at those blogs now

@Both, please note a slight change in my badly behaving query

Code:
select a.UnifiedAcctApplNumber
,a.DIM_CO_Account_SSK
,a.StatusTo
,a.TransactionDate_DATETIME
,b.InitAwaitingCOTDate_DATETIME

from @StatusAccount a

left join 

	(select DIM_CO_Account_SSK
	 ,MIN(TransactionDate_DATETIME) as InitAwaitingCOTDate_DATETIME
	 from @StatusAccount
	 where StatusTo = 'AwaitingCOT'
	 group by DIM_CO_Account_SSK) b

on a.DIM_CO_Account_SSK = b.DIM_CO_Account_SSK

order by UnifiedAcctApplNumber, TransactionDate_DATETIME


EO
Hertfordshire, England
 
To fix your query you need
Code:
select a.UnifiedAcctApplNumber
,a.DIM_CO_Account_SSK
,a.StatusTo
,a.TransactionDate_DATETIME
,b.InitAwaitingCOTDate_DATETIME

from @StatusAccount a

left join 

    (select DIM_CO_Account_SSK
     ,MIN(TransactionDate_DATETIME) as InitAwaitingCOTDate_DATETIME
     from @StatusAccount
     where StatusTo = 'AwaitingCOT'
     group by DIM_CO_Account_SSK) b

on a.DIM_CO_Account_SSK = b.DIM_CO_Account_SSK
and a.TransactionDate_Datetime = b.InitAwaitingCOTDate_Datetime
order by UnifiedAcctApplNumber, TransactionDate_DATETIME

PluralSight Learning Library
 
This may or may not speed up your query....

I suggest you try adding an index to the table variable. Sometimes this can cause the code to run slower, and sometimes it can cause the code to run noticeably faster. But.... trying this will only take a couple minutes of your time, so you have nothing to lose.

Code:
DECLARE @StatusAccount TABLE (
 StatusTo_DWSK uniqueidentifier null
,StatusFrom_DWSK uniqueidentifier null
,DIM_CO_Account_DWSK uniqueidentifier null
,DIM_CO_Account_SSK varchar(50) [!]not[/!] null
,DIM_SE_Account_DWSK uniqueidentifier null
,DIM_SE_Account_SSK varchar(50) null
,UnifiedAcctApplNumber varchar(50) null
,AccountNumber varchar(50) null
,Transaction_DATE int null
,TransactionDate_DATETIME datetime [!]Not[/!] null
,StatusFrom varchar(80) null
,StatusTo varchar(80) null
[!]Primary Key (DIM_CO_Account_SSK,TransactionDate_DATETIME)[/!]
)

I'm curious... so can you please let me know if this makes a noticeably improvement in execution time?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top