how to select single record from each year of last ten year ?

[amith_ami]

Hi all, I am trying to select single record from each year of last ten year. Given below is the code what I am using now. But its taking too much time to get the result. Is there any way to make it fast?

$sql_cond ="SELECT Close_Price FROM (SELECT Close_price FROM cash_data WHERE SERIES='".$series."' AND SYMBOL='".$symbol."'";

  $sql_gain_data="SELECT 
(".$sql_cond." AND date_added <= '".$year_1."' ORDER BY date_added DESC LIMIT 0,1) as year1) as clyear1,
(".$sql_cond." AND date_added <= '".$year_2."' ORDER BY date_added DESC LIMIT 0,1) as year2) as clyear2,
(".$sql_cond." AND date_added <= '".$year_3."' ORDER BY date_added DESC LIMIT 0,1) as year3) as clyear3,
(".$sql_cond." AND date_added <= '".$year_4."' ORDER BY date_added DESC LIMIT 0,1) as year4) as clyear4,
(".$sql_cond." AND date_added <= '".$year_5."' ORDER BY date_added DESC LIMIT 0,1) as year5) as clyear5,
(".$sql_cond." AND date_added <= '".$year_6."' ORDER BY date_added DESC LIMIT 0,1) as year6) as clyear6,
(".$sql_cond." AND date_added <= '".$year_7."' ORDER BY date_added DESC LIMIT 0,1) as year7) as clyear7,
(".$sql_cond." AND date_added <= '".$year_8."' ORDER BY date_added DESC LIMIT 0,1) as year8) as clyear8,
(".$sql_cond." AND date_added <= '".$year_9."' ORDER BY date_added DESC LIMIT 0,1) as year9) as clyear9,
(".$sql_cond." AND date_added <= '".$year_10."' ORDER BY date_added DESC LIMIT 0,1) as year10) as clyear10";

Thanks.

[urtrivedi]

Here I am using direct year value instead of date in your $year_x variable. You must try to fetch query from database at once. You are firing same query every time for each year, so It is causing time delay.

$year_1=2005;
$year_2=2006;
$year_3=2007;
$year_4=2008;
$year_5=2009;
$year_6=2010;
$year_7=2011;
$year_8=2012;
$year_9=2013;
$year_10=2014;

$sql_gain_data="select series,symbol

,max(if(b.year='".$year_1."',close_price,null)) clyear1
,max(if(b.year='".$year_2."',close_price,null)) clyear2
,max(if(b.year='".$year_3."',close_price,null)) clyear3
,max(if(b.year='".$year_4."',close_price,null)) clyear4
,max(if(b.year='".$year_5."',close_price,null)) clyear5
,max(if(b.year='".$year_6."',close_price,null)) clyear6
,max(if(b.year='".$year_7."',close_price,null)) clyear7
,max(if(b.year='".$year_7."',close_price,null)) clyear8
,max(if(b.year='".$year_9."',close_price,null)) clyear9
,max(if(b.year='".$year_10."',close_price,null)) clyear10

from cash_data a 

inner join (
select series,symbol
,date_format(date_added,'%Y') year, max(date_added) close_date 
from cash_data 
WHERE SERIES='".$series."' AND SYMBOL='".$symbol."'"
group by  series,symbol,date_format(date_added,'%Y') 
) b on b.close_date=a.date_added and a.series=b.series and a.symbol=b.symbol

group by series,symbol";

[amith_ami]

@urtrivedi thanks for your reply. But i don't understand it well. Given below is the full code.

    $date = date("Y-m-d",strtotime($_POST["add_date"]));
    $add_limit = $_POST["add_limit"]; // choosen limit

$sql_cs = "SELECT * FROM (SELECT * FROM cash_data WHERE SERIES='EQ' AND SYMBOL!='' AND date_added= '$date') AS CSDAT  ORDER BY Close_Price DESC  LIMIT $add_limit,100"; // to get each cash data as single

$sql_cs_dat =mysql_query($sql_cs );
if(mysql_num_rows($sql_cs_dat)>0){

while($row_cs_dat = mysql_fetch_array($sql_cs_dat)){ 
$symbol=$row_cs_dat["SYMBOL"];
$series =$row_cs_dat["SERIES"];
$current_close = $row_cs_dat["Close_Price"];

//to find exist data
         $cond_exist = "WHERE SERIES='$series' AND SYMBOL='$symbol' AND date_added ='$date'";
         $sql_exist=$objA->select_rows(TABLEPREFIX.'gain_loss_analysis','count(*) as nm',$cond_exist);

         $row_exist = mysql_fetch_array($sql_exist);


$latest_date = date("Y-m-d",strtotime($row_cs_dat["date_added"]));
$month_1 = date('Y-m-d',strtotime($latest_date."-1 month"));//month
$month_3 = date('Y-m-d',strtotime($latest_date."-3 month"));
$month_6 = date('Y-m-d',strtotime($latest_date."-6 month"));
$month_9 = date('Y-m-d',strtotime($latest_date."-9 month"));
$week_1 = date('Y-m-d',strtotime($latest_date."-1 week"));//week
$week_2 = date('Y-m-d',strtotime($latest_date."-2 week"));
$year_1 = date('Y-m-d',strtotime($latest_date."-1 year"));//year
$year_2 = date('Y-m-d',strtotime($latest_date."-2 year"));
$year_3 = date('Y-m-d',strtotime($latest_date."-3 year"));
$year_4 = date('Y-m-d',strtotime($latest_date."-4 year"));
$year_5 = date('Y-m-d',strtotime($latest_date."-5 year"));
$year_6 = date('Y-m-d',strtotime($latest_date."-6 year"));
$year_7 = date('Y-m-d',strtotime($latest_date."-7 year"));
$year_8 = date('Y-m-d',strtotime($latest_date."-8 year"));
$year_9 = date('Y-m-d',strtotime($latest_date."-9 year"));
$year_10 = date('Y-m-d',strtotime($latest_date."-10 year"));


$sql_cond ="SELECT Close_Price FROM (SELECT Close_price FROM cash_data WHERE SERIES='".$series."' AND SYMBOL='".$symbol."'";

 $sql_gain_data="SELECT 
(".$sql_cond." AND date_added < '".$latest_date."' ORDER BY date_added DESC LIMIT 0,1) as day1) as clday1,
(".$sql_cond." AND date_added <= '".$month_1."' ORDER BY date_added DESC LIMIT 0,1) as month1) as clmonth1,
(".$sql_cond." AND date_added <= '".$month_3."' ORDER BY date_added DESC LIMIT 0,1) as month3) as clmonth3,
(".$sql_cond." AND date_added <= '".$month_6."' ORDER BY date_added DESC LIMIT 0,1) as month6) as clmonth6,
(".$sql_cond." AND date_added <= '".$month_9."' ORDER BY date_added DESC LIMIT 0,1) as month9) as clmonth9,
(".$sql_cond." AND date_added <= '".$week_1."' ORDER BY date_added DESC LIMIT 0,1) as week1) as clweek1,
(".$sql_cond." AND date_added <= '".$week_2."' ORDER BY date_added DESC LIMIT 0,1) as week2) as clweek2,
(".$sql_cond." AND date_added <= '".$year_1."' ORDER BY date_added DESC LIMIT 0,1) as year1) as clyear1,
(".$sql_cond." AND date_added <= '".$year_2."' ORDER BY date_added DESC LIMIT 0,1) as year2) as clyear2,
(".$sql_cond." AND date_added <= '".$year_3."' ORDER BY date_added DESC LIMIT 0,1) as year3) as clyear3,
(".$sql_cond." AND date_added <= '".$year_4."' ORDER BY date_added DESC LIMIT 0,1) as year4) as clyear4,
(".$sql_cond." AND date_added <= '".$year_5."' ORDER BY date_added DESC LIMIT 0,1) as year5) as clyear5,
(".$sql_cond." AND date_added <= '".$year_6."' ORDER BY date_added DESC LIMIT 0,1) as year6) as clyear6,
(".$sql_cond." AND date_added <= '".$year_7."' ORDER BY date_added DESC LIMIT 0,1) as year7) as clyear7,
(".$sql_cond." AND date_added <= '".$year_8."' ORDER BY date_added DESC LIMIT 0,1) as year8) as clyear8,
(".$sql_cond." AND date_added <= '".$year_9."' ORDER BY date_added DESC LIMIT 0,1) as year9) as clyear9,
(".$sql_cond." AND date_added <= '".$year_10."' ORDER BY date_added DESC LIMIT 0,1) as year10) as clyear10";

$sql_query_data = mysql_query($sql_gain_data);
$row_gain_data = mysql_fetch_array($sql_query_data);
$cl_day1 = $row_gain_data["clday1"];
$cl_month1 = $row_gain_data["clmonth1"];
$cl_month3 =$row_gain_data["clmonth3"];
$cl_month6 =$row_gain_data["clmonth6"];
$cl_month9 =$row_gain_data["clmonth9"];
$cl_week1 =$row_gain_data["clweek1"];
$cl_week2 =$row_gain_data["clweek2"];
$cl_year1 =$row_gain_data["clyear1"];
$cl_year2 =$row_gain_data["clyear2"];
$cl_year3 =$row_gain_data["clyear3"];
$cl_year4 =$row_gain_data["clyear4"];
$cl_year5 =$row_gain_data["clyear5"];
$cl_year6 =$row_gain_data["clyear6"];
$cl_year7 =$row_gain_data["clyear7"];
$cl_year8 =$row_gain_data["clyear8"];
$cl_year9 =$row_gain_data["clyear9"];
$cl_year10 =$row_gain_data["clyear10"];

below is the screenshot of result. But i want to make this query faster.

[diafol]

Ridiculously convoluted. WHy all the redeclarations? e.g.

`$series =$row_cs_dat["SERIES"];`

but you're already filtering on SERIES = 'EQ'

You seem to be running 100 iterations of the loop. There seems to be so much repetition here. A better idea would be to give us the schema and some sample data (SQL dump) and a visual of which bits of data come from where in your final html table.

[amith_ami]

@ diafol, given below is the sample data and there is ten year data for each symbol in the cash_data table.

[diafol]

Ok, some help - was hoping for a SQL dump, so we could fiddle with it.
Is each record you want in the final HTML table from a different SYMBOL (company)? Or can rows be from the same SYMBOL?

[amith_ami]

I want different symbol in the final html. Below is the html screenshot.

[diafol]

Ok, third time of asking - can you supply a SQL dump of the table (an *.sql file) so we can work on it? Without this, it's difficult to proceed.

[amith_ami]

hi diafol, i am extremely sorry for the delay . Please check the sample data given below.

-- phpMyAdmin SQL Dump
-- version 3.3.9
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Dec 10, 2015 at 11:03 AM
-- Server version: 5.5.8
-- PHP Version: 5.3.5

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

--
-- Database: `stockers`
--

-- --------------------------------------------------------

--
-- Table structure for table `cash_data`
--

CREATE TABLE IF NOT EXISTS `cash_data` (
  `Cash_id` int(11) NOT NULL AUTO_INCREMENT,
  `MKT` varchar(10) NOT NULL,
  `SERIES` varchar(10) NOT NULL,
  `SYMBOL` varchar(50) NOT NULL,
  `SECURITY` varchar(100) NOT NULL,
  `PREV_CL_PR` decimal(18,2) NOT NULL,
  `Open_Price` decimal(18,2) NOT NULL,
  `High_Price` decimal(18,2) NOT NULL,
  `Low_Price` decimal(18,2) NOT NULL,
  `Close_Price` decimal(18,2) NOT NULL,
  `NET_TRDVAL` decimal(18,2) NOT NULL,
  `NET_TRDQTY` int(11) NOT NULL,
  `IND_SEC` varchar(10) NOT NULL,
  `CORP_IND` varchar(10) NOT NULL,
  `TRADES` int(11) NOT NULL,
  `HI_52_WK` decimal(18,2) NOT NULL,
  `LO_52_WK` decimal(18,2) NOT NULL,
  `date_added` datetime NOT NULL,
  `file_name` varchar(150) NOT NULL,
  PRIMARY KEY (`Cash_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=572386 ;

--
-- Dumping data for table `cash_data`
--

INSERT INTO `cash_data` (`Cash_id`, `MKT`, `SERIES`, `SYMBOL`, `SECURITY`, `PREV_CL_PR`, `Open_Price`, `High_Price`, `Low_Price`, `Close_Price`, `NET_TRDVAL`, `NET_TRDQTY`, `IND_SEC`, `CORP_IND`, `TRADES`, `HI_52_WK`, `LO_52_WK`, `date_added`, `file_name`) VALUES(538888, 'N', 'EQ', 'AARTIIND', 'AARTI INDUSTRIES LTD', '513.50', '493.50', '521.00', '485.00', '516.95', '19099468.60', 37287, 'N', 'XD', 2409, '587.00', '247.00', '2015-11-09 00:00:00', '09-11-2015-Pd091115.csv'),(538889, 'N', 'EQ', 'AARVEEDEN', 'AARVEE DEN. & EXP. LTD', '50.95', '50.65', '52.30', '46.05', '51.60', '382272.10', 7497, 'N', ' ', 102, '79.95', '38.00', '2015-11-09 00:00:00', '09-11-2015-Pd091115.csv'),(538890, 'N', 'EQ', 'ABAN', 'ABAN OFFSHORE LTD.', '218.70', '214.00', '218.75', '200.00', '214.40', '188232835.50', 880090, 'N', ' ', 14008, '654.80', '200.00', '2015-11-09 00:00:00', '09-11-2015-Pd091115.csv'),(538891, 'N', 'EQ', 'ABB', 'ABB INDIA LIMITED', '1241.55', '1231.00', '1254.90', '1201.85', '1248.75', '25882351.50', 21054, 'N', ' ', 2428, '1526.30', '1018.60', '2015-11-09 00:00:00', '09-11-2015-Pd091115.csv'),(538892, 'N', 'EQ', 'ABBOTINDIA', 'ABBOTT INDIA LIMITED', '5337.20', '5196.70', '5360.00', '5085.00', '5128.15', '34596793.10', 6646, 'N', ' ', 1249, '6177.70', '3155.00', '2015-11-09 00:00:00', '09-11-2015-Pd091115.csv'),(538893, 'N', 'EQ', 'ABCIL', 'ADITYA BIRLA CHEM (I) LTD', '224.40', '222.55', '224.50', '222.00', '224.05', '688261.55', 3090, 'N', ' ', 56, '266.20', '195.00', '2015-11-09 00:00:00', '09-11-2015-Pd091115.csv'),(538894, 'N', 'EQ', 'ABGSHIP', 'ABG SHIPYARD LTD', '92.95', '90.00', '93.85', '87.60', '91.85', '30351486.15', 331707, 'N', ' ', 3757, '260.00', '87.60', '2015-11-09 00:00:00', '09-11-2015-Pd091115.csv'),(538895, 'N', 'EQ', 'ABIRLANUVO', 'ADITYA BIRLA NUVO LIMITED', '2047.80', '2011.00', '2029.90', '1957.05', '1998.30', '609756442.00', 306229, 'N', ' ', 16124, '2344.85', '1516.00', '2015-11-09 00:00:00', '09-11-2015-Pd091115.csv'),(538896, 'N', 'EQ', 'ACCELYA', 'ACCELYA KALE SOLUTION LTD', '916.10', '905.60', '925.05', '905.55', '921.05', '2972569.05', 3244, 'N', ' ', 265, '1229.80', '775.00', '2015-11-09 00:00:00', '09-11-2015-Pd091115.csv'),(538897, 'N', 'EQ', 'ACE', 'ACTION CONST EQUIP LTD', '43.30', '41.90', '44.25', '38.50', '43.60', '9167756.10', 215513, 'N', ' ', 1311, '55.40', '31.10', '2015-11-09 00:00:00', '09-11-2015-Pd091115.csv'),(538898, 'N', 'EQ', 'ACROPETAL', 'ACROPETAL TECH LTD', '2.80', '2.70', '2.80', '2.60', '2.75', '30238.55', 11147, 'N', ' ', 28, '4.75', '2.10', '2015-11-09 00:00:00', '09-11-2015-Pd091115.csv'),(538899, 'N', 'EQ', 'ADANIENT', 'ADANI ENTERPRISES LIMITED', '79.05', '75.70', '80.30', '75.10', '79.45', '331775294.40', 4257169, 'N', ' ', 18917, '803.95', '65.85', '2015-11-09 00:00:00', '09-11-2015-Pd091115.csv'),(538900, 'N', 'EQ', 'ADANIPOWER', 'ADANI POWER LTD', '28.75', '26.25', '27.95', '26.20', '27.40', '326117740.80', 11963298, 'N', ' ', 18156, '60.15', '19.60', '2015-11-09 00:00:00', '09-11-2015-Pd091115.csv'),(538901, 'N', 'EQ', 'ADANITRANS', 'ADANI TRANSMISSION LTD', '34.35', '33.00', '35.70', '31.60', '35.15', '65821287.05', 1932610, 'N', ' ', 5564, '42.80', '25.00', '2015-11-09 00:00:00', '09-11-2015-Pd091115.csv'),(538902, 'N', 'EQ', 'ADFFOODS', 'ADF FOODS LIMITED', '88.00', '87.25', '90.00', '85.15', '89.65', '5052130.95', 56921, 'N', ' ', 871, '103.95', '51.80', '2015-11-09 00:00:00', '09-11-2015-Pd091115.csv'),(538903, 'N', 'EQ', 'ADHUNIK', 'ADHUNIK METALIKS LTD.', '14.65', '14.70', '15.20', '14.40', '14.65', '718601.15', 49013, 'N', ' ', 504, '38.20', '14.05', '2015-11-09 00:00:00', '09-11-2015-Pd091115.csv'),(538904, 'N', 'EQ', 'ADI', 'ADI FINECHEM LIMITED', '226.35', '220.00', '230.00', '215.20', '227.40', '2716832.65', 11994, 'N', ' ', 196, '308.90', '178.05', '2015-11-09 00:00:00', '09-11-2015-Pd091115.csv'),(538905, 'N', 'EQ', 'ADLABS', 'ADLABS ENTERTAINMENT LTD.', '106.95', '105.00', '106.00', '100.25', '104.85', '6201277.35', 59284, 'N', ' ', 4581, '207.00', '100.25', '2015-11-09 00:00:00', '09-11-2015-Pd091115.csv'),(538906, 'N', 'EQ', 'ADORWELD', 'ADOR WELDING LTD', '247.95', '246.95', '268.00', '244.00', '263.10', '3071749.65', 12160, 'N', ' ', 613, '324.90', '182.20', '2015-11-09 00:00:00', '09-11-2015-Pd091115.csv'),(538907, 'N', 'EQ', 'ADSL', 'ALLIED DIGITAL SERV. LTD', '34.25', '33.90', '39.35', '32.45', '37.95', '12567046.55', 336404, 'N', ' ', 2733, '43.90', '15.20', '2015-11-09 00:00:00', '09-11-2015-Pd091115.csv'),(538908, 'N', 'EQ', 'ADVANIHOTR', 'ADVANI HOT.& RES.(I) LTD', '45.40', '52.50', '52.50', '46.00', '49.80', '333517.95', 6720, 'N', ' ', 46, '76.90', '40.25', '2015-11-09 00:00:00', '09-11-2015-Pd091115.csv'),(538909, 'N', 'EQ', 'ADVANTA', 'ADVANTA LIMITED', '507.45', '502.10', '511.85', '477.65', '508.95', '2994852.40', 5935, 'N', ' ', 393, '634.95', '324.00', '2015-11-09 00:00:00', '09-11-2015-Pd091115.csv'),(538910, 'N', 'EQ', 'AEGISCHEM', 'AEGIS LOGISTICS LIMITED', '92.85', '90.00', '97.00', '87.50', '94.20', '32283397.55', 344510, 'N', ' ', 4077, '919.90', '78.60', '2015-11-09 00:00:00', '09-11-2015-Pd091115.csv'),(538911, 'N', 'EQ', 'AFL', 'ACCEL FRONTLINE LTD', '72.15', '70.75', '72.50', '69.00', '71.40', '1624508.20', 22920, 'N', ' ', 348, '130.40', '58.05', '2015-11-09 00:00:00', '09-11-2015-Pd091115.csv'),(538912, 'N', 'EQ', 'AGARIND', 'AGARWAL INDS CORP LTD.', '145.70', '164.80', '164.80', '142.00', '146.25', '5679856.80', 38976, 'N', ' ', 316, '164.80', '86.50', '2015-11-09 00:00:00', '09-11-2015-Pd091115.csv'),(538913, 'N', 'EQ', 'AGCNET', 'AGC NETWORKS LIMITED', '85.95', '87.00', '88.20', '82.70', '88.00', '390400.95', 4501, 'N', ' ', 104, '157.50', '71.65', '2015-11-09 00:00:00', '09-11-2015-Pd091115.csv'),(538914, 'N', 'EQ', 'AGRODUTCH', 'AGRO DUTCH INDUSTRIES LTD', '3.55', '3.50', '3.85', '3.00', '3.80', '109612.75', 29031, 'N', ' ', 44, '7.40', '2.15', '2015-11-09 00:00:00', '09-11-2015-Pd091115.csv'),
(538915, 'N', 'SM', 'AHIMSA', 'AHIMSA INDUSTRIES LTD.', '25.10', '25.00', '25.00', '25.00', '25.00', '150000.00', 6000, 'N', ' ', 1, '26.00', '25.00', '2015-11-09 00:00:00', '09-11-2015-Pd091115.csv'),(538916, 'N', 'EQ', 'AHLEAST', 'ASIAN HOTELS (EAST) LTD', '142.60', '145.25', '145.25', '140.15', '140.15', '2809.20', 20, 'N', ' ', 4, '217.00', '135.10', '2015-11-09 00:00:00', '09-11-2015-Pd091115.csv'),(538917, 'N', 'EQ', 'AHLUCONT', 'AHLUWALIA CONT IND LTD', '235.80', '234.00', '243.35', '231.40', '239.10', '1551343.75', 6500, 'N', ' ', 459, '297.00', '148.95', '2015-11-09 00:00:00', '09-11-2015-Pd091115.csv'),(538918, 'N', 'EQ', 'AHLWEST', 'ASIAN HOTELS (WEST) LTD', '121.00', '121.00', '121.00', '121.00', '121.00', '50215.00', 415, 'N', ' ', 2, '144.00', '100.10', '2015-11-09 00:00:00', '09-11-2015-Pd091115.csv'),(538919, 'N', 'EQ', 'AIAENG', 'AIA ENGINEERING LIMITED', '928.70', '918.00', '923.95', '900.00', '919.95', '80154747.35', 87235, 'N', ' ', 1055, '1364.20', '890.80', '2015-11-09 00:00:00', '09-11-2015-Pd091115.csv'),(538920, 'N', 'EQ', 'AIL', 'ALSTOM INDIA LIMITED', '611.30', '593.80', '618.00', '584.00', '597.15', '33078478.85', 55574, 'N', ' ', 3211, '876.95', '520.55', '2015-11-09 00:00:00', '09-11-2015-Pd091115.csv'),(538921, 'N', 'EQ', 'AJANTPHARM', 'AJANTA PHARMA LIMITED', '1462.95', '1440.00', '1460.00', '1405.80', '1439.35', '199563399.90', 138519, 'N', ' ', 11308, '3460.00', '1088.00', '2015-11-09 00:00:00', '09-11-2015-Pd091115.csv'),(538922, 'N', 'EQ', 'AJMERA', 'AJMERA REALTY & INF I LTD', '118.90', '118.45', '126.05', '114.85', '123.60', '6641474.95', 54128, 'N', ' ', 1538, '192.00', '82.10', '2015-11-09 00:00:00', '09-11-2015-Pd091115.csv'),(538923, 'N', 'EQ', 'AKSHOPTFBR', 'AKSH OPTIFIBRE LIMITED', '17.25', '16.50', '17.90', '16.05', '17.65', '22919302.40', 1333171, 'N', ' ', 3551, '23.80', '12.20', '2015-11-09 00:00:00', '09-11-2015-Pd091115.csv'),(538924, 'N', 'EQ', 'AKZOINDIA', 'AKZO NOBEL INDIA LIMITED', '1320.50', '1312.00', '1339.00', '1301.00', '1331.60', '3718359.80', 2799, 'N', ' ', 332, '1551.00', '1212.90', '2015-11-09 00:00:00', '09-11-2015-Pd091115.csv'),(538925, 'N', 'EQ', 'ALANKIT', 'ALANKIT LIMITED', '51.65', '51.10', '51.65', '49.90', '50.55', '20425693.65', 406129, 'N', ' ', 623, '231.45', '40.00', '2015-11-09 00:00:00', '09-11-2015-Pd091115.csv'),(538926, 'N', 'EQ', 'ALBK', 'ALLAHABAD BANK', '74.50', '72.00', '79.45', '72.00', '78.20', '408098198.80', 5267257, 'N', ' ', 20335, '136.70', '72.00', '2015-11-09 00:00:00', '09-11-2015-Pd091115.csv')

thanks in advance.

[diafol]

Ok, think I've got it work. However, your dump only had records for a single date. No biggie - I can change data. Your screenshot suggests that you are not using raw data in the columns after current. Is this data a simple +/- from the current amount? If so, which way does it work?
e.g. is Day1 = current - day1 or is it Day1 = day1 - current or is it something completely different?

[amith_ami]

@ diafol, actually i need to take Close_price of a SYMBOL and SERIES from previous ten years of data with respect to the current date of calculation.
for ex.
SYMBOL = ACC and SERIES=EQ and date_added =2015-12-11
Today's Close_price = 1500.00
previous working day Close_price = 1475.00
1 year(2014-12-11) before Close_price = 1350.00
In case there is no data on 2014-12-11 it should take data from the next working day's Close_price and it may be 2014-12-13or 2014-12-14 like this I need 2,3,4.......10 year's Close_price.
Then I need to find the difference

    ie. //1 year difference calculation
    Difference  = Today's Close_price - Close_price(1 year) 
    Percentage Difference = (Difference / Close_price(1 year) )*100  

    Difference = 1500.00 - 1350.00 = 150.00
    Percentage Difference = (150.00/1350.00)*100 = 11.11

I hope this is enough.

Thanks

[diafol]

OK will take a little while and I have other things to do - prob. get back to you in a few hours.

[diafol]

OK - here's a simple class I wrote:

fauxpivot.php

<?php

/**
 * Class FauxPivot
 */
class FauxPivot
{
    //DATA properties
    private $mainSelect = "(SELECT `date_added`, `SYMBOL`, `Close_Price` FROM `cash_data`
                    WHERE `SERIES` = '%s' AND `SYMBOL` != '' AND `date_added` = ?
                    GROUP BY `SYMBOL`
                    ORDER BY `SYMBOL`
                    LIMIT %d, %d) AS %s";

    private $joinSelect = "SELECT `SYMBOL`, `Close_Price` FROM `cash_data`
                    WHERE `SERIES` = '%s' AND `date_added` <= ?
                    ORDER BY date_added
                    LIMIT 1";

    private $currentAlias = 'P0D';
    private $fieldListArray = ["`%s`.`SYMBOL`, `%s`.`Close_Price` AS %sc"];

    //OUTPUT properties
    private $fieldListString;
    private $joinString;

    private $bindings = [];

    /**
     * FauxPivot constructor.
     * @param date $startDate
     * @param array $offsetData array of datetime intervals and types
     * @param string $series
     * @param int $limitOffset
     * @param int $limitTotal
     */
    public function __construct($startDate, $offsetData, $series='EQ', $limitOffset=0, $limitTotal=100)
    {
        $this->mainSelect = sprintf($this->mainSelect, $series, $limitOffset, $limitTotal, $this->currentAlias);
        $this->joinSelect = sprintf($this->joinSelect, $series);
        $this->fieldListArray[0] = sprintf($this->fieldListArray[0], $this->currentAlias, $this->currentAlias, $this->currentAlias);
        $this->bindings[] = $startDate;
        $this->fieldListString = implode(', ', array_merge($this->fieldListArray, $this->createFieldList($offsetData)));
        $dateArray = $this->makeDates($startDate, $offsetData);
        $this->joinString = $this->createJoinString($dateArray);
    }

    /**
     * @param array $offsetData array of datetime intervals and types
     * @return array data to add to $fieldListArray and to create the string $fieldListString
     */
    private function createFieldList($offsetData)
    {
        $addToArray = [];
        foreach($offsetData as $alias=>$type)
            $addToArray[] = ($type == 'sub') ? $this->asSubtraction($alias) : $this->asPercent($alias);
        return $addToArray;
    }

    /**
     * @param string $alias simply the datetime interval
     * @return string the aliased calculated field for difference
     */
    private function asSubtraction($alias)
    {
        return "(`$alias`.`Close_Price` - `{$this->currentAlias}`.`Close_Price`) AS {$alias}c";
    }

    /**
     * @param string $alias simply the datetime interval
     * @return string the aliased calculated field for percentage difference
     */
    private function asPercent($alias)
    {
        return "((`$alias`.`Close_Price` - `{$this->currentAlias}`.`Close_Price`)/`$alias`.`Close_Price`)*100 AS {$alias}c";
    }

    /**
     * @param $startDate
     * @param $offsetData array of datetime intervals and types
     * @return array of interval=>date
     */
    private function makeDates($startDate,$offsetData)
    {
        $dt = new DateTime($startDate);
        $dateArray = [];
        foreach ($offsetData as $iv=>$type) {
            $clone = clone $dt;
            $dateArray[$iv] = $clone->sub(new DateInterval($iv))->format('Y-m-d');
        }
        return $dateArray;
    }

    /**
     * @param array $dateArray array of interval=>date
     * @return string complete SQL clauses of JOINs
     */
    private function createJoinString($dateArray)
    {
        $joinArray = [];
        foreach($dateArray as $iv=>$date)
        {
            $joinArray[] = "LEFT JOIN (" . $this->joinSelect . ") AS $iv ON `{$this->currentAlias}`.`SYMBOL` = `$iv`.`SYMBOL`\n";
            $this->bindings[] = $date;
        }
        return implode(' ', $joinArray);
    }

    /**
     * @return string output complete SQL statement ready to run
     */
    public function createSQL()
    {
        return 'SELECT ' . $this->fieldListString . ' FROM ' . $this->mainSelect . "\n" . $this->joinString . "ORDER BY SYMBOL";
    }

    /**
     * @return array ready to place into PDO execute() method
     */
    public function getBindings()
    {
        return $this->bindings;
    }
}

//HELPER functions for creating HTML table
function makeHeaders($mapCols)
{
    $output = '';
    foreach($mapCols as $mp) $output .= "\n<th>$mp</th>";
    return "<tr>$output</tr>";
}

function makeBody($data)
{
    $output = '';
    foreach($data as $d)
    {
        $output .= "\n<tr>";
        foreach($d as $item) $output .= "\n\t<td>$item</td>";
        $output .= "\n</tr>";
    }
    return $output;
}

You can use this with the following type of data:

$offsetData = [
    'P1D'=>'sub',
    'P7D'=>'sub',
    'P14D'=>'sub',
    'P1M'=>'sub',
    'P3M'=>'sub',
    'P6M'=>'sub',
    'P9M'=>'sub',
    'P1Y'=>'pc',
    'P2Y'=>'pc',
    'P3Y'=>'pc',
    'P4Y'=>'pc',
    'P5Y'=>'pc',
    'P6Y'=>'pc',
    'P7Y'=>'pc',
    'P8Y'=>'pc',
    'P9Y'=>'pc',
    'P10Y'=>'pc'
];

$mapCols = [
    'Company',
    'Current Close',
    'Day 1',
    'Week 1',
    'Week 2',
    'Month 1',
    'Month 3',
    'Month 6',
    'Month 9',
    'Year 1 %',
    'Year 2 %',
    'Year 3 %',
    'Year 4 %',
    'Year 5 %',
    'Year 6 %',
    'Year 7 %',
    'Year 8 %',
    'Year 9 %',
    'Year 10 %'
];


$date = '2015-11-09';

$pivot = new FauxPivot($date, $offsetData);
$dbh = new PDO('mysql:host=localhost;dbname=dwtest','root','');
$stmt = $dbh->prepare($pivot->createSQL());
$stmt->execute($pivot->getBindings());
$data = $stmt->fetchAll(PDO::FETCH_ASSOC);

Simply place into your HTML:

<table>
    <thead>
    <?=makeHeaders($mapCols)?>
    </thead>
    <tbody>
    <?=makeBody($data)?>
    </tbody>
</table>

I couldn't test this thoroughly due to incomplete data for years, months ago etc. Anyhow, it may give you a start.

[amith_ami]

@ diafol, thanks for your kind help, I will try this and let you know.