Senthilkumar

Dear Barand, I want to update each category column total aslo to my databse. How to update the total. My code is <form method="POST" action=""> <div style="display: inline-block;width:50%"> <div class="form-group" style="width:100%;margin-top:10px"> <label class="control-label col-sm-6" for="Dealer_Name">Dealer Name:</label> <div class="col-sm-8"> <input type="text" class="form-control Dealer_Name" id="Dealer_Name" placeholder="Search Dealer Name" name="Dealer_Name" Required style="font-size:14px !important"/> <input type="hidden" class="form-control" id="Dealer_Name1" placeholder="Search Dealer Name" name="Dealer_Name1" /> </div> <div style="text-align:center;margin-top:20px"> <button type="button" name="View" id="View" class="View" value="View" onclick="getLastYearData()" style="width:auto;margin-top:25px;background: #0A2558; color:white;font-family:Cambria;border-radius:5px;height:30px;padding:0 5px 0 5px;display:none">View Submitted Data </button> </div> </div> </div> <script> $( function() { $( "#Dealer_Name" ).autocomplete({ source: 'Auto_Complete_User.php', select: function (event, ui) { $("#Dealer_Name1").val(ui.item.id);//Put Id in a hidden field } }); }); </script> <div id="wrap" class="datalist-wrapper" style="margin-top:-25px"> <div class="" id="dataContainer"> <?php // generate month labels foreach(range(1,12) as $m) { $mon[] = ucfirst(date('M',strtotime("2023-$m-01"))); } ?> <table style="margin-top:40px; height:15px" id="example" cellpadding="0" cellspacing="0" border="0" class="datatable table-sm table-hover table-striped table-bordered"> <thead> <tr> <?php $Year = date("y"); $previousYear = $Year - 1; ?> <th style="text-align:center; font-weight:bold;width:2%">S.No</th> <th style="text-align:center; font-weight:bold;width:8%">Category</th> <th style="text-align:center; font-weight:bold;width:8%">Model</th> <?php foreach($mon as $m) {?> <th style="text-align:center; font-weight:bold;width:4.5%"> <?=$m.$previousYear?> </th> <?php } ?> <th style="text-align:center; font-weight:bold;width:5%">Total</th> <th style="text-align:center; font-weight:bold;width:7%">MS %</th> </tr> </thead> <?php $qry=mysqli_query($conn,"SELECT * FROM model WHERE Brand_ID = '1' order by Category_Name ASC"); $n = 1; // remember last category, to detect when it changes $last_cat = ''; while($row = $qry->fetch_assoc()) { $Category_Name = $row['Category_Name']; $Model_Name = $row['Model_Name']; // detect if the category changed if($last_cat !== $row['Category_Name']) { // test if not the first output if($last_cat !== '') { // close the previous section - output a total row ?> <tr> <td colspan="3" style="text-align:center"> <?PHP echo $last_cat; ?> TOTAL </td> <?php foreach($mon as $m) {?> <td> <input type="number" class="form-control col_Total<?=$m?>" name="col_Total_<?=$m?>[]" style="width:100%;font-size:14px;text-align:center" readonly /> </td> <?php } ?> <td> <input type="number" name="cat_Total[]" class="form-control cat_Total" style="width:100%;font-size:14px;text-align:center" readonly /> </td> <td>&nbsp;</td> </tr> <?php } // remember the new category $last_cat = $row['Category_Name']; // start a new section ?> <tbody> <?php } ?> <tr> <td align='center'> <?=$n++?> </td> <td style="text-align:center"> <?=$row['Category_Name']?> </td> <td hidden> <input type="text" name="Category_Name[]" id="Category_Name" value="<?php echo $Category_Name; ?>" style="width:100px;font-size:14px;text-align:center" readonly /> </td> <td style="text-align:center"> <?=$row['Model_Name']?> </td> <td hidden> <input type="text" name="Model_Name[]" id="Model_Name" value="<?php echo $Model_Name; ?>" style="width:100px;font-size:14px;text-align:center" readonly /> </td> <?php foreach($mon as $m) {?> <td> <input type="number" class="form-control calc <?=$m?>" name="<?=$m?>[]" style="width:100%;font-size:14px;text-align:center" /> </td> <?php } ?> <td> <input type="number" name="Total[]" class="form-control Total" style="width:100%;font-size:14px;text-align:center" readonly /> </td> <td> <input type="number" name="MS[]" class="form-control MS" style="width:100%;font-size:14px;text-align:center" readonly /> </td> <td hidden align='center'> <input type="checkbox" checked="checked" class="checkbox" name='lang[]' value="1" /> </td> </tr> <?php } // end of data loop // if there was any output, close out the last section if($last_cat !== '') { // close the previous section - output a total row ?> <tr> <td hidden> <td colspan="3" style="text-align:center"> <?PHP echo $last_cat; ?> TOTAL </td> <?php foreach($mon as $m) {?> <td> <input type="number" class="form-control col_Total<?=$m?>" name="col_Total_<?=$m?>[]" style="width:100%;font-size:14px;text-align:center" readonly /> </td> <?php } ?> <td> <input type="number" name="cat_Total[]" class="form-control cat_Total" style="width:100%;font-size:14px;text-align:center" readonly /> </td> <td>&nbsp;</td> </tr> </tbody> <?php } ?> </table> </div> </div> <div style="text-align:center"> <input type="submit" name="addInvoice" class="addInvoice" id="addInvoice" value="Submit" style="width:100px;background: #0A2558;margin-top:10px; color:white;font-family:Cambria;border-radius:5px;height:30px;" /> </div> </form> <?php if(isset($_POST['addInvoice'])){ $Dealer_ID = $_POST['Dealer_Name1']; $dealname = mysqli_query($conn, "SELECT * from users WHERE Emp_No = '$Dealer_ID'"); $dealerrow = $dealname->fetch_assoc(); $Dealername = $dealerrow['Name']; $Status = 1; $Year = date("Y"); $previousYear = $Year - 1; foreach ($_POST['lang'] as $ID => $VAL) { $Category = $_POST['Category_Name'][$ID]; $Model= $_POST['Model_Name'][$ID]; $Jan = $_POST['Jan'][$ID]; $Feb = $_POST['Feb'][$ID]; $Mar = $_POST['Mar'][$ID]; $Apr = $_POST['Apr'][$ID]; $May = $_POST['May'][$ID]; $Jun = $_POST['Jun'][$ID]; $Jul = $_POST['Jul'][$ID]; $Aug = $_POST['Aug'][$ID]; $Sep = $_POST['Sep'][$ID]; $Oct = $_POST['Oct'][$ID]; $Nov = $_POST['Nov'][$ID]; $December= $_POST['Dec'][$ID]; $Total = $_POST['Total'][$ID]; $MS = $_POST['MS'][$ID]; //$query = "UPDATE users SET Branch ='$Branch', Posting_Location ='$Posted_Location', Department='$Department', Divison='$Division', Emp_No='$Emp_No', Name='$Name', Designation='$Designation', User_Type='$User_Type', Functional_Role='$Functional_Role', Employed='$Employed', Mobile='$Mobile', Email='$Email', DOJ='$DOJ', Qualifications='$Qualifications', DOB='$DOB', Approver1_ID='$Approver1_ID', Approver1_Name='$Approver1_Name', Approver2_ID='$Approver2_ID', Approver2_Name='$Approver2_Name' , Approver='$Approver', Gender='$Gender', Blood='$Blood' WHERE id = '$ida'"; $query = "INSERT INTO lastyeardata (Dealer_ID, Dealername, Category, Model, Year, Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, December, Total, MS,Status) VALUES ('$Dealer_ID','$Dealername', '$Category','$Model','$previousYear','$Jan', '$Feb','$Mar', '$Apr', '$May', '$Jun','$Jul', '$Aug', '$Sep','$Oct', '$Nov', '$December','$Total','$MS','$Status')"; $result = mysqli_query($conn, $query); if($result) { // echo "<script type='text/javascript'> document.location = 'Users.php'; </script>"; echo '<script type="text/javascript">Swal.fire({ text: "Last Year Data Details added Successfully", icon: "success", showCancelButton: false, confirmButtonColor: "#3085d6", confirmButtonText: "OK" }).then((result) => { if (result.isConfirmed) { window.location.href = "Last_Year_Data.php" } })</script>'; } else { //echo '<script type="text/javascript"> alert("Details are Not Updated.")</script>'; echo '<script type="text/javascript">Swal.fire({ text: "Details are Not Updated. Please check the details entered.", icon: "error", showCancelButton: false, confirmButtonColor: "#3085d6", confirmButtonText: "OK" }).then((result) => { if (result.isConfirmed) { window.history.back() } })</script>'; } } } ?> The calculated column total row is not updating on database. only the actual rows are updating. Please guide me how to update the column total row to databse

Dear Barand, Thanks for your immediate response. I changed the code as per your suggession. Noe it is working properly.

Deat Team, I want to sum the column values of last updated month from database. My query is select Category, sum(Total) as SUM from equipement_population_data where Dealer_ID = '$Emp_No' AND Month = ________ group by Category order by SUM DESC Example: If the month is found 2023-08, (that is previous month) then the query should complete like Month = '2023-08' . If last updated month is found 2023-07 then, then the query should complete like Month = '2023-07'. If last updated month is found 2023-06 then, then the query should complete like Month = '2023-06'. If last updated month is found 2023-01 then, then the query should complete like Month = '2023-01' My database table looks Can any one help me how to do this

can you pls guide me how to do this

Dear Barand, That is not spreadsheets. This is the sample of my database. My database is

correction When i am selecting the from month (2023-01) and to month (2023-03) , it should sum the column Jan+Feb+Mar where the year is 2023. When i am selecting from month (2023-05) and to month (2023-09) , it should sum the column May+Jun+Jul+Aug+Sep where the year is 2023 and When i am selecting from month (2023-11) and to month (2024-02) , it should sum the column Nov+Dec (2023) & Jan+Feb(2024) .

Dear Team, I am having two inputs (From month & To mont). I want to sum the column data basedon the month selection. My sql table looks like my code is $dealerid = $_POST["dealerid"]; $FromMonth=$_POST["fromMonth"]; $ToMonth = $_POST["toMonth"]; $ES_Model = $_POST["ES_Model"]; $countMonth = //here the month sould update like Jan+Feb+Mar... as per the selection if($FromMonth == "" && $ToMonth == ""){ $Month_Condition = ""; $Sum = "sum(total)"; }else{ $Month_Condition = "AND Month >= '$FromMonth' AND Month <= '$ToMonth'"; $Sum = "sum($countMonth)"; } if ($ES_Model == 'All') { $Modal_Cond = ''; } else { $Modal_Cond = "And Model ='$ES_Model'"; } $query = "SELECT category, $Sum as target FROM currentyeardata WHERE 1 $Modal_Cond GROUP BY category" $result = mysqli_query($conn, $query); I was struckup on $countMonth When i am selecting the from month (2023-01) and to month (2023-03) , it should sum the column Jan+Feb+Mar where the year is 2023. When i am selecting from month (2023-05) and to month (2023-09) , it should sum the column May+Jun+Jul+Aug+Sep where the year is 2023 and When i am selecting from month (2023-11) and to month (2024-02) , it should sum the column Nov+Dec (2023) & Jan+Feb(2024) . I want to output like bellow Can any one help me how to do this

Dear Barand, Thanks for your guid. I changed the code as per below Select distinct P.Number, P.Description,STRING_AGG( CAST(M.Name as nvarchar(MAX)),', ') as name from SubAssembly as SA inner join PartDrawing as PD on SA.PartDrawingID = PD.Id inner join CatalogPartDrawing as CPD on PD.Id = CPD.PartDrawingID inner join [Catalog] as C on C.ID = CPD.CatalogID inner join Model as M on M.ID = C.ModelID inner join Part as P on SA.ChildPartID = P.ID and P.Number in(80001619) GROUP BY P.Number, P.Description I am getting output now.

Dear Barand, Using the GROUP_CONCAT I am getting error Pls look on it

Dear team, I am filtering the data from my database. My query is Select distinct P.Number, P.Description, M.[Name] from SubAssembly as SA inner join PartDrawing as PD on SA.PartDrawingID = PD.Id inner join CatalogPartDrawing as CPD on PD.Id = CPD.PartDrawingID inner join [Catalog] as C on C.ID = CPD.CatalogID inner join Model as M on M.ID = C.ModelID inner join Part as P on SA.ChildPartID = P.ID and P.Number in(80001619) My outpiut is But i Want the output like belo image, Can any one pls correct me to get my required output.

how to pass another variable here

The above conditon is not understanding

Dear Barand, Thanks for your reply. It is working perfetly as per my requirement. Can you please explain this code for my better understand of this

Dear Barand, Pls reply

[{"TIV_Status":"Pending from Dealer","Marketingpromotion_Status":"Pending from Dealer","Revenue_Status":"Pending from 1st Approver","Equipementpopulation_Status":"Pending from 1st Approver","Employeeexpense_Status":"Pending from Dealer","Infrastructureexpense_Status":"Pending from Dealer","Otherexpense_Status":"Pending from Dealer","Month":"Feb-2023","TIV":"0","Marketingpromotion":"0","Revenue":"1","Equipementpopulation":"1","Employeeexpense":"0","Infrastructureexpense":"0","Otherexpense":"0"},{"TIV_Status":"Pending from 1st Approver","Marketingpromotion_Status":"Pending from 1st Approver","Revenue_Status":"Pending from 1st Approver","Equipementpopulation_Status":"Pending from 1st Approver","Employeeexpense_Status":"Pending from 1st Approver","Infrastructureexpense_Status":"Pending from 1st Approver","Otherexpense_Status":"Pending from 1st Approver","Month":"Apr-2023","TIV":"1","Marketingpromotion":"1","Revenue":"1","Equipementpopulation":"1","Employeeexpense":"1","Infrastructureexpense":"1","Otherexpense":"1"},{"TIV_Status":"Pending from 2nd Approver","Marketingpromotion_Status":"Pending from 1st Approver","Revenue_Status":"Pending from Dealer","Equipementpopulation_Status":"Pending from Dealer","Employeeexpense_Status":"Pending from Dealer","Infrastructureexpense_Status":"Pending from Dealer","Otherexpense_Status":"Pending from Dealer","Month":"May-2023","TIV":"2","Marketingpromotion":"1","Revenue":"0","Equipementpopulation":"0","Employeeexpense":"0","Infrastructureexpense":"0","Otherexpense":"0"},{"TIV_Status":"Approval Completed","Marketingpromotion_Status":"Pending from 1st Approver","Revenue_Status":"Pending from Dealer","Equipementpopulation_Status":"Pending from Dealer","Employeeexpense_Status":"Pending from 1st Approver","Infrastructureexpense_Status":"Pending from Dealer","Otherexpense_Status":"Pending from Dealer","Month":"Jun-2023","TIV":"3","Marketingpromotion":"1","Revenue":"0","Equipementpopulation":"0","Employeeexpense":"1","Infrastructureexpense":"0","Otherexpense":"0"}]

My MIS_Table.php code is <?php include("../connection.php"); $dealerid = $_GET["dealerid"]; $query = "select * from monthly_mis_status WHERE dealerid = '$dealerid' ORDER BY month ASC "; $result = mysqli_query($conn, $query); while($row = mysqli_fetch_array($result)){ $TIV= $row['TIV']; $Marketingpromotion= $row['Marketingpromotion']; $Revenue= $row['Revenue']; $Equipementpopulation= $row['Equipementpopulation']; $Employeeexpense= $row['Employeeexpense']; $Infrastructureexpense = $row['Infrastructureexpense']; $Otherexpense = $row['Otherexpense']; $Month = $row['month']; $Month_Format = date('M-Y', strtotime($Month)); if ($TIV == 0) { $TIVStatus = "Pending from Dealer"; } else if ($TIV ==1) { $TIVStatus = "Pending from 1st Approver"; } else if ($TIV == 2) { $TIVStatus = "Pending from 2nd Approver"; } else if ($TIV == 3) { $TIVStatus = "Approval Completed"; } if ($Marketingpromotion == 0) { $Marketingpromotionstatus = "Pending from Dealer"; } else if ($Marketingpromotion == 1) { $Marketingpromotionstatus = "Pending from 1st Approver"; } else if ($Marketingpromotion == 2) { $Marketingpromotionstatus = "Pending from 2nd Approver"; } else if ($Marketingpromotion == 3) { $Marketingpromotionstatus = "Approval Completed"; } if ($Equipementpopulation == 0) { $Equipementpopulationstatus = "Pending from Dealer"; } else if ($Equipementpopulation == 1) { $Equipementpopulationstatus = "Pending from 1st Approver"; } else if ($Equipementpopulation == 2) { $Equipementpopulationstatus = "Pending from 2nd Approver"; } else if ($Equipementpopulation == 3) { $Equipementpopulationstatus = "Approval Completed"; } if ($Employeeexpense == 0) { $Employeeexpenseststus = "Pending from Dealer"; } else if ($Employeeexpense == 1) { $Employeeexpenseststus = "Pending from 1st Approver"; } else if ($Employeeexpense == 2) { $Employeeexpenseststus = "Pending from 2nd Approver"; } else if ($Employeeexpense == 3) { $Employeeexpenseststus = "Approved Completed"; } if ($Revenue == 0) { $RevenueStatus = "Pending from Dealer"; } else if ($Revenue == 1) { $RevenueStatus = "Pending from 1st Approver"; } else if ($Revenue == 2) { $RevenueStatus = "Pending from 2nd Approver"; } else if ($Revenue == 3) { $RevenueStatus = "Approval Completed"; } if ($Infrastructureexpense == 0) { $Infrastructureexpenseststus = "Pending from Dealer"; } else if ($Infrastructureexpense == 1) { $Infrastructureexpenseststus = "Pending from 1st Approver"; } else if ($Infrastructureexpense == 2) { $Infrastructureexpenseststus = "Pending from 2nd Approver"; } else if ($Infrastructureexpense == 3) { $Infrastructureexpenseststus = "Approval Completed"; } if ($Otherexpense == 0) { $Otherexpenseststus = "Pending from Dealer"; } else if ($Otherexpense == 1) { $Otherexpenseststus = "Pending from 1st Approver"; } else if ($Otherexpense == 2) { $Otherexpenseststus = "Pending from 2nd Approver"; } else if ($Otherexpense == 3) { $Otherexpenseststus = "Approval Completed"; } $return_arr[] = array( "TIV_Status" => $TIVStatus, "Marketingpromotion_Status" => $Marketingpromotionstatus, "Revenue_Status" => $RevenueStatus, "Equipementpopulation_Status" => $Equipementpopulationstatus, "Employeeexpense_Status" => $Employeeexpenseststus, "Infrastructureexpense_Status" => $Infrastructureexpenseststus, "Otherexpense_Status" => $Otherexpenseststus, "Month" => $Month_Format, "TIV" => $TIV, "Marketingpromotion" => $Marketingpromotion, "Revenue" => $Revenue, "Equipementpopulation" => $Equipementpopulation, "Employeeexpense" => $Employeeexpense, "Infrastructureexpense" => $Infrastructureexpense, "Otherexpense" => $Otherexpense, ); } echo json_encode($return_arr);

Dear Team, I am appending the table using Ajax script on my php page. In my table i am having 7 columns (TIV, Marketingpromotion, Revenue, Equipementpopulation, Employeeexpense, Infrastructureexpense, Otherexpense). All these columns are updating from Mysql databse sucessfully. But i want to change the text colour based on the conditon. My table output is My condition is If the value is 0 it should display "Pending from Dealer" using class name of "pending", if value is 1 it shout display "Pending from 1st Approver" using class name of "progress", if value is 2 it shout display "Pending from 1st Approver" using class name of "progress" and if value is 3 it shout display "Pending from 1st Approver" using class name of "submited". My code is <script> $('.Filter').click(function () { var dealerid = document.getElementById("Dealer").value; var empno = <?PHP echo $Emp_No ?>; $.ajax({ type: 'get', dataType: 'JSON', url: 'MIS_Table.php', data: 'dealerid=' + dealerid + '&empno=' + empno , success: function (response) { if(response){ var len = response.length; $("#userTable tbody").empty(); for (var i = 0; i < len; i++) { var TIV_Status = response[i].TIV_Status; var Marketingpromotion_Status = response[i].Marketingpromotion_Status; var Revenue_Status = response[i].Revenue_Status; var Equipementpopulation_Status = response[i].Equipementpopulation_Status; var Employeeexpense_Status = response[i].Employeeexpense_Status; var Infrastructureexpense_Status = response[i].Infrastructureexpense_Status; var Otherexpense_Status = response[i].Otherexpense_Status; var Month = response[i].Month; var TIV = response[i].TIV; var Marketingpromotion = response[i].Marketingpromotion; var Revenue = response[i].Revenue; var Equipementpopulation = response[i].Equipementpopulation; var Employeeexpense = response[i].Employeeexpense; var Infrastructureexpense = response[i].Infrastructureexpense; var Otherexpense = response[i].Otherexpense; var tr_str = "<tr>" + "<td align='center'>" + (i + 1) + "</td>" + "<td align='center'>" + Month + "</td>" + "<td>" + TIV_Status + "</td>" + "<td>" + Marketingpromotion_Status + "</td>" + "<td>" + Revenue_Status + "</td>" + "<td>" + Equipementpopulation_Status + "</td>" + "<td>" + Employeeexpense_Status + "</td>" + "<td>" + Infrastructureexpense_Status + "</td>" + "<td>" + Otherexpense_Status + "</td>" + "</tr>"; $("#userTable tbody").append(tr_str); TIV == '0' ? document.querySelectorAll("td")[2].classList.add('pending') : document.querySelectorAll("td")[2].classList.add('submited'); Marketingpromotion == '0' ? document.querySelectorAll("td")[3].classList.add('pending') : document.querySelectorAll("td")[3].classList.add('submited'); Revenue == '0' ? document.querySelectorAll("td")[4].classList.add('pending') : document.querySelectorAll("td")[4].classList.add('submited'); Equipementpopulation == '0' ? document.querySelectorAll("td")[5].classList.add('pending') : document.querySelectorAll("td")[5].classList.add('submited'); Employeeexpense == '0' ? document.querySelectorAll("td")[6].classList.add('pending') : document.querySelectorAll("td")[6].classList.add('submited'); Infrastructureexpense == '0' ? document.querySelectorAll("td")[7].classList.add('pending') : document.querySelectorAll("td")[7].classList.add('submited'); Otherexpense == '0' ? document.querySelectorAll("td")[8].classList.add('pending') : document.querySelectorAll("td")[8].classList.add('submited'); } } } }); }); </script> Witht the above code the color is changing only on the first row. Not for all the row. And i dont know how to apply all the four conditions. Can any onr please help me to solve this issue

Dear Barand, Thanks for your support. it is working ap per my requirement

Dear Team, I am having Mysql databse table in the name of fieldtable. My table looks bellow image I want to fetch the data from this table where the value is 1 and display on my PHP table row wise How cani display the data like above picture. Can any one help me this

Dear Barand, Thanks for your suggession. It is working now. My final query is SELECT mnth.Month, Revanue, EmployeeExpense, InfraExpense, OtherExpense FROM ( select id, concat(YEAR(CURDATE()), '-', month) as Month from dbms.month ) mnth LEFT JOIN ( select Month, sum(Revanue) as Revanue from dbms.revenue_data where dealerid = '81019218' AND Status = '1' group by Month ) revenue USING(Month) LEFT JOIN ( select Month, sum(Total) as EmployeeExpense from dbms.employeeexpense where dealerid = '81019218' AND Status = '1' group by Month ) EmployeeExpense USING(Month) LEFT JOIN ( select Month, sum(Total) as InfraExpense from dbms.infrastructure_expense where dealerid = '81019218' AND Status = '1' group by Month ) InfraExpense USING(Month) LEFT JOIN ( select Month, sum(Total) as OtherExpense from dbms.otherexpense where dealerid = '81019218' AND Status = '1' group by Month ) OtherExpense USING(Month) ORDER BY mnth.id; And table is

It will store the month only

Dear Barand, Pls suggest me

InfraExpense Table Revenue Table Employee Expense Table Other Expense

I enter the month directly on the table by my self for checking the condition

I didnt noticed that. Now it is working properly SELECT mnth.Month, Revanue, EmployeeExpense, InfraExpense, OtherExpense FROM ( select id, Month from dbms.month ) mnth LEFT JOIN ( select Month, sum(Revanue) as Revanue from dbms.revenue_data where dealerid = '81019218' AND Status = '1' group by Month ) revenue USING(Month) LEFT JOIN ( select Month, sum(Total) as EmployeeExpense from dbms.employeeexpense where dealerid = '81019218' AND Status = '1' group by Month ) EmployeeExpense USING(Month) LEFT JOIN ( select Month, sum(Total) as InfraExpense from dbms.infrastructure_expense where dealerid = '81019218' AND Status = '1' group by Month ) InfraExpense USING(Month) LEFT JOIN ( select Month, sum(Total) as OtherExpense from dbms.otherexpense where dealerid = '81019218' AND Status = '1' group by Month ) OtherExpense USING(Month) ORDER BY mnth.id; I need one suggession from you. In month table we pre defined the year. How to do this for next year. Is ther any other way to capure the current year on this table