CODEWITHSUNDEEP
phpmysql
Usman Khan
Usman Khan

Reputation: 359

if data of current year not available show previous year

I have created a query to show data of present year and the rows in which data of current year is not present show previous year data but now the problem is that the companies which have present year data available is showing previous year data as well

Database company_annual_data

 +--id--+---company_id----+----year-----+----number_of_shares_issued----+
+   1  +     20          +    2011     +              425              +
+   2  +     21          +    2011     +              425              +
+   3  +     22          +    2011     +              425              +
+   4  +     23          +    2011     +              425              +
+   5  +     21          +    2012     +              425              +
+   6  +     20          +    2012     +              425              +
+   7  +     23          +    2012     +              425              +
+   8  +     20          +    2013     +              425              +

Companies 

+--id--+---company_name----+----category-----+
+  20  +     Company 1     +       2         +
+  21  +     Company 2     +       2         +
+  22  +     Company 3     +       2         +
+  23  +     Company 4     +       5         +

This is my query

$sql            = mysqli_query($connect, "SELECT * FROM companies WHERE category = '36'");
    $total_count    = mysqli_num_rows($sql);
    $companies = array();


    while($row = mysqli_fetch_assoc($sql)) {
            $companies[] = $row['id'];
    }

    $ids = join(', ', $companies);

    $sql1         = mysqli_query($connect, "SELECT year FROM companies_annual_data WHERE company_id IN ($ids) ORDER BY year DESC LIMIT 0, $total_count");
    $total_count1 = mysqli_num_rows($sql1);
    $year     = array();

    $prev_val = null;       
    while($total_year = mysqli_fetch_assoc($sql1)) {
        if($total_year['year'] != $prev_val) {
            $year[] =  $total_year['year'];
        }
        $prev_val = $total_year['year'];    
    }

    $years      = join(', ', $year);
    $sql2       = mysqli_query($connect, "SELECT DISTINCT number_of_shares_issued, year, company_id FROM companies_annual_data WHERE company_id IN ($ids) AND year IN ($years)");
    $prev_val_y = null;
    while($share_num = mysqli_fetch_assoc($sql2)) {
        if($share_num['year'] != date('Y')) {
            echo "Number Of Shares : " . $share_num['number_of_shares_issued'];
            echo " Year : " . $share_num['year'];
            echo " Company Id : " . $share_num['company_id'];
            echo "<br />";
        }
    }

This is the out put

Number Of Shares : 284125000 Year : 2015 Company Id : 348
Number Of Shares : 100000000 Year : 2015 Company Id : 351
Number Of Shares : 152098344 Year : 2015 Company Id : 356
Number Of Shares : 152098334 Year : 2016 Company Id : 356
Number Of Shares : 283500000 Year : 2015 Company Id : 366
Number Of Shares : 283500000 Year : 2016 Company Id : 366

That is wrong the output should be like this 

Number Of Shares : 284125000 Year : 2015 Company Id : 348
Number Of Shares : 100000000 Year : 2015 Company Id : 351
Number Of Shares : 152098334 Year : 2016 Company Id : 356
Number Of Shares : 283500000 Year : 2016 Company Id : 366

Can anyone help me out with this please

Upvotes: 0

Views: 309

Answers (1)

xQbert
xQbert

Reputation: 35333

If I understand the question...

This will return the most recent year's data on record for each company provided category is 36

SELECT CAD.num_of_Shares_Issued, CAD.`Year`, CAD.Company_ID
FROM Companies C
INNER JOIN (SELECT max(`year`) MYear, Company_ID 
            FROM company_Annual_data 
            GROUP BY Company_ID) SUB
 on SUB.CompanyID = C.Company_ID
LEFT JOIN Company_Annual_data CAD
 on C.ID = CAD.Company_ID
and SUB.MYear = CAD.`year`
WHERE  C.Category = 36

Upvotes: 1

Related Questions