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)
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)