How do I speed up this self-referencing query?

G

Guest

Hi,

I am running this query below which aims to consolidate the data from a
table called Fin0AllAustria into a table called Fin0Austria.

Fin0AllAustria's primary key is SE (an ID number), RS (which can be 'R' or
'S') and Quarter combined, while Fin0Austria's primary key is SE.

Therefore the idea is to pick one entry from the first table to populate the
second. There may be any number of records (allowing for the primary key
constraints of course) for each SE so the logic is to take the max quarter
and then the max RS ('S' is > 'R') and select this record. All the other
fields are copied straight across.

This needs to be done with a self-referencing query but mine must be too
complicated; the query below has been running for a couple of hours (and the
data doesn't justify that amount of time) but I think it works. What have I
done wrong here?

Many thanks!
Rob

INSERT INTO Fin0Austria (SE, Country, Lvl, RTOC, Lv10, Lv9, Lv8, Lv7, Lv6,
Lv5, Lv2, ATOC, Postcode, City, SalesID, CLMID, Name, DiscRate, MarkAgr,
SetupDate, MajorInd, MinorInd, DetailInd, SEIMS)
SELECT SE, Country, Lvl, RTOC, Lv10, Lv9, Lv8, Lv7, Lv6, Lv5, Lv2, ATOC,
Postcode, City, SalesID, CLMID, Name, DiscRate, MarkAgr, SetupDate, MajorInd,
MinorInd, DetailInd, SEIMS from Fin0AllAustria f
WHERE RS = (SELECT max(RS) FROM Fin0AllAustria WHERE Quarter = (SELECT
max(Quarter) FROM Fin0AllAustria WHERE SE = f.SE))
And Quarter = (SELECT max(Quarter) FROM Fin0AllAustria WHERE SE = f.SE)
 
N

Nikos Yannacopoulos

Robert,

Try this query:

SELECT SE, Country, Lvl, RTOC, Lv10, Lv9, Lv8, Lv7, Lv6, Lv5, Lv2, ATOC,
Postcode, City, SalesID, CLMID, DiscRate, MarkAgr, SetupDate, MajorInd,
DetailInd, SEIMS, Max(RS) AS MaxOfRS, Max(Quarter) AS MaxOfQuarter
FROM Fin0AllAustria
GROUP BY SE, Country, Lvl, RTOC, Lv10, Lv9, Lv8, Lv7, Lv6, Lv5, Lv2,
ATOC, Postcode, City, SalesID, CLMID, DiscRate, MarkAgr, SetupDate,
MajorInd, DetailInd, SEIMS

It should return the records you want. If happy with it, you can change
it into an Append query:

INSERT INTO Fin0Austria (SE, Country, Lvl, RTOC, Lv10, Lv9, Lv8, Lv7,
Lv6, Lv5, Lv2, ATOC, Postcode, City, SalesID, CLMID, Name, DiscRate,
MarkAgr, SetupDate, MajorInd, MinorInd, DetailInd, SEIMS)
SELECT SE, Country, Lvl, RTOC, Lv10, Lv9, Lv8, Lv7, Lv6, Lv5, Lv2, ATOC,
Postcode, City, SalesID, CLMID, DiscRate, MarkAgr, SetupDate, MajorInd,
DetailInd, SEIMS, Max(RS) AS MaxOfRS, Max(Quarter) AS MaxOfQuarter
FROM Fin0AllAustria
GROUP BY SE, Country, Lvl, RTOC, Lv10, Lv9, Lv8, Lv7, Lv6, Lv5, Lv2,
ATOC, Postcode, City, SalesID, CLMID, DiscRate, MarkAgr, SetupDate,
MajorInd, DetailInd, SEIMS

I must ask, though, why you need to appen the records to another table,
when all you need is the first query above to do whatever you wish
(report, I guess) on the fly. Chances are you don't really need it.

HTH,
Nikos
 
Top