April 25, 2008 at 1:57 pm
I have the following 2 tables:
CREATE TABLE TXN
(
INT
, txnDate DATETIME
)
CREATE TABLE PERIOD
(
INT
, pStart DATETIME
)
I populate them like this:
INSERT INTO TXN VALUES (1, '20080110')
INSERT INTO TXN VALUES (1, '20080111')
INSERT INTO TXN VALUES (2, '20080215')
INSERT INTO TXN VALUES (2, '20080216')
INSERT INTO TXN VALUES (3, '20080316')
INSERT INTO PERIOD VALUES (1, '20080101')
INSERT INTO PERIOD VALUES (2, '20080201')
INSERT INTO PERIOD VALUES (3, '20080301')
Here is the query. It returns the maximum txnDate for each pStart value:
SELECT
MAX(A.txnDate) txnDate
, A.pStart
FROM
(
SELECT
T.txnDate
, P.pStart
FROM TXN T
INNER JOIN PERIOD P ON T. = P.
) A
INNER JOIN
(
SELECT
T.txnDate
, P.pStart
FROM TXN T
INNER JOIN PERIOD P ON T. = P.
) B
ON
A.pStart = B.pStart
AND A.txnDate <= B.txnDate
GROUP BY
A.pStart
Is there a way to re-write this query so that it is better optimized?
Any ideas anyone?
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
April 25, 2008 at 2:29 pm
Try this:
SELECT pStart, MAX(txnDate) AS txnDate
FROM Period p INNER JOIN TXN t ON p.[Key] = t.[Key]
GROUP BY pStart
Dave Novak
April 25, 2008 at 2:35 pm
thank you, I should have seen that....:)
Actually, I tried to simplify a more complex query for the original posting.
I will try to find out what I missed when I trimmed out the details...
Back soon
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
April 25, 2008 at 2:50 pm
Actually, this works like a charm, thank you!
Query returns 500,000 records in a fifth of the time of what it did before.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
April 28, 2008 at 7:06 am
Glad I could help. It is always helpful to have another set of eyes look at things.
Dave
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply