How to sum calculated years from two columns group by id?

44
April 16, 2019, at 04:50 AM

I need to create a searching in the staff list according to some criteria selected. I am using ajax in laravel 5. One of the searching criteria is the number of years working in a state.

I have queried the data and success using this sql in controller:

 $query = DB::table('itemregistrations')
                ->join('sections', 'itemregistrations.SectionID', '=', 'sections.SectionID')
                ->join('categories', 'itemregistrations.CategoryID', '=', 'categories.CategoryID')
                ->join('operasi', 'itemregistrations.OperasiID', '=', 'operasi.OperasiID')
                ->join('negeri', 'itemregistrations.NegeriID', '=', 'negeri.NegeriID')
                ->join('gred', 'itemregistrations.GredID', '=', 'gred.GredID')
                ->where('itemregistrations.statusProID', '=', 1)
                ->select('itemregistrations.name','sections.sectionname', 'categories.categoryname', 'operasi.operasiname', 'itemregistrations.Nobadan', 'itemregistrations.lahir_yy', 'itemregistrations.yy', 'gred.namagred', 'itemregistrations.itemRegistrationID', 'itemregistrationpangkat.yeartamatkhidmat', 'itemregistrationpangkat.yearmulakhidmat');
        if($request->input('negeri_lahir') != ''){
            $query->where('itemregistrations.NegeriID', $request->input('negeri_lahir'));
        }
        if($request->input('kategori') != '') {
            $query->where('itemregistrations.CategoryID', $request->input('kategori'));
        }
        if($request->input('pangkat') != '') {
            $query->where('itemregistrations.OperasiID', $request->input('pangkat'));
        }
        if(request('umur')) {
            $query->whereRaw('YEAR(CURDATE()) - lahir_yy >= ?', [request('umur')]);  
        }
        if($request->input('gred') != '') {
            $query->where('itemregistrations.GredID', $request->input('gred'));
        }
        if(request('tempoh')) {
            $query->whereRaw('YEAR(CURDATE()) - pdrm_yy >= ?', [request('tempoh')]);  
        }
        if($request->input('negeri_perkhidmatan') != '') {
            $query->join('itemregistrationpangkat', 'itemregistrationpangkat.itemRegistrationID', '=', 'itemregistrations.itemRegistrationID')
                ->where('itemregistrationpangkat.NegeriID', $request->input('negeri_perkhidmatan'));               
        }
        if(request('tempoh_negeri')) {
            $query->whereRaw('yeartamatkhidmat - yearmulakhidmat >= ?', [request('tempoh_negeri')]);
        }
        $newitem = $query->get();
        return response::json($newitem);

The results will be displayed using data table. This is the console log data of some searching example(searching negeri K with 1-year working experience):

  0:
  Nobadan: "A0291"
  categoryname: "PEGAWAI"
  itemRegistrationID: 791
  lahir_yy: 1970
  namagred: "Tiada rekod"
  name: "SALIM BIN YAACOB"
  operasiname: "A"
  y: 1987
  sectionname: "x1"
  yearmulakhidmat: "2009"
  yeartamatkhidmat: "2011"
  __proto__: Object
  1:
  Nobadan: "A0291"
  categoryname: "PEGAWAI"
  itemRegistrationID: 791
  lahir_yy: 1970
  namagred: "Tiada rekod"
  name: "SALIM BIN YAACOB"
  operasiname: "A"
  yy: 1987
  sectionname: "x1"
  yearmulakhidmat: "2012"
  yeartamatkhidmat: "2013"

I need to get the sum of the 2 query results..that is 1 year + 2 years would be 3 years. I want to display only one Salim Yaacob which sum the total years of experience in the same state.

I tried to modify the sql like this:

  $query = DB::table('itemregistrations')
                ->join('sections', 'itemregistrations.SectionID', '=', 'sections.SectionID')
                ->join('categories', 'itemregistrations.CategoryID', '=', 'categories.CategoryID')
                ->join('operasi', 'itemregistrations.OperasiID', '=', 'operasi.OperasiID')
                ->join('negeri', 'itemregistrations.NegeriID', '=', 'negeri.NegeriID')
                ->join('gred', 'itemregistrations.GredID', '=', 'gred.GredID')
                ->where('itemregistrations.statusProID', '=', 1)
                ->select('itemregistrations.name','sections.sectionname', 'categories.categoryname', 'operasi.operasiname', 'itemregistrations.Nobadan', 'itemregistrations.lahir_yy', 'itemregistrations.pdrm_yy', 'gred.namagred', 'itemregistrations.itemRegistrationID');
        if($request->input('negeri_lahir') != ''){
            $query->where('itemregistrations.NegeriID', $request->input('negeri_lahir'));
        }
        if($request->input('kategori') != '') {
            $query->where('itemregistrations.CategoryID', $request->input('kategori'));
        }
        if($request->input('pangkat') != '') {
            $query->where('itemregistrations.OperasiID', $request->input('pangkat'));
        }
        if(request('umur')) {
            $query->whereRaw('YEAR(CURDATE()) - lahir_yy >= ?', [request('umur')]);  
        }
        if($request->input('gred') != '') {
            $query->where('itemregistrations.GredID', $request->input('gred'));
        }
        if(request('tempoh')) {
            $query->whereRaw('YEAR(CURDATE()) - pdrm_yy >= ?', [request('tempoh')]);  
        }
        if($request->input('negeri_perkhidmatan') != '') {
            $query->join('itemregistrationpangkat', 'itemregistrationpangkat.itemRegistrationID', '=', 'itemregistrations.itemRegistrationID')
                ->where('itemregistrationpangkat.NegeriID', $request->input('negeri_perkhidmatan'));               
        }
        if(request('tempoh_negeri')) {
            $query->whereRaw('sum(yeartamatkhidmat - yearmulakhidmat) >= ?', [request('tempoh_negeri')])
                  ->groupBy('itemregistrationpangkat.ItemRegistrationID');  
        }
        $newitem = $query->get();
        return response::json($newitem);

But the results failed with 500 error.

Hope you can help, thanks.

READ ALSO
How can I INNER JOIN these 3 tables correctly?

How can I INNER JOIN these 3 tables correctly?

I have 3 tablesOrders, copacks, copack_products

56
which hash function to choose for indexing uuid string on mysql? md5,crc64 or fnv

which hash function to choose for indexing uuid string on mysql? md5,crc64 or fnv

I have a column store the uuid stringI add a new column to store its integer(64bits) hash value for indexing

26
MySQL Error seems unfixable: Index column size too large

MySQL Error seems unfixable: Index column size too large

I'm getting: ERROR 1709 (HY000): Index column size too largeThe maximum column size is 767 bytes

38
How do I pass my url params to async functions in javascript files separate from the route with Node.js?

How do I pass my url params to async functions in javascript files separate from the route with Node.js?

I'm just learning to code and I'm building an application for my businessI'm having an issue with passing url parameters on my show route

42