Hello,
I am working on a query for a dog agility venue where it's calculating award requirements.
There are two columns that are identifiers, one is the RegNum which is the dogs identification number, and the other is Class_Subset, which identifies which class the points were earned under. And then there is another column, DRI, which needs to be over a 99.99 before the award is given.
But it also needs to count how many there are. Such as the first one being a P1 and the second being a P2 and so on and so forth.
So here is what I have so far:
SELECT
PointsUploading.Hgt, PointsUploading.RegNum, PointsUploading.Callname, PointsUploading.OLastname,
PointsUploading.Tpe, PointsUploading.Points, PointsUploading.Class, PointsUploading.Total_Points,
PointsUploading.Title, PointsUploading.Platinum, PointsUploading.Silver_Purple,
PointsUploading.Date_Earned, PointsUploading.Judge, PointsUploading.HostClub, PointsUploading.DRI,
PointsUploading.Class_Subset, PointsUploading.IncorrectRegNum, PointsUploading.MemNum, PointsUploading.ID
FROM
(
SELECT
Hgt, RegNum, Callname, OLastname, Tpe, Points, Class,
Total_Points, Title, Silver_Purple, Date_Earned, Judge,
HostClub, DRI, Class_Subset, IncorrectRegNum, MemNum, ID,
IF (
Class_Subset = @prevSub
AND RegNum = @prevNum
AND `DRI` > 99.99,
@Platinum := @Platinum + 1,
0
) AS Platinum,
@prevSub := Class_Subset,
@prevNum := RegNum
FROM
PointsUploading,
(
SELECT @Platinum := 0, @prevSub := '', @prevNum := ''
) r
ORDER BY RegNum, Class_Subset, Date_Earned
) PointsUploading
Obviously this doesn't work or I wouldn't be here.
The result I get from this is it won't give out the award for the first entry, because there is no previous number to go off of, so it comes back false and gives a 0. But then when there are multiple entries in a row, it will still skip the first one, because it doesn't match anything, but then it will start giving out the award for the second and third entries. But they are always off by 1.
And then it also does not reset. So rather then when it gets to a new dog and it starts at 1, it starts at wherever it left off with the last one.
I hope this isn't too vague, not sure what the best way is to explain it. Maybe a screenshot of what the correct file should look like?
