October 17, 2012 at 2:37 am
Eric M Russell (10/15/2012)
You can simulate the functional equivalent in SQL Server 2000 less gracefully like so:
select C_RECID, H_RECID from
(
select C.RECID C_RECID, H.RECID H_RECID,
(select count(*) from #HISTORY HX
where HX.ACCOUNTNO = H.ACCOUNTNO and HX.RECID >= H.RECID) history_rank
from #CONTACT C
join #HISTORY H on H.ACCOUNTNO = C.ACCOUNTNO
) x
where history_rank = 1;
C_RECID H_RECID
------- -------
CR1 HR3
CR2 HR5
If Don has time, it would be interesting to know how this triangular join method compares to the aggregate methods. With small partitions it could be quite performant; with large partitions it won't. TJ's scale poorly.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
October 17, 2012 at 7:40 am
ChrisM@home (10/17/2012)
Eric M Russell (10/15/2012)
You can simulate the functional equivalent in SQL Server 2000 less gracefully like so:
select C_RECID, H_RECID from
(
select C.RECID C_RECID, H.RECID H_RECID,
(select count(*) from #HISTORY HX
where HX.ACCOUNTNO = H.ACCOUNTNO and HX.RECID >= H.RECID) history_rank
from #CONTACT C
join #HISTORY H on H.ACCOUNTNO = C.ACCOUNTNO
) x
where history_rank = 1;
C_RECID H_RECID
------- -------
CR1 HR3
CR2 HR5
If Don has time, it would be interesting to know how this triangular join method compares to the aggregate methods. With small partitions it could be quite performant; with large partitions it won't. TJ's scale poorly.
I agree the 2000 method would be potentially problematic in terms of optimization when compared to the the 2005+ windowing function method, although both of them could take considerable resources when dealing with million+ row tables. It's essential that HISTORY table be indexed in a way that supports it, perhaps even a covered indexed just to support this particular report, if it's called multiple times daily.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
October 17, 2012 at 8:54 am
If the records in history have the same date without a timestamp there needs to be a unique way to identify the latest record. If RecID is sequential then you don't even need the date.
From your dummy data and what you're looking for (H1, H4) it looks as if the RecID is sequential but in decending order which I'm assuming it's not.
Here's a modified script of the one above using the RecID.
CREATE TABLE #CONTACT
(
ACCOUNTNO VARCHAR(5),
COMPANY VARCHAR(50),
CONTACT VARCHAR(50),
RECID VARCHAR(5)
)
CREATE TABLE #HISTORY
(
ACCOUNTNO VARCHAR(5),
LASTUSER VARCHAR(8),
LASTDATE DATE,
RECID VARCHAR(5)
)
INSERT INTO #CONTACT (ACCOUNTNO, COMPANY, CONTACT, RECID)
SELECT 'C1', 'ACME', 'WILEY COYOTE', 'CR1' UNION ALL
SELECT 'C2', 'Beta Ltd', 'Joe Soap', 'CR2'
INSERT INTO #HISTORY (ACCOUNTNO, LASTUSER, LASTDATE, RECID)
SELECT 'C1', 'JOE', '20120101', 'HR1' UNION ALL
SELECT 'C1', 'BOB', '20120101', 'HR2' UNION ALL
SELECT 'C1', 'JOE', '20120201', 'HR3' UNION ALL
SELECT 'C2', 'JOE', '20120202', 'HR4' UNION ALL
SELECT 'C2', 'BOB', '20120202', 'HR5'
--Assuming that the RecID is sequential and ascending
select C.*, H.*
from #CONTACT C
left join (
select Hi.*
from #HISTORY Hi
inner join (
select max(recid) Min_RecID, ACCOUNTNO
from #HISTORY
group by ACCOUNTNO
) S
on Hi.ACCOUNTNO = S.ACCOUNTNO
and Hi.RECID = S.Min_RecID
) H
on C.ACCOUNTNO = H.ACCOUNTNO
--This works to get H1 and H4 but it implies that the recid is in descending order...
select C.*, H.*
from #CONTACT C
left join (
select Hi.*
from #HISTORY Hi
inner join (
select min(recid) Min_RecID, ACCOUNTNO
from #HISTORY
group by ACCOUNTNO
) S
on Hi.ACCOUNTNO = S.ACCOUNTNO
and Hi.RECID = S.Min_RecID
) H
on C.ACCOUNTNO = H.ACCOUNTNO
drop table #Contact
drop table #History
---------------------------------------------------------------
Mike Hahn - MCSomething someday:-)
Right way to ask for help!!
http://www.sqlservercentral.com/articles/Best+Practices/61537/
I post so I can see my avatar :hehe:
I want a personal webpage 😎
I want to win the lotto 😀
I want a gf like Tiffa :w00t: Oh wait I'm married!:-D
October 17, 2012 at 10:03 am
Hi Guys,
I'll try and make some time during the week to have a play with this and report back. Otherwise I'll spend some time over the weekend.
Thanks again, for all your help.
October 28, 2012 at 3:46 am
ChrisM@home (10/17/2012)
Eric M Russell (10/15/2012)
If Don has time, it would be interesting to know how this triangular join method compares to the aggregate methods. With small partitions it could be quite performant; with large partitions it won't. TJ's scale poorly.
Hi guys,
I've converted the 3 scripts so they now run and return the same data.
The script results were:
Script 1: 05 seconds returning 5343 records (ChrisMs script)
Script 2: 02 seconds returning 5343 records (Erics 2005 script)
Script 3: 1.36 returning 5343 records (Erics 2000 script)
The first script initially took 32 seconds to return 5550, but I found that Id messed up the joins.
Thanks for the lesson. ;o)
October 28, 2012 at 3:54 am
Don. (10/28/2012)
ChrisM@home (10/17/2012)
Eric M Russell (10/15/2012)
If Don has time, it would be interesting to know how this triangular join method compares to the aggregate methods. With small partitions it could be quite performant; with large partitions it won't. TJ's scale poorly.
Hi guys,
I've converted the 3 scripts so they now run and return the same data.
The script results were:
Script 1: 05 seconds returning 5343 records (ChrisMs script)
Script 2: 02 seconds returning 5343 records (Erics 2005 script)
Script 3: 1.36 returning 5343 records (Erics 2000 script)
The first script initially took 32 seconds to return 5550, but I found that Id messed up the joins.
Thanks for the lesson. ;o)
Thanks for the feedback, Don. The script in bold - is it the 2000-compatible one?
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
October 28, 2012 at 4:02 am
ChrisM@home (10/28/2012)
Thanks for the feedback, Don. The script in bold - is it the 2000-compatible one?
Morning Chris,
It was the following script you gave me help with:
SELECT c.*, h.*
FROM #Contact c
INNER JOIN (
SELECT h1.Accountno, h1.LastDate, RECID = MIN(h1.RECID)
FROM #History h1
INNER JOIN (
SELECT Accountno, MAX(LastDate) LastDate
FROM #History
GROUP BY Accountno
) l
ON l.Accountno = h1.Accountno
AND l.LastDate = h1.LastDate
GROUP BY h1.Accountno, h1.LastDate
) h
ON h.Accountno = c.Accountno
I plugged it into my existing script and got the results needed.
October 28, 2012 at 4:15 am
Don. (10/28/2012)
ChrisM@home (10/28/2012)
Thanks for the feedback, Don. The script in bold - is it the 2000-compatible one?
Morning Chris,
It was the following script you gave me help with:
SELECT c.*, h.*FROM #Contact c
INNER JOIN (
SELECT h1.Accountno, h1.LastDate, RECID = MIN(h1.RECID)
FROM #History h1
INNER JOIN (
SELECT Accountno, MAX(LastDate) LastDate
FROM #History
GROUP BY Accountno
) l
ON l.Accountno = h1.Accountno
AND l.LastDate = h1.LastDate
GROUP BY h1.Accountno, h1.LastDate
) h
ON h.Accountno = c.Accountno
I plugged it into my existing script and got the results needed.
Thanks Don - that's the 2000-compatible script. If you're curious about the performance of the triangular join method posted by Eric, you will find the answer in Jeff Moden's excellent article linked in my sig, bottom left.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
October 28, 2012 at 4:35 am
I'll have a read.
Thanks Chris.
October 29, 2012 at 12:13 pm
Don.,
Are you using a Touchstar system database?
October 29, 2012 at 12:52 pm
Lee Crain (10/29/2012)
Don.,Are you using a Touchstar system database?
Hi Lee,
Its for a CMS product called Goldmine.
October 30, 2012 at 3:38 am
Hi Don,
Can you please check below query -
SELECT c.*, h.*
FROM #CONTACT c
INNER JOIN (
SELECT Accountno, ROW_NUMBER() OVER(PARTITION BY accountno ORDER BY lastdate) Rownum ,RECID FROM #History
GROUP BY Accountno,LastDate,RECID) h
ON c.ACCOUNTNO=h.ACCOUNTNO
WHERE h.Rownum=1
October 30, 2012 at 3:41 am
chandan.kumar (10/30/2012)
Hi Don,Can you please check below query -
SELECT c.*, h.*
FROM #CONTACT c
INNER JOIN (
SELECT Accountno, ROW_NUMBER() OVER(PARTITION BY accountno ORDER BY lastdate) Rownum ,RECID FROM #History
GROUP BY Accountno,LastDate,RECID) h
ON c.ACCOUNTNO=h.ACCOUNTNO
WHERE h.Rownum=1
Always read the whole thread. It's a spec.
Don. (10/12/2012)
Any suggestions as to how Id be able to get the same results except using scripts for an older backend( SQL 2000 🙁 )?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 13 posts - 16 through 27 (of 27 total)
You must be logged in to reply to this topic. Login to reply