merging table using date field if common and add new row if date field is not common

Question

I am developing house app.I retrieve data using query for expense and income from different table name as expenses and incomes respectively. If I use expense only then it would print and if i try to mix income value too it give me problem.

Code:

 $expenses = $expense->dailyExpense();
 $income = $income->dailyIncome();
 return response()->json(['data' => ['expenses' => $expenses , 'income' => $income] , 'msg' => 'Daily Expense']);

the query portion for income is:

    public function dailyIncome()
    {

        return $this->makeModel()
            ->select(DB::raw("sum(cost) as income"),"date")
            ->groupBy('date')
            ->get();
    }

the query portion for income is:

 public function dailyExpense()
    {

        return $this->makeModel()
            ->select(DB::raw("sum(cost) as cost") , "date" , DB::raw("dayname(date) as calendar"))
            ->groupBy('date')
            ->get();
    }

Client portion:

 $scope.genereateReport = function () {

    $scope.choices = ['Daily', 'Monthly', 'Yearly'];
    $scope.$watch('selection', function (newVal, oldVal) {
        switch (newVal) {
            case 'Daily':

                $scope.url = $scope.base_path + 'dailyExpenses';
                $http.get($scope.url).success(function (response) {
                    $scope.expenses = response.data.expenses;
                    $scope.income = response.data.income;
                    $scope.totalExpenses = response.data.totalExpenses;


                });

                $scope.displayedCollection = [].concat($scope.expenses,$scope.income);
                console.log("collection:" + $scope.displayedCollection);
                $scope.totalExpenses = [].concat($scope.$totalExpenses);
                // $scope.income = [].concat($scope.income);
                //

                $scope.itemsByPage = 10;
                break;
 }
);
};

View Portion:

 <tr>
                                <th class="table-header-check">S.N</th>
                                <th st-sort="date" class="table-header-repeat line-left minwidth-1">Date</th>
                                <th st-sort="date" class="table-header-repeat line-left minwidth-1">Calandar</th>
                                <th st-sort="cost" class="table-header-repeat line-left minwidth-1">
                                    Expense
                                </th> <th st-sort="cost" class="table-header-repeat line-left minwidth-1">
                                    Income
                                </th>

                            </tr>


                            </thead>
                            <tbody>

                            <tr data-ng-repeat="row in displayedCollection track by $index" >
                                <td><% $index+1 %></td>
                                <td><%row.date%></td>
                                <td><%row.calendar%></td>
                                <td><%row.cost%></td>

                                <td><%income['row.date']%></td>


                            </tr>
                            <tr>
                                <td colspan="4">
                                   Total Expense: <%totalExpenses%>
                                </td>


                            </tr>

Result of above query in json form is:

View is like this without displaying income:

Desired output is like this..

enter image description here ]3

Ashok Adhikari Ashok Adhikari · Accepted Answer · 2016-07-27T15:04:48

You can get your expected result by using sql union and join. Instead creating two separate functions, you can create one function and use raw query like below :

select e.date,e.cost,'0' as income
from test.expenses as e
where e.date not in (select i.date from test.incomes as i)
union all
select e.date, e.cost as cost, i.income as income
from test.expenses as e
inner join test.incomes as i on e.date = i.date 
union all
select i.date,'0' as cost,i.income as income
from test.incomes as i
where i.date not in (select e.date from test.expenses as e);

Here I created two tables named expenses and incomes with the given fields in the the table you specified above.And I use the query mentioned above and result is as follows.

I hope this is what you expected.

merging table using date field if common and add new row if date field is not common

1 Answers