0

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?

2
Contributors
3
Replies
29
Views
4 Years
Discussion Span
Last Post by ChopTop69
0

I hope this isn't too vague, not sure what the best way is to explain it.

It would help if you include your table structure and test data. A sample of what the output should look like would be helpful too. It's very hard to answer this seeing just the query.

0

Structure:

CREATE TABLE `PointsUploading` (
  `Hgt` varchar(3) default '-',
  `RegNum` varchar(10) NOT NULL,
  `Callname` varchar(20) NOT NULL,
  `OLastname` varchar(50) default '-',
  `Type` varchar(10) default '-',
  `Points` varchar(10) NOT NULL,
  `Class` varchar(10) default '-',
  `Total_Points` int(10) NOT NULL,
  `Title` varchar(25) default '-',
  `Platinum` varchar(20) default '-',
  `Silver_Purple` varchar(20) default '-',
  `Date_Earned` date NOT NULL,
  `Judge` varchar(50) default '-',
  `HostClub` varchar(50) default '-',
  `DRI` decimal(10,2) default NULL,
  `Class_Subset` varchar(10) NOT NULL,
  `IncorrectRegNum` varchar(10) default '-',
  `MemNum` varchar(10) NOT NULL,
  `ID` int(10) NOT NULL

Data:

INSERT INTO `PointsUploading` VALUES('12', '01-11062', 'Abby', 'Weisz', '', '10', 'EAC', 315, NULL, 'P1', '', '2007-10-07', 'Daun', 'AARF Oct-07', 101.87, 'RZ', '', 'MU101', 335172);
INSERT INTO `PointsUploading` VALUES('12', '01-11062', 'Abby', 'Weisz', '', '10', 'TN-E', 110, NULL, 'P1', '', '2008-10-04', 'Nally', 'AARF Oct-08', 101.65, 'TNZ', '', 'MU101', 730721);
INSERT INTO `PointsUploading` VALUES('20', '01-11068', 'Ronnie', 'Cirinelli', '', '10', 'EJC', 200, 'S-EJC', 'P1', '', '2006-09-20', '', 'NADAC Championships Sep-06', 100.44, 'JZ', '', 'ME522', 154795);
INSERT INTO `PointsUploading` VALUES('20', '01-11068', 'Ronnie', 'Cirinelli', '', '10', 'EJC', 210, NULL, 'P2', '', '2006-09-20', '', 'NADAC Championships Sep-06', 108.13, 'JZ', '', 'ME522', 154796);
INSERT INTO `PointsUploading` VALUES('16', '01-11068', 'Ronnie', 'Cirinelli', '', '10', 'EJC-V', 390, NULL, 'P3', '', '2008-09-17', 'NADAC judges', 'NADAC Championships 2008', 107.02, 'JZ', '', 'ME522', 154814);
INSERT INTO `PointsUploading` VALUES('16', '01-11068', 'Ronnie', 'Cirinelli', '', '10', 'EJC-V', 400, 'EJC-400', 'P4', '', '2008-09-17', 'NADAC judges', 'NADAC Championships 2008', 110.00, 'JZ', '', 'ME522', 154815);
INSERT INTO `PointsUploading` VALUES('16', '01-11068', 'Ronnie', 'Cirinelli', '', '10', 'EAC-V', 485, NULL, 'P1', '', '2008-05-24', 'Birdsong', 'Manzanita Agility club May-08', 100.86, 'RZ', '', 'ME522', 335232);
INSERT INTO `PointsUploading` VALUES('16', '01-11068', 'Ronnie', 'Cirinelli', '', '10', 'EAC-V', 595, NULL, 'P2', '', '2009-03-01', 'Perlmutter', 'BARK Feb-09', 100.41, 'RZ', '', 'ME522', 335243);
INSERT INTO `PointsUploading` VALUES('20', '01-11068', 'Ronnie', 'Cirinelli', '', '10', 'TG-E', 150, NULL, 'P1', '', '2008-07-27', 'Brown', 'Manzanita Agility Jul-08', 100.92, 'TGZ', '', 'ME522', 630088);
INSERT INTO `PointsUploading` VALUES('20', '01-11068', 'Ronnie', 'Cirinelli', '', '10', 'TN-E', 240, NULL, 'P1', '', '2008-02-09', 'Judges List', 'NADAC Ramona Feb8-10-08', 106.35, 'TNZ', '', 'ME522', 730747);
INSERT INTO `PointsUploading` VALUES('20', '01-11068', 'Ronnie', 'Cirinelli', '', '10', 'TN-E', 310, NULL, 'P2', '', '2009-11-01', 'Brown', 'Manzanita Agility Oct-09', 103.13, 'TNZ', '', 'ME522', 730754);
INSERT INTO `PointsUploading` VALUES('20', '01-11073', 'Harley', 'Perry', '', '10', 'EJC', 510, NULL, 'P1', '', '2006-01-07', '', 'Columbia Agility Team Jan-06', 109.76, 'JZ', '', 'MG076', 975519);
INSERT INTO `PointsUploading` VALUES('20', '01-11073', 'Harley', 'Perry', '', '10', 'EJC', 520, NULL, 'P2', '', '2006-01-08', '', 'Columbia Agility Team Jan-06', 107.20, 'JZ', '', 'MG076', 975510);
INSERT INTO `PointsUploading` VALUES('20', '01-11073', 'Harley', 'Perry', '', '10', 'EJC', 560, NULL, 'P3', '', '2006-03-11', '', 'Top Dog Mar-06', 109.25, 'JZ', '', 'MG076', 975506);
INSERT INTO `PointsUploading` VALUES('20', '01-11073', 'Harley', 'Perry', '', '10', 'EJC', 570, NULL, 'P4', '', '2006-03-12', '', 'Top Dog Mar-06', 110.00, 'JZ', '', 'MG076', 975500);
INSERT INTO `PointsUploading` VALUES('20', '01-11073', 'Harley', 'Perry', '', '10', 'EJC', 580, NULL, 'P5', '', '2006-04-23', '', 'Extreme Agility Team Apr-06', 110.00, 'JZ', '', 'MG076', 975499);
INSERT INTO `PointsUploading` VALUES('20', '01-11073', 'Harley', 'Perry', '', '10', 'EJC', 590, NULL, 'P6', '', '2006-06-04', '', 'Zooming Agility Pups Jun-06', 103.57, 'JZ', '', 'MG076', 975498);
INSERT INTO `PointsUploading` VALUES('20', '01-11073', 'Harley', 'Perry', '', '10', 'EJC', 600, 'EJC-600', 'P7', '', '2006-06-11', '', 'NADAC Auburn Jun-06', 110.00, 'JZ', '', 'MG076', 975497);
INSERT INTO `PointsUploading` VALUES('20', '01-11073', 'Harley', 'Perry', '', '10', 'EJC', 610, NULL, 'P8', '', '2006-06-24', '', 'Rainier Agility Team Jun-06', 106.97, 'JZ', '', 'MG076', 975496);
INSERT INTO `PointsUploading` VALUES('20', '01-11073', 'Harley', 'Perry', '', '10', 'EJC', 620, NULL, 'P9', '', '2006-06-25', '', 'Rainier Agility Team Jun-06', 101.19, 'JZ', '', 'MG076', 975495);
INSERT INTO `PointsUploading` VALUES('20', '01-11073', 'Harley', 'Perry', '', '10', 'EJC', 700, 'EJC-700', 'P10', '', '2007-02-10', '', 'Rainier Agility Team Feb-07', 110.00, 'JZ', '', 'MG076', 975477);
INSERT INTO `PointsUploading` VALUES('20', '01-11073', 'Harley', 'Perry', '', '10', 'EJC', 720, NULL, 'P11', '', '2007-02-25', '', 'Columbia Agility Team Feb-07', 106.50, 'JZ', '', 'MG076', 975475);
INSERT INTO `PointsUploading` VALUES('20', '01-11073', 'Harley', 'Perry', '', '10', 'EJC', 730, 'NATCH-7-Jum', 'P12', '', '2007-04-22', '', 'Extreme Agility Team Apr-07', 108.92, 'JZ', '', 'MG076', 975474);
INSERT INTO `PointsUploading` VALUES('20', '01-11073', 'Harley', 'Perry', '', '10', 'EJC', 740, NULL, 'P13', '', '2007-05-27', '', 'NADAC Corvallis MT May 26-28-07', 101.16, 'JZ', '', 'MG076', 975473);
INSERT INTO `PointsUploading` VALUES('20', '01-11073', 'Harley', 'Perry', '', '10', 'EJC', 770, NULL, 'P14', '', '2007-06-20', '', 'Rainier Agility Team Jun-07', 102.64, 'JZ', '', 'MG076', 975452);
INSERT INTO `PointsUploading` VALUES('20', '01-11073', 'Harley', 'Perry', '', '10', 'EJC', 780, NULL, 'P15', '', '2007-06-21', '', 'Rainier Agility Team Jun-07', 110.00, 'JZ', '', 'MG076', 975313);
INSERT INTO `PointsUploading` VALUES('20', '01-11073', 'Harley', 'Perry', '', '10', 'EJC', 790, NULL, 'P16', '', '2007-08-31', '', 'NADAC Gold Beach Labor Day-07', 110.00, 'JZ', '', 'MG076', 975236);
INSERT INTO `PointsUploading` VALUES('20', '01-11073', 'Harley', 'Perry', '', '10', 'EJC', 830, 'NATCH-8-Jum', 'P17', '', '2007-09-20', '', 'NADAC Championships Sep-07', 110.00, 'JZ', '', 'MG076', 975232);
INSERT INTO `PointsUploading` VALUES('20', '01-11073', 'Harley', 'Perry', '', '10', 'EJC', 860, NULL, 'P18', '', '2007-10-28', 'Daun', 'Willamette Agility Group Oct-07', 105.14, 'JZ', '', 'MG076', 975216);
INSERT INTO `PointsUploading` VALUES('20', '01-11073', 'Harley', 'Perry', '', '10', 'EJC', 870, NULL, 'P19', '', '2007-11-23', 'Katzen', 'ZAP Nov-07', 105.16, 'JZ', '', 'MG076', 975215);

And ideally what it shoudld look like is the attached image
7fb29a5a16cf07ef160bdf179da3aeb7

Edited by ChopTop69: missed image

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.