SitePoint Sponsor

User Tag List

Results 1 to 24 of 24

Thread: Moving average

  1. #1
    SitePoint Evangelist Miguel61's Avatar
    Join Date
    Mar 2008
    Posts
    402
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Moving average

    Moving average

    Hello guys, I've problem with MySQL and need your help.

    I attach the file xlsx in the test.zip.

    This file xlsx is able to calculate the moving average (column G - AVERAGE_12_MONTHS) of the column F (F_SUM).

    The average is dynamic because in the column G is a line feed, e.g.:

    In the cell G13 I've the average of cell F2:F13, in the cell G14 the average is of cells F3:F14, in the cell G15 the average is of cells F4:F15... etc

    I need realize the same average in mysql, any suggestion?
    Can you help me?
    Any help would be very appreciated
    Attached Files Attached Files

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    1. i cannot read zip files
    2. i don't want to read xlsx files

    please restate your question in terms of database tables
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Evangelist Miguel61's Avatar
    Join Date
    Mar 2008
    Posts
    402
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    1. i cannot read zip files
    2. i don't want to read xlsx files

    please restate your question in terms of database tables
    Ok, I understand.

    In this table `page1-1` I need:
    1. In column `F_SUM` update with sum of colums values `F1`, `F2` and `F3`
    2. In column calculate `AVERAGE_12_MONTHS` the dynamic average of the column `F_SUM`

    The average is dynamic because in the column `AVERAGE_12_MONTHS` is a line feed, e.g.:
    • In the record number 12 I've the average of records 1,2,3,4,5,6,7,8,9,10,11 and 12
    • In the record number 13 I've the average of records 2,3,4,5,6,7,8,9,10,11,12 and 13
    • In the record number 14 I've the average of records 3,4,5,6,7,8,9,10,11,12,13 and 14
    • .....

    Code:
    -- ----------------------------
    -- Table structure for `page1-1`
    -- ----------------------------
    DROP TABLE IF EXISTS `page1-1`;
    CREATE TABLE `page1-1` (
      `YEARS` varchar(255) DEFAULT NULL,
      `MONTHS` varchar(255) DEFAULT NULL,
      `F1` varchar(255) DEFAULT NULL,
      `F2` varchar(255) DEFAULT NULL,
      `F3` varchar(255) DEFAULT NULL,
      `F_SUM` varchar(255) DEFAULT NULL,
      `AVERAGE_12_MONTHS` varchar(255) DEFAULT NULL,
      `VAR` varchar(255) DEFAULT NULL,
      `ID` int(11) NOT NULL AUTO_INCREMENT,
      PRIMARY KEY (`ID`)
    ) ENGINE=MyISAM AUTO_INCREMENT=54 DEFAULT CHARSET=latin1;
    
    -- ----------------------------
    -- Records of page1-1
    -- ----------------------------
    INSERT INTO `page1-1` VALUES ('2009', '05', '92.626,4', '32.796,56', '59.924,640002', '', '', '', '1');
    INSERT INTO `page1-1` VALUES ('2009', '06', '95.008,64', '31.933,6', '56.977,839993', '', '', '', '2');
    INSERT INTO `page1-1` VALUES ('2009', '07', '111.853,12', '33.091,92', '53.262,400004', '', '', '', '3');
    INSERT INTO `page1-1` VALUES ('2009', '08', '108.379,92', '32.500,48', '60.452,640005', '', '', '', '4');
    INSERT INTO `page1-1` VALUES ('2009', '09', '102.605,44', '34.639,36', '58.108,399992', '', '', '', '5');
    INSERT INTO `page1-1` VALUES ('2009', '10', '83.442,56', '31.952,400002', '52.181,6', '', '', '', '6');
    INSERT INTO `page1-1` VALUES ('2009', '11', '55.527,84', '24.864', '47.962,800004', '', '', '', '7');
    INSERT INTO `page1-1` VALUES ('2009', '12', '45.067,28', '21.627,28', '50.793,6', '', '', '', '8');
    INSERT INTO `page1-1` VALUES ('2010', '01', '50.108,6386', '32.070,35916', '88.992,39535', '', '', '', '9');
    INSERT INTO `page1-1` VALUES ('2010', '02', '45.229,00261', '32.119,99696', '57.848,00256', '', '', '', '10');
    INSERT INTO `page1-1` VALUES ('2010', '03', '51.329,99474', '34.104,99888', '64.472,82168', '', '', '', '11');
    INSERT INTO `page1-1` VALUES ('2010', '04', '47.097,99864', '38.268,00225', '77.124,0064', '', '', '', '12');
    INSERT INTO `page1-1` VALUES ('2010', '05', '43.667,00318', '31.968,99992', '108.472,0064', '', '', '', '13');
    INSERT INTO `page1-1` VALUES ('2010', '06', '55.458,99688', '32.521,00124', '103.608', '', '', '', '14');
    INSERT INTO `page1-1` VALUES ('2010', '07', '99.716,0032', '33.510,99578', '74.126,99424', '', '', '', '15');
    INSERT INTO `page1-1` VALUES ('2010', '08', '101.051,99552', '31.377,0024', '67.117,99712', '', '', '', '16');
    INSERT INTO `page1-1` VALUES ('2010', '09', '72.834,00258', '35.291,00016', '81.023,00416', '', '', '', '17');
    INSERT INTO `page1-1` VALUES ('2010', '10', '42.791,00364', '44.317,0016', '87.538,9998', '', '', '', '18');
    INSERT INTO `page1-1` VALUES ('2010', '11', '41.505,99693', '32.237,00012', '66.770,9952', '', '', '', '19');
    INSERT INTO `page1-1` VALUES ('2010', '12', '38.599,005', '27.211,99872', '61.212,99744', '', '', '', '20');
    INSERT INTO `page1-1` VALUES ('2011', '01', '55.458,99761', '28.852,99888', '60.279,0048', '', '', '', '21');
    INSERT INTO `page1-1` VALUES ('2011', '02', '46.786,00489', '29.952,00224', '58.459,00032', '', '', '', '22');
    INSERT INTO `page1-1` VALUES ('2011', '03', '52.550,00568', '32.447,00064', '64.910,76056', '', '', '', '23');
    INSERT INTO `page1-1` VALUES ('2011', '04', '39.288,9992', '41.818,0032', '81.383,00113', '', '', '', '24');
    INSERT INTO `page1-1` VALUES ('2011', '05', '84.902,99957', '32.890,002', '66.315,99776', '', '', '', '25');
    INSERT INTO `page1-1` VALUES ('2011', '06', '94.883,73246', '35.711,45084', '60.993,83012', '', '', '', '26');
    INSERT INTO `page1-1` VALUES ('2011', '07', '113.265,999', '38.767,0016', '55.319,98993', '', '', '', '27');
    INSERT INTO `page1-1` VALUES ('2011', '08', '122.653,8295', '28.179,25128', '48.713,92516', '', '', '', '28');
    INSERT INTO `page1-1` VALUES ('2011', '09', '118.939,00216', '25.380,0064', '44.827,99808', '', '', '', '29');
    INSERT INTO `page1-1` VALUES ('2011', '10', '72.671,00321', '35.154,0034', '66.821,9968', '', '', '', '30');
    INSERT INTO `page1-1` VALUES ('2011', '11', '52.688,03987', '29.032,06384', '58.792,89148', '', '', '', '31');
    INSERT INTO `page1-1` VALUES ('2011', '12', '50.778,1824', '24.184,41893', '52.061,39352', '', '', '', '32');
    INSERT INTO `page1-1` VALUES ('2012', '01', '67.452', '27.755,99892', '49.381,99808', '', '', '', '33');
    INSERT INTO `page1-1` VALUES ('2012', '02', '65.157,00029', '25.064,9984', '44.974,99776', '', '', '', '34');
    INSERT INTO `page1-1` VALUES ('2012', '03', '51.895,99976', '35.507,99695', '62.303,66252', '', '', '', '35');
    INSERT INTO `page1-1` VALUES ('2012', '04', '59.680,0026', '34.401,99684', '68.407,00416', '', '', '', '36');
    INSERT INTO `page1-1` VALUES ('2012', '05', '76.293,00236', '36.553,00128', '71.262,99872', '', '', '', '37');
    INSERT INTO `page1-1` VALUES ('2012', '06', '91.630,00308', '36.862,00284', '63.096,99387', '', '', '', '38');
    INSERT INTO `page1-1` VALUES ('2012', '07', '121.355,00758', '35.470,99968', '50.525,99904', '', '', '', '39');
    INSERT INTO `page1-1` VALUES ('2012', '08', '104.053,9946', '38.849,00088', '56.643,00224', '', '', '', '40');
    INSERT INTO `page1-1` VALUES ('2012', '09', '87.243,99989', '38.957,9976', '62.945,9968', '', '', '', '41');
    INSERT INTO `page1-1` VALUES ('2012', '10', '75.514,00438', '35.562,0016', '63.570,9984', '', '', '', '42');
    INSERT INTO `page1-1` VALUES ('2012', '11', '47.608,00044', '30.661,00425', '62.245,0048', '', '', '', '43');
    INSERT INTO `page1-1` VALUES ('2012', '12', '13.527,2', '10.924,04032', '26.763,04036', '', '', '', '44');
    INSERT INTO `page1-1` VALUES ('2013', '01', '15.955,44', '9.888,8', '18.707,36', '', '', '', '45');
    INSERT INTO `page1-1` VALUES ('2013', '02', '14.813,76', '9.452,24', '17.036,08', '', '', '', '46');
    INSERT INTO `page1-1` VALUES ('2013', '03', '13.567,92', '9.950,64', '18.263,44', '', '', '', '47');
    INSERT INTO `page1-1` VALUES ('2013', '04', '12.965,6', '8.086', '17.543,6', '', '', '', '48');
    INSERT INTO `page1-1` VALUES ('2013', '05', '12.363,76', '8.034,64', '16.605,04', '', '', '', '49');
    INSERT INTO `page1-1` VALUES ('2013', '06', '15.822,88', '8.388,88', '16.211,92', '', '', '', '50');
    INSERT INTO `page1-1` VALUES ('2013', '07', '21.269,04', '9.082,4', '16.352,48', '', '', '', '51');
    INSERT INTO `page1-1` VALUES ('2013', '08', '18.727,44', '10.274,16', '18.108,96', '', '', '', '52');
    INSERT INTO `page1-1` VALUES ('2013', '09', '16.470,56', '8.159,76', '16.271,92', '', '', '', '53');

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Miguel61 View Post
    In column `F_SUM` update with sum of colums values `F1`, `F2` and `F3`
    let's start with this problem
    Code:
    UPDATE `page1-1` 
       SET F_sum = F1 + F2 + F3
    this gives the error message "Truncated incorrect DOUBLE value 59.924,640002"

    i think the problem is your use of VARCHAR(255) for numeric values
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Evangelist Miguel61's Avatar
    Join Date
    Mar 2008
    Posts
    402
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    let's start with this problem
    Code:
    UPDATE `page1-1` 
       SET F_sum = F1 + F2 + F3
    this gives the error message "Truncated incorrect DOUBLE value 59.924,640002"

    i think the problem is your use of VARCHAR(255) for numeric values
    thank you for help.
    this is new version of table `page1-1`.
    I've update in column `F_SUM` with sum of colums values `F1`, `F2` and `F3`.

    But I can not calculate the average dynamic... because in the column `AVERAGE_12_MONTHS` is a line feed, e.g.:
    • In the record number 12 I've the average of records 1,2,3,4,5,6,7,8,9,10,11 and 12
    • In the record number 13 I've the average of records 2,3,4,5,6,7,8,9,10,11,12 and 13
    • In the record number 14 I've the average of records 3,4,5,6,7,8,9,10,11,12,13 and 14
    • .....

    Code:
    -- ----------------------------
    -- Table structure for `page1-1`
    -- ----------------------------
    DROP TABLE IF EXISTS `page1-1`;
    CREATE TABLE `page1-1` (
      `YEARS` int(11) DEFAULT NULL,
      `MONTHS` int(11) DEFAULT NULL,
      `F1` decimal(10,4) DEFAULT NULL,
      `F2` decimal(10,4) DEFAULT NULL,
      `F3` decimal(10,4) DEFAULT NULL,
      `F_SUM` varchar(255) DEFAULT NULL,
      `AVERAGE_12_MONTHS` varchar(255) DEFAULT NULL,
      `VAR` varchar(255) DEFAULT NULL,
      `ID` int(11) NOT NULL AUTO_INCREMENT,
      PRIMARY KEY (`ID`)
    ) ENGINE=MyISAM AUTO_INCREMENT=54 DEFAULT CHARSET=latin1;
    
    -- ----------------------------
    -- Records of page1-1
    -- ----------------------------
    INSERT INTO `page1-1` VALUES ('2009', '5', '92626.4000', '32796.5600', '59924.6400', '185347.6000', '', '', '1');
    INSERT INTO `page1-1` VALUES ('2009', '6', '95008.6400', '31933.6000', '56977.8400', '183920.0800', '', '', '2');
    INSERT INTO `page1-1` VALUES ('2009', '7', '111853.1200', '33091.9200', '53262.4000', '198207.4400', '', '', '3');
    INSERT INTO `page1-1` VALUES ('2009', '8', '108379.9200', '32500.4800', '60452.6400', '201333.0400', '', '', '4');
    INSERT INTO `page1-1` VALUES ('2009', '9', '102605.4400', '34639.3600', '58108.4000', '195353.2000', '', '', '5');
    INSERT INTO `page1-1` VALUES ('2009', '10', '83442.5600', '31952.4000', '52181.6000', '167576.5600', '', '', '6');
    INSERT INTO `page1-1` VALUES ('2009', '11', '55527.8400', '24864.0000', '47962.8000', '128354.6400', '', '', '7');
    INSERT INTO `page1-1` VALUES ('2009', '12', '45067.2800', '21627.2800', '50793.6000', '117488.1600', '', '', '8');
    INSERT INTO `page1-1` VALUES ('2010', '1', '50108.6386', '32070.3592', '88992.3954', '171171.3932', '', '', '9');
    INSERT INTO `page1-1` VALUES ('2010', '2', '45229.0026', '32119.9970', '57848.0026', '135197.0022', '', '', '10');
    INSERT INTO `page1-1` VALUES ('2010', '3', '51329.9947', '34104.9989', '64472.8217', '149907.8153', '', '', '11');
    INSERT INTO `page1-1` VALUES ('2010', '4', '47097.9986', '38268.0023', '77124.0064', '162490.0073', '', '', '12');
    INSERT INTO `page1-1` VALUES ('2010', '5', '43667.0032', '31968.9999', '108472.0064', '184108.0095', '', '', '13');
    INSERT INTO `page1-1` VALUES ('2010', '6', '55458.9969', '32521.0012', '103608.0000', '191587.9981', '', '', '14');
    INSERT INTO `page1-1` VALUES ('2010', '7', '99716.0032', '33510.9958', '74126.9942', '207353.9932', '', '', '15');
    INSERT INTO `page1-1` VALUES ('2010', '8', '101051.9955', '31377.0024', '67117.9971', '199546.9950', '', '', '16');
    INSERT INTO `page1-1` VALUES ('2010', '9', '72834.0026', '35291.0002', '81023.0042', '189148.0070', '', '', '17');
    INSERT INTO `page1-1` VALUES ('2010', '10', '42791.0036', '44317.0016', '87538.9998', '174647.0050', '', '', '18');
    INSERT INTO `page1-1` VALUES ('2010', '11', '41505.9969', '32237.0001', '66770.9952', '140513.9922', '', '', '19');
    INSERT INTO `page1-1` VALUES ('2010', '12', '38599.0050', '27211.9987', '61212.9974', '127024.0011', '', '', '20');
    INSERT INTO `page1-1` VALUES ('2011', '1', '55458.9976', '28852.9989', '60279.0048', '144591.0013', '', '', '21');
    INSERT INTO `page1-1` VALUES ('2011', '2', '46786.0049', '29952.0022', '58459.0003', '135197.0074', '', '', '22');
    INSERT INTO `page1-1` VALUES ('2011', '3', '52550.0057', '32447.0006', '64910.7606', '149907.7669', '', '', '23');
    INSERT INTO `page1-1` VALUES ('2011', '4', '39288.9992', '41818.0032', '81383.0011', '162490.0035', '', '', '24');
    INSERT INTO `page1-1` VALUES ('2011', '5', '84902.9996', '32890.0020', '66315.9978', '184108.9994', '', '', '25');
    INSERT INTO `page1-1` VALUES ('2011', '6', '94883.7325', '35711.4508', '60993.8301', '191589.0134', '', '', '26');
    INSERT INTO `page1-1` VALUES ('2011', '7', '113265.9990', '38767.0016', '55319.9899', '207352.9905', '', '', '27');
    INSERT INTO `page1-1` VALUES ('2011', '8', '122653.8295', '28179.2513', '48713.9252', '199547.0060', '', '', '28');
    INSERT INTO `page1-1` VALUES ('2011', '9', '118939.0022', '25380.0064', '44827.9981', '189147.0067', '', '', '29');
    INSERT INTO `page1-1` VALUES ('2011', '10', '72671.0032', '35154.0034', '66821.9968', '174647.0034', '', '', '30');
    INSERT INTO `page1-1` VALUES ('2011', '11', '52688.0399', '29032.0638', '58792.8915', '140512.9952', '', '', '31');
    INSERT INTO `page1-1` VALUES ('2011', '12', '50778.1824', '24184.4189', '52061.3935', '127023.9948', '', '', '32');
    INSERT INTO `page1-1` VALUES ('2012', '1', '67452.0000', '27755.9989', '49381.9981', '144589.9970', '', '', '33');
    INSERT INTO `page1-1` VALUES ('2012', '2', '65157.0003', '25064.9984', '44974.9978', '135196.9965', '', '', '34');
    INSERT INTO `page1-1` VALUES ('2012', '3', '51895.9998', '35507.9970', '62303.6625', '149707.6593', '', '', '35');
    INSERT INTO `page1-1` VALUES ('2012', '4', '59680.0026', '34401.9968', '68407.0042', '162489.0036', '', '', '36');
    INSERT INTO `page1-1` VALUES ('2012', '5', '76293.0024', '36553.0013', '71262.9987', '184109.0024', '', '', '37');
    INSERT INTO `page1-1` VALUES ('2012', '6', '91630.0031', '36862.0028', '63096.9939', '191588.9998', '', '', '38');
    INSERT INTO `page1-1` VALUES ('2012', '7', '121355.0076', '35470.9997', '50525.9990', '207352.0063', '', '', '39');
    INSERT INTO `page1-1` VALUES ('2012', '8', '104053.9946', '38849.0009', '56643.0022', '199545.9977', '', '', '40');
    INSERT INTO `page1-1` VALUES ('2012', '9', '87243.9999', '38957.9976', '62945.9968', '189147.9943', '', '', '41');
    INSERT INTO `page1-1` VALUES ('2012', '10', '75514.0044', '35562.0016', '63570.9984', '174647.0044', '', '', '42');
    INSERT INTO `page1-1` VALUES ('2012', '11', '47608.0004', '30661.0043', '62245.0048', '140514.0095', '', '', '43');
    INSERT INTO `page1-1` VALUES ('2012', '12', '13527.2000', '10924.0403', '26763.0404', '51214.2807', '', '', '44');
    INSERT INTO `page1-1` VALUES ('2013', '1', '15955.4400', '9888.8000', '18707.3600', '44551.6000', '', '', '45');
    INSERT INTO `page1-1` VALUES ('2013', '2', '14813.7600', '9452.2400', '17036.0800', '41302.0800', '', '', '46');
    INSERT INTO `page1-1` VALUES ('2013', '3', '13567.9200', '9950.6400', '18263.4400', '41782.0000', '', '', '47');
    INSERT INTO `page1-1` VALUES ('2013', '4', '12965.6000', '8086.0000', '17543.6000', '38595.2000', '', '', '48');
    INSERT INTO `page1-1` VALUES ('2013', '5', '12363.7600', '8034.6400', '16605.0400', '37003.4400', '', '', '49');
    INSERT INTO `page1-1` VALUES ('2013', '6', '15822.8800', '8388.8800', '16211.9200', '40423.6800', '', '', '50');
    INSERT INTO `page1-1` VALUES ('2013', '7', '21269.0400', '9082.4000', '16352.4800', '46703.9200', '', '', '51');
    INSERT INTO `page1-1` VALUES ('2013', '8', '18727.4400', '10274.1600', '18108.9600', '47110.5600', '', '', '52');
    INSERT INTO `page1-1` VALUES ('2013', '9', '16470.5600', '8159.7600', '16271.9200', '40902.2400', '', '', '53');

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Miguel61 View Post
    this is new version of table `page1-1`.
    could you please also change VARCHAR(255) in F_SUM and AVERAGE_12_MONTHS as well
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Evangelist Miguel61's Avatar
    Join Date
    Mar 2008
    Posts
    402
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    could you please also change VARCHAR(255) in F_SUM and AVERAGE_12_MONTHS as well
    Ok:
    Code:
    -- ----------------------------
    -- Table structure for `page1-1`
    -- ----------------------------
    DROP TABLE IF EXISTS `page1-1`;
    CREATE TABLE `page1-1` (
      `YEARS` int(11) DEFAULT NULL,
      `MONTHS` int(11) DEFAULT NULL,
      `F1` decimal(10,4) DEFAULT NULL,
      `F2` decimal(10,4) DEFAULT NULL,
      `F3` decimal(10,4) DEFAULT NULL,
      `F_SUM` decimal(10,4) DEFAULT NULL,
      `AVERAGE_12_MONTHS` decimal(10,4) DEFAULT NULL,
      `VAR` int(11)DEFAULT NULL,
      `ID` int(11) NOT NULL AUTO_INCREMENT,
      PRIMARY KEY (`ID`)
    ) ENGINE=MyISAM AUTO_INCREMENT=54 DEFAULT CHARSET=latin1;
    
    -- ----------------------------
    -- Records of page1-1
    -- ----------------------------
    INSERT INTO `page1-1` VALUES ('2009', '5', '92626.4000', '32796.5600', '59924.6400', '185347.6000', '', '', '1');
    INSERT INTO `page1-1` VALUES ('2009', '6', '95008.6400', '31933.6000', '56977.8400', '183920.0800', '', '', '2');
    INSERT INTO `page1-1` VALUES ('2009', '7', '111853.1200', '33091.9200', '53262.4000', '198207.4400', '', '', '3');
    INSERT INTO `page1-1` VALUES ('2009', '8', '108379.9200', '32500.4800', '60452.6400', '201333.0400', '', '', '4');
    INSERT INTO `page1-1` VALUES ('2009', '9', '102605.4400', '34639.3600', '58108.4000', '195353.2000', '', '', '5');
    INSERT INTO `page1-1` VALUES ('2009', '10', '83442.5600', '31952.4000', '52181.6000', '167576.5600', '', '', '6');
    INSERT INTO `page1-1` VALUES ('2009', '11', '55527.8400', '24864.0000', '47962.8000', '128354.6400', '', '', '7');
    INSERT INTO `page1-1` VALUES ('2009', '12', '45067.2800', '21627.2800', '50793.6000', '117488.1600', '', '', '8');
    INSERT INTO `page1-1` VALUES ('2010', '1', '50108.6386', '32070.3592', '88992.3954', '171171.3932', '', '', '9');
    INSERT INTO `page1-1` VALUES ('2010', '2', '45229.0026', '32119.9970', '57848.0026', '135197.0022', '', '', '10');
    INSERT INTO `page1-1` VALUES ('2010', '3', '51329.9947', '34104.9989', '64472.8217', '149907.8153', '', '', '11');
    INSERT INTO `page1-1` VALUES ('2010', '4', '47097.9986', '38268.0023', '77124.0064', '162490.0073', '', '', '12');
    INSERT INTO `page1-1` VALUES ('2010', '5', '43667.0032', '31968.9999', '108472.0064', '184108.0095', '', '', '13');
    INSERT INTO `page1-1` VALUES ('2010', '6', '55458.9969', '32521.0012', '103608.0000', '191587.9981', '', '', '14');
    INSERT INTO `page1-1` VALUES ('2010', '7', '99716.0032', '33510.9958', '74126.9942', '207353.9932', '', '', '15');
    INSERT INTO `page1-1` VALUES ('2010', '8', '101051.9955', '31377.0024', '67117.9971', '199546.9950', '', '', '16');
    INSERT INTO `page1-1` VALUES ('2010', '9', '72834.0026', '35291.0002', '81023.0042', '189148.0070', '', '', '17');
    INSERT INTO `page1-1` VALUES ('2010', '10', '42791.0036', '44317.0016', '87538.9998', '174647.0050', '', '', '18');
    INSERT INTO `page1-1` VALUES ('2010', '11', '41505.9969', '32237.0001', '66770.9952', '140513.9922', '', '', '19');
    INSERT INTO `page1-1` VALUES ('2010', '12', '38599.0050', '27211.9987', '61212.9974', '127024.0011', '', '', '20');
    INSERT INTO `page1-1` VALUES ('2011', '1', '55458.9976', '28852.9989', '60279.0048', '144591.0013', '', '', '21');
    INSERT INTO `page1-1` VALUES ('2011', '2', '46786.0049', '29952.0022', '58459.0003', '135197.0074', '', '', '22');
    INSERT INTO `page1-1` VALUES ('2011', '3', '52550.0057', '32447.0006', '64910.7606', '149907.7669', '', '', '23');
    INSERT INTO `page1-1` VALUES ('2011', '4', '39288.9992', '41818.0032', '81383.0011', '162490.0035', '', '', '24');
    INSERT INTO `page1-1` VALUES ('2011', '5', '84902.9996', '32890.0020', '66315.9978', '184108.9994', '', '', '25');
    INSERT INTO `page1-1` VALUES ('2011', '6', '94883.7325', '35711.4508', '60993.8301', '191589.0134', '', '', '26');
    INSERT INTO `page1-1` VALUES ('2011', '7', '113265.9990', '38767.0016', '55319.9899', '207352.9905', '', '', '27');
    INSERT INTO `page1-1` VALUES ('2011', '8', '122653.8295', '28179.2513', '48713.9252', '199547.0060', '', '', '28');
    INSERT INTO `page1-1` VALUES ('2011', '9', '118939.0022', '25380.0064', '44827.9981', '189147.0067', '', '', '29');
    INSERT INTO `page1-1` VALUES ('2011', '10', '72671.0032', '35154.0034', '66821.9968', '174647.0034', '', '', '30');
    INSERT INTO `page1-1` VALUES ('2011', '11', '52688.0399', '29032.0638', '58792.8915', '140512.9952', '', '', '31');
    INSERT INTO `page1-1` VALUES ('2011', '12', '50778.1824', '24184.4189', '52061.3935', '127023.9948', '', '', '32');
    INSERT INTO `page1-1` VALUES ('2012', '1', '67452.0000', '27755.9989', '49381.9981', '144589.9970', '', '', '33');
    INSERT INTO `page1-1` VALUES ('2012', '2', '65157.0003', '25064.9984', '44974.9978', '135196.9965', '', '', '34');
    INSERT INTO `page1-1` VALUES ('2012', '3', '51895.9998', '35507.9970', '62303.6625', '149707.6593', '', '', '35');
    INSERT INTO `page1-1` VALUES ('2012', '4', '59680.0026', '34401.9968', '68407.0042', '162489.0036', '', '', '36');
    INSERT INTO `page1-1` VALUES ('2012', '5', '76293.0024', '36553.0013', '71262.9987', '184109.0024', '', '', '37');
    INSERT INTO `page1-1` VALUES ('2012', '6', '91630.0031', '36862.0028', '63096.9939', '191588.9998', '', '', '38');
    INSERT INTO `page1-1` VALUES ('2012', '7', '121355.0076', '35470.9997', '50525.9990', '207352.0063', '', '', '39');
    INSERT INTO `page1-1` VALUES ('2012', '8', '104053.9946', '38849.0009', '56643.0022', '199545.9977', '', '', '40');
    INSERT INTO `page1-1` VALUES ('2012', '9', '87243.9999', '38957.9976', '62945.9968', '189147.9943', '', '', '41');
    INSERT INTO `page1-1` VALUES ('2012', '10', '75514.0044', '35562.0016', '63570.9984', '174647.0044', '', '', '42');
    INSERT INTO `page1-1` VALUES ('2012', '11', '47608.0004', '30661.0043', '62245.0048', '140514.0095', '', '', '43');
    INSERT INTO `page1-1` VALUES ('2012', '12', '13527.2000', '10924.0403', '26763.0404', '51214.2807', '', '', '44');
    INSERT INTO `page1-1` VALUES ('2013', '1', '15955.4400', '9888.8000', '18707.3600', '44551.6000', '', '', '45');
    INSERT INTO `page1-1` VALUES ('2013', '2', '14813.7600', '9452.2400', '17036.0800', '41302.0800', '', '', '46');
    INSERT INTO `page1-1` VALUES ('2013', '3', '13567.9200', '9950.6400', '18263.4400', '41782.0000', '', '', '47');
    INSERT INTO `page1-1` VALUES ('2013', '4', '12965.6000', '8086.0000', '17543.6000', '38595.2000', '', '', '48');
    INSERT INTO `page1-1` VALUES ('2013', '5', '12363.7600', '8034.6400', '16605.0400', '37003.4400', '', '', '49');
    INSERT INTO `page1-1` VALUES ('2013', '6', '15822.8800', '8388.8800', '16211.9200', '40423.6800', '', '', '50');
    INSERT INTO `page1-1` VALUES ('2013', '7', '21269.0400', '9082.4000', '16352.4800', '46703.9200', '', '', '51');
    INSERT INTO `page1-1` VALUES ('2013', '8', '18727.4400', '10274.1600', '18108.9600', '47110.5600', '', '', '52');
    INSERT INTO `page1-1` VALUES ('2013', '9', '16470.5600', '8159.7600', '16271.9200', '40902.2400', '', '', '53');

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    okay, you changed the datatype, but you forgot to change the values in the INSERT statements
    SQL Error (1366): Incorrect decimal value: '' for column 'AVERAGE_12_MONTHS' at row 1
    you cannot insert strings into numeric columns
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Evangelist Miguel61's Avatar
    Join Date
    Mar 2008
    Posts
    402
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    okay, you changed the datatype, but you forgot to change the values in the INSERT statements

    you cannot insert strings into numeric columns

    Code:
    -- ----------------------------
    -- Table structure for `page1-1`
    -- ----------------------------
    DROP TABLE IF EXISTS `page1-1`;
    CREATE TABLE `page1-1` (
      `YEARS` int(11) DEFAULT NULL,
      `MONTHS` int(11) DEFAULT NULL,
      `F1` decimal(10,4) DEFAULT NULL,
      `F2` decimal(10,4) DEFAULT NULL,
      `F3` decimal(10,4) DEFAULT NULL,
      `F_SUM` decimal(10,4) DEFAULT NULL,
      `AVERAGE_12_MONTHS` decimal(10,4) DEFAULT NULL,
      `VAR` int(11) DEFAULT NULL,
      `ID` int(11) NOT NULL AUTO_INCREMENT,
      PRIMARY KEY (`ID`)
    ) ENGINE=MyISAM AUTO_INCREMENT=54 DEFAULT CHARSET=latin1;
    
    -- ----------------------------
    -- Records of page1-1
    -- ----------------------------
    INSERT INTO `page1-1` VALUES ('2009', '5', '92626.4000', '32796.5600', '59924.6400', '185347.6000', null, null, '1');
    INSERT INTO `page1-1` VALUES ('2009', '6', '95008.6400', '31933.6000', '56977.8400', '183920.0800', null, null, '2');
    INSERT INTO `page1-1` VALUES ('2009', '7', '111853.1200', '33091.9200', '53262.4000', '198207.4400', null, null, '3');
    INSERT INTO `page1-1` VALUES ('2009', '8', '108379.9200', '32500.4800', '60452.6400', '201333.0400', null, null, '4');
    INSERT INTO `page1-1` VALUES ('2009', '9', '102605.4400', '34639.3600', '58108.4000', '195353.2000', null, null, '5');
    INSERT INTO `page1-1` VALUES ('2009', '10', '83442.5600', '31952.4000', '52181.6000', '167576.5600', null, null, '6');
    INSERT INTO `page1-1` VALUES ('2009', '11', '55527.8400', '24864.0000', '47962.8000', '128354.6400', null, null, '7');
    INSERT INTO `page1-1` VALUES ('2009', '12', '45067.2800', '21627.2800', '50793.6000', '117488.1600', null, null, '8');
    INSERT INTO `page1-1` VALUES ('2010', '1', '50108.6386', '32070.3592', '88992.3954', '171171.3932', null, null, '9');
    INSERT INTO `page1-1` VALUES ('2010', '2', '45229.0026', '32119.9970', '57848.0026', '135197.0022', null, null, '10');
    INSERT INTO `page1-1` VALUES ('2010', '3', '51329.9947', '34104.9989', '64472.8217', '149907.8153', null, null, '11');
    INSERT INTO `page1-1` VALUES ('2010', '4', '47097.9986', '38268.0023', '77124.0064', '162490.0073', null, null, '12');
    INSERT INTO `page1-1` VALUES ('2010', '5', '43667.0032', '31968.9999', '108472.0064', '184108.0095', null, null, '13');
    INSERT INTO `page1-1` VALUES ('2010', '6', '55458.9969', '32521.0012', '103608.0000', '191587.9981', null, null, '14');
    INSERT INTO `page1-1` VALUES ('2010', '7', '99716.0032', '33510.9958', '74126.9942', '207353.9932', null, null, '15');
    INSERT INTO `page1-1` VALUES ('2010', '8', '101051.9955', '31377.0024', '67117.9971', '199546.9950', null, null, '16');
    INSERT INTO `page1-1` VALUES ('2010', '9', '72834.0026', '35291.0002', '81023.0042', '189148.0070', null, null, '17');
    INSERT INTO `page1-1` VALUES ('2010', '10', '42791.0036', '44317.0016', '87538.9998', '174647.0050', null, null, '18');
    INSERT INTO `page1-1` VALUES ('2010', '11', '41505.9969', '32237.0001', '66770.9952', '140513.9922', null, null, '19');
    INSERT INTO `page1-1` VALUES ('2010', '12', '38599.0050', '27211.9987', '61212.9974', '127024.0011', null, null, '20');
    INSERT INTO `page1-1` VALUES ('2011', '1', '55458.9976', '28852.9989', '60279.0048', '144591.0013', null, null, '21');
    INSERT INTO `page1-1` VALUES ('2011', '2', '46786.0049', '29952.0022', '58459.0003', '135197.0074', null, null, '22');
    INSERT INTO `page1-1` VALUES ('2011', '3', '52550.0057', '32447.0006', '64910.7606', '149907.7669', null, null, '23');
    INSERT INTO `page1-1` VALUES ('2011', '4', '39288.9992', '41818.0032', '81383.0011', '162490.0035', null, null, '24');
    INSERT INTO `page1-1` VALUES ('2011', '5', '84902.9996', '32890.0020', '66315.9978', '184108.9994', null, null, '25');
    INSERT INTO `page1-1` VALUES ('2011', '6', '94883.7325', '35711.4508', '60993.8301', '191589.0134', null, null, '26');
    INSERT INTO `page1-1` VALUES ('2011', '7', '113265.9990', '38767.0016', '55319.9899', '207352.9905', null, null, '27');
    INSERT INTO `page1-1` VALUES ('2011', '8', '122653.8295', '28179.2513', '48713.9252', '199547.0060', null, null, '28');
    INSERT INTO `page1-1` VALUES ('2011', '9', '118939.0022', '25380.0064', '44827.9981', '189147.0067', null, null, '29');
    INSERT INTO `page1-1` VALUES ('2011', '10', '72671.0032', '35154.0034', '66821.9968', '174647.0034', null, null, '30');
    INSERT INTO `page1-1` VALUES ('2011', '11', '52688.0399', '29032.0638', '58792.8915', '140512.9952', null, null, '31');
    INSERT INTO `page1-1` VALUES ('2011', '12', '50778.1824', '24184.4189', '52061.3935', '127023.9948', null, null, '32');
    INSERT INTO `page1-1` VALUES ('2012', '1', '67452.0000', '27755.9989', '49381.9981', '144589.9970', null, null, '33');
    INSERT INTO `page1-1` VALUES ('2012', '2', '65157.0003', '25064.9984', '44974.9978', '135196.9965', null, null, '34');
    INSERT INTO `page1-1` VALUES ('2012', '3', '51895.9998', '35507.9970', '62303.6625', '149707.6593', null, null, '35');
    INSERT INTO `page1-1` VALUES ('2012', '4', '59680.0026', '34401.9968', '68407.0042', '162489.0036', null, null, '36');
    INSERT INTO `page1-1` VALUES ('2012', '5', '76293.0024', '36553.0013', '71262.9987', '184109.0024', null, null, '37');
    INSERT INTO `page1-1` VALUES ('2012', '6', '91630.0031', '36862.0028', '63096.9939', '191588.9998', null, null, '38');
    INSERT INTO `page1-1` VALUES ('2012', '7', '121355.0076', '35470.9997', '50525.9990', '207352.0063', null, null, '39');
    INSERT INTO `page1-1` VALUES ('2012', '8', '104053.9946', '38849.0009', '56643.0022', '199545.9977', null, null, '40');
    INSERT INTO `page1-1` VALUES ('2012', '9', '87243.9999', '38957.9976', '62945.9968', '189147.9943', null, null, '41');
    INSERT INTO `page1-1` VALUES ('2012', '10', '75514.0044', '35562.0016', '63570.9984', '174647.0044', null, null, '42');
    INSERT INTO `page1-1` VALUES ('2012', '11', '47608.0004', '30661.0043', '62245.0048', '140514.0095', null, null, '43');
    INSERT INTO `page1-1` VALUES ('2012', '12', '13527.2000', '10924.0403', '26763.0404', '51214.2807', null, null, '44');
    INSERT INTO `page1-1` VALUES ('2013', '1', '15955.4400', '9888.8000', '18707.3600', '44551.6000', null, null, '45');
    INSERT INTO `page1-1` VALUES ('2013', '2', '14813.7600', '9452.2400', '17036.0800', '41302.0800', null, null, '46');
    INSERT INTO `page1-1` VALUES ('2013', '3', '13567.9200', '9950.6400', '18263.4400', '41782.0000', null, null, '47');
    INSERT INTO `page1-1` VALUES ('2013', '4', '12965.6000', '8086.0000', '17543.6000', '38595.2000', null, null, '48');
    INSERT INTO `page1-1` VALUES ('2013', '5', '12363.7600', '8034.6400', '16605.0400', '37003.4400', null, null, '49');
    INSERT INTO `page1-1` VALUES ('2013', '6', '15822.8800', '8388.8800', '16211.9200', '40423.6800', null, null, '50');
    INSERT INTO `page1-1` VALUES ('2013', '7', '21269.0400', '9082.4000', '16352.4800', '46703.9200', null, null, '51');
    INSERT INTO `page1-1` VALUES ('2013', '8', '18727.4400', '10274.1600', '18108.9600', '47110.5600', null, null, '52');
    INSERT INTO `page1-1` VALUES ('2013', '9', '16470.5600', '8159.7600', '16271.9200', '40902.2400', null, null, '53');

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    okay, that works, but you do realize that all the other strings in your INSERT statements are intended for numeric columns, right? they shouldn't have quotes either, but luckily (perhaps not the adverb many people would use), mysql is quite forgiving

    anyhow, now we are at the point of calculating a moving average

    what do you think would be the best way to calculate which rows should go into the calculation?

    in other words, how do you define the range of rows for the sum?

    HINT: DO NOT USE THE AUTO_INCREMENT
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    SitePoint Evangelist Miguel61's Avatar
    Join Date
    Mar 2008
    Posts
    402
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    mysql is quite forgiving
    Right ...

    Quote Originally Posted by r937 View Post
    HINT: DO NOT USE THE AUTO_INCREMENT
    Okay

    anyhow, now we are at the point of calculating a moving average

    what do you think would be the best way to calculate which rows should go into the calculation?

    in other words, how do you define the range of rows for the sum?
    this is what I don't understand... how to iterate through the rows back in a mysql table ?

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Miguel61 View Post
    ... how to iterate through the rows back in a mysql table ?
    here's a hint -- within the last 12 months

    WHERE some_date_diff BETWEEN 0 AND 11
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  13. #13
    SitePoint Evangelist Miguel61's Avatar
    Join Date
    Mar 2008
    Posts
    402
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    here's a hint -- within the last 12 months

    WHERE some_date_diff BETWEEN 0 AND 11
    thanks, you mean this?
    Code:
    mysql> SELECT
    	AVG(DISTINCT F_SUM)
    FROM
    	`page1-1`
    WHERE
    	MONTHS BETWEEN 0
    AND 11;
    +---------------------+
    | AVG(DISTINCT F_SUM) |
    +---------------------+
    | 149022.83596939     |
    +---------------------+
    1 row in set

  14. #14
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Miguel61 View Post
    thanks, you mean this?
    nope

    let me try to explain it this way...

    pick any row in your table... go ahead, pick one in your mind, a specific year and month

    okay, now let's say i'm trying to calculate the 12 month moving average for year 2010 month 09... how do i know whether the row you are thinking of should be included in the 12 months?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  15. #15
    SitePoint Evangelist Miguel61's Avatar
    Join Date
    Mar 2008
    Posts
    402
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    nope

    let me try to explain it this way...

    pick any row in your table... go ahead, pick one in your mind, a specific year and month

    okay, now let's say i'm trying to calculate the 12 month moving average for year 2010 month 09... how do i know whether the row you are thinking of should be included in the 12 months?
    Okey, for calculate the 12 month moving average for year 2010 month 09 I need iterate the rows ...
    Code:
    2009	10
    2009	11
    2009	12
    2010	01
    2010	02
    2010	03
    2010	04
    2010	05
    2010	06
    2010	07
    2010	08
    2010	09

  16. #16
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    correct

    now, think about how you would compare the year and month of those rows in a WHERE clause

    hint: it's a self-join
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  17. #17
    SitePoint Evangelist Miguel61's Avatar
    Join Date
    Mar 2008
    Posts
    402
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    correct

    now, think about how you would compare the year and month of those rows in a WHERE clause

    hint: it's a self-join
    Sorry I don't understand ....

  18. #18
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    you need to compare a given row (let's say 2010 09) with all the other rows in the table, and keep only those rows where the year and month is within the preceeding 12 months of 2010 09, and use those 12 rows to compute the average for 2010 09

    to compare rows like that, you need a self-join -- google it

    and let me give you some advice: if you cannot write the self-join, then perhaps you should consider pulling the entire table into your application (php or whatever) and doing that 12 month row comparison there

    and if you can't do that either, then perhaps you should hire someone (hint: not me)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  19. #19
    SitePoint Evangelist Miguel61's Avatar
    Join Date
    Mar 2008
    Posts
    402
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    you need to compare a given row (let's say 2010 09) with all the other rows in the table, and keep only those rows where the year and month is within the preceeding 12 months of 2010 09, and use those 12 rows to compute the average for 2010 09

    to compare rows like that, you need a self-join -- google it

    and let me give you some advice: if you cannot write the self-join, then perhaps you should consider pulling the entire table into your application (php or whatever) and doing that 12 month row comparison there

    and if you can't do that either, then perhaps you should hire someone (hint: not me)
    thank you, I tried this solution and worked:
    Code:
    CREATE TABLE foo SELECT
    	ID,
    	F1,
    	F2,
    	F3,
    	F_SUM,
    	@sum1 := @sum1 + F_SUM AS sum1,
    	var
    FROM
    	`page1-1`
    JOIN (SELECT @sum1 := 0) init;
    
    UPDATE `page1-1` t1
    JOIN (
    	SELECT
    		b.ID,
    		b.f1,
    		b.f2,
    		b.f3,
    		ROUND(b.F_SUM, 2) AS FSUM,
    		ROUND((b.sum1 - a.sum1) / 12, 2) AS MA,
    		b.var
    	FROM
    		foo a
    	JOIN foo b ON a.ID = b.ID - 12
    ) AS t2 ON t1.ID = t2.ID
    SET t1.AVERAGE_12_MONTHS = t2.MA;

  20. #20
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    that's nice

    i notice you used a.ID = b.ID - 12

    this is problematic unless (1) your id values are synchronized to year and month, and (2) no year/month is missing

    also, please double check your updated values for the first 11 rows in the table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  21. #21
    SitePoint Evangelist Miguel61's Avatar
    Join Date
    Mar 2008
    Posts
    402
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    Okey, I understand ... what your suggestion?

  22. #22
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Miguel61 View Post
    what your suggestion?
    instead of comparing id values, compare year and month values
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  23. #23
    SitePoint Evangelist Miguel61's Avatar
    Join Date
    Mar 2008
    Posts
    402
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    instead of comparing id values, compare year and month values
    compare year and month values with what values?

  24. #24
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Miguel61 View Post
    compare year and month values with what values?
    see post #15
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •