I have problem sql query displays empty id in tablegrid

agusecc · 2024-11-15 09:00:38

agusecc
Member
Offline

Registered: 2015-12-19
Posts: 126

Topic: I have problem sql query displays empty id in tablegrid

anyone can help me?
I have problem sql query displays empty id in tablegrid on my aplcation when i search between datetimepicker1 and datetimepicker2 at Laporan Absensi

there are my script

select distinct 
(select employees.id from employees where employees.id = attendancedays.id_employees AND attendancedays.date >= {datetimepicker1} and  attendancedays.date <= {datetimepicker2} ) as  id,                                 

(select  trim(
ifnull(firstname||' ','')||       
ifnull(lastname,'')
)     from employees where employees.id = attendancedays.id_employees AND attendancedays.date >= {datetimepicker1} and  attendancedays.date <= {datetimepicker2} ) as Nama,                                 
(select  jabatan from jabatan where jabatan.id = employees.id_jabatan AND attendancedays.date >= {datetimepicker1} and  attendancedays.date <= {datetimepicker2}  ) as Jabatan,
(select  unitkerja from unitkerja where unitkerja.id = employees.id_unitkerja AND attendancedays.date >= {datetimepicker1} and  attendancedays.date <= {datetimepicker2}  ) as UnitKerja,
(select  romawi from unitkerja where unitkerja.id = employees.id_unitkerja AND attendancedays.date >= {datetimepicker1} and  attendancedays.date <= {datetimepicker2}  ) as romawi,
(select  harikerja from attendancedays where employees.id = attendancedays.id_employees AND attendancedays.date >= {datetimepicker1} and  attendancedays.date <= {datetimepicker2}  ) as HariKerja,
(select sum (hadir) from attendancedays where employees.id = attendancedays.id_employees AND attendancedays.date >= {datetimepicker1} and  attendancedays.date <= {datetimepicker2}  ) as Hadir,
(select sum (alpha) from attendancedays where employees.id = attendancedays.id_employees AND attendancedays.date >= {datetimepicker1} and  attendancedays.date <= {datetimepicker2} ) as Alpha,
(select sum (sakit) from attendancedays where employees.id = attendancedays.id_employees AND attendancedays.date >= {datetimepicker1} and  attendancedays.date <= {datetimepicker2}  ) as sakit,
(select sum (ijincuti) from attendancedays where employees.id = attendancedays.id_employees AND attendancedays.date >= {datetimepicker1} and  attendancedays.date <= {datetimepicker2}   ) as Ijin_Cuti, 
(select sum (terlambat) from attendancedays where employees.id = attendancedays.id_employees AND attendancedays.date >= {datetimepicker1} and  attendancedays.date <= {datetimepicker2} ) as Terlambat,
(select sum (sanksiapel) from attendancedays where employees.id = attendancedays.id_employees AND attendancedays.date >= {datetimepicker1} and  attendancedays.date <= {datetimepicker2} ) as SanksiApel,
(select sum (lembur) from attendancedays where employees.id = attendancedays.id_employees AND attendancedays.date >= {datetimepicker1} and  attendancedays.date <= {datetimepicker2} ) as Lembur


from attendancedays
left outer join employees on attendancedays.id_employees = employees.id    
left outer join unitkerja on employees.id_unitkerja = unitkerja.id    

order by  unitkerja.id asc

this is the link app :
https://www.mediafire.com/file/0e1neg10 … 7.rar/file

Post's attachments

eror.png 48.02 kb, 8 downloads since 2024-11-15

sparrow · 2024-11-15 11:50:08

sparrow
Member
Offline

From: Рідна МАТИ, UKRAINE
Registered: 2018-06-02
Posts: 1,077

Re: I have problem sql query displays empty id in tablegrid

Hi,

Sorry, but queries are not written like that.
You need to get to know the SQL language better.
There is enough literature and sites to study on the Internet.

Here is your query.

SELECT distinct 
  employees.id as id,                       
  TRIM(ifnull(employees.firstname||' ','')||ifnull(employees.lastname,'')) as Nama,                                 
  jabatan.jabatan as Jabatan,
  unitkerja.unitkerja as UnitKerja,
  unitkerja.romawi as romawi,
  attendancedays.harikerja as HariKerja,
  (SELECT SUM(hadir) FROM attendancedays a WHERE a.id_employees = attendancedays.id_employees) as Hadir,
  (SELECT SUM(alpha) FROM attendancedays a WHERE a.id_employees = attendancedays.id_employees) as Alpha,
  (SELECT SUM(sakit) FROM attendancedays a WHERE a.id_employees = attendancedays.id_employees) as sakit,
  (SELECT SUM(ijincuti) FROM attendancedays a WHERE a.id_employees = attendancedays.id_employees) as Ijin_Cuti, 
  (SELECT SUM(terlambat) FROM attendancedays a WHERE a.id_employees = attendancedays.id_employees) as Terlambat,
  (SELECT SUM(sanksiapel) FROM attendancedays a WHERE a.id_employees = attendancedays.id_employees) as SanksiApel,
  (SELECT SUM(lembur) FROM attendancedays a WHERE a.id_employees = attendancedays.id_employees) as Lembur
FROM attendancedays
    LEFT JOIN employees on attendancedays.id_employees = employees.id    
    LEFT JOIN unitkerja on employees.id_unitkerja = unitkerja.id
    LEFT JOIN jabatan on jabatan.id = employees.id_jabatan 
WHERE attendancedays.date >= {datetimepicker1} 
    AND attendancedays.date <= {datetimepicker2}
ORDER BY unitkerja.id asc

agusecc · 2024-11-16 03:16:47

agusecc
Member
Offline

Registered: 2015-12-19
Posts: 126

Re: I have problem sql query displays empty id in tablegrid

sparrow, thank you for your attention.
my project done well. I learned a lot from you and MVD friends.

agusecc · 2024-11-17 00:12:12

agusecc
Member
Offline

Registered: 2015-12-19
Posts: 126

Re: I have problem sql query displays empty id in tablegrid

Hai Sparrow when i use there script

SELECT distinct 
  employees.id as id,                       
  TRIM(ifnull(employees.firstname||' ','')||ifnull(employees.lastname,'')) as Nama,                                 
  jabatan.jabatan as Jabatan,
  unitkerja.unitkerja as UnitKerja,
  unitkerja.romawi as romawi,
  attendancedays.harikerja as HariKerja,
  (SELECT SUM(hadir) FROM attendancedays a WHERE a.id_employees = attendancedays.id_employees) as Hadir,
  (SELECT SUM(alpha) FROM attendancedays a WHERE a.id_employees = attendancedays.id_employees) as Alpha,
  (SELECT SUM(sakit) FROM attendancedays a WHERE a.id_employees = attendancedays.id_employees) as sakit,
  (SELECT SUM(ijincuti) FROM attendancedays a WHERE a.id_employees = attendancedays.id_employees) as Ijin_Cuti, 
  (SELECT SUM(terlambat) FROM attendancedays a WHERE a.id_employees = attendancedays.id_employees) as Terlambat,
  (SELECT SUM(sanksiapel) FROM attendancedays a WHERE a.id_employees = attendancedays.id_employees) as SanksiApel,
  (SELECT SUM(lembur) FROM attendancedays a WHERE a.id_employees = attendancedays.id_employees) as Lembur
FROM attendancedays
    LEFT JOIN employees on attendancedays.id_employees = employees.id    
    LEFT JOIN unitkerja on employees.id_unitkerja = unitkerja.id
    LEFT JOIN jabatan on jabatan.id = employees.id_jabatan 
WHERE attendancedays.date >= {datetimepicker1} 
    AND attendancedays.date <= {datetimepicker2}
ORDER BY unitkerja.id asc]

the function sum results do not match.
but when I use this function the results match the input data

SELECT distinct                       
  TRIM(ifnull(employees.firstname||' ','')||ifnull(employees.lastname,'')) as Nama,                                 
  jabatan.jabatan as Jabatan,
  unitkerja.unitkerja as UnitKerja,
  attendancedays.harikerja as HariKerja, 
 (select sum (hadir) from attendancedays where employees.id = attendancedays.id_employees AND attendancedays.date >= {datetimepicker1} and  attendancedays.date <= {datetimepicker2}  ) as Hadir,
(select sum (alpha) from attendancedays where employees.id = attendancedays.id_employees AND attendancedays.date >= {datetimepicker1} and  attendancedays.date <= {datetimepicker2} ) as Alpha,
(select sum (sakit) from attendancedays where employees.id = attendancedays.id_employees AND attendancedays.date >= {datetimepicker1} and  attendancedays.date <= {datetimepicker2}  ) as sakit,
(select sum (ijincuti) from attendancedays where employees.id = attendancedays.id_employees AND attendancedays.date >= {datetimepicker1} and  attendancedays.date <= {datetimepicker2}   ) as Ijin_Cuti, 
(select sum (terlambat) from attendancedays where employees.id = attendancedays.id_employees AND attendancedays.date >= {datetimepicker1} and  attendancedays.date <= {datetimepicker2} ) as Terlambat,
(select sum (sanksiapel) from attendancedays where employees.id = attendancedays.id_employees AND attendancedays.date >= {datetimepicker1} and  attendancedays.date <= {datetimepicker2} ) as SanksiApel,
(select sum (lembur) from attendancedays where employees.id = attendancedays.id_employees AND attendancedays.date >= {datetimepicker1} and  attendancedays.date <= {datetimepicker2} ) as Lembur

FROM attendancedays
    LEFT JOIN employees on attendancedays.id_employees = employees.id    
    LEFT JOIN unitkerja on employees.id_unitkerja = unitkerja.id
    LEFT JOIN jabatan on jabatan.id = employees.id_jabatan  
WHERE attendancedays.date >= {datetimepicker1} 
    AND attendancedays.date <= {datetimepicker2}  
ORDER BY unitkerja.id asc

Is there any advice for me to improve the script? thank you so much.

sparrow · 2024-11-17 07:28:00

sparrow
Member
Offline

From: Рідна МАТИ, UKRAINE
Registered: 2018-06-02
Posts: 1,077

Re: I have problem sql query displays empty id in tablegrid

Yes, it's my mistake. You can use your query or my corrected version.

In any case, these are not final queries until you decide on the working days column.
When displaying the result, you can see either 21 days or 22 days or both at once for one employee, depending on the filter.

SELECT distinct                       
  TRIM(ifnull(employees.firstname||' ','')||ifnull(employees.lastname,'')) as Nama,                                 
  jabatan.jabatan as Jabatan,
  unitkerja.unitkerja as UnitKerja,
  a.harikerja as HariKerja,
  Hadir,
  Alpha,
  sakit,
  Ijin_Cuti, 
  Terlambat,
  SanksiApel,
  Lembur
FROM (SELECT id_employees, 
        harikerja, SUM(hadir) as Hadir, SUM(alpha) as Alpha, SUM(sakit) as sakit, SUM(ijincuti) as Ijin_Cuti, 
        SUM(terlambat) as Terlambat, SUM(sanksiapel) as SanksiApel, SUM(lembur) as Lembur 
      FROM attendancedays
      WHERE date(attendancedays.date) >= {datetimepicker1}  
        AND date(attendancedays.date) <= {datetimepicker2}  
      GROUP BY harikerja, id_employees) as a
   LEFT JOIN employees on a.id_employees = employees.id    
   LEFT JOIN unitkerja on employees.id_unitkerja = unitkerja.id
   LEFT JOIN jabatan on jabatan.id = employees.id_jabatan
ORDER BY unitkerja.id asc

agusecc · 2024-11-17 18:53:58

agusecc
Member
Offline

Registered: 2015-12-19
Posts: 126

Re: I have problem sql query displays empty id in tablegrid

Sparrow, I am thanks you for your sql query, but i try search startdate 26/09/2024 - 25/10/24 result 7 employees only should be 39 employees.
maybe you can try again?

Post's attachments

akurasi.png 139.1 kb, 6 downloads since 2024-11-17

sparrow · 2024-11-17 19:11:15

sparrow
Member
Offline

From: Рідна МАТИ, UKRAINE
Registered: 2018-06-02
Posts: 1,077

Re: I have problem sql query displays empty id in tablegrid

Replace in the query

      WHERE date(attendancedays.date) >= {datetimepicker1}  
        AND date(attendancedays.date) <= {datetimepicker2}

with

       WHERE date(attendancedays.date) >= date({datetimepicker1})  
        AND date(attendancedays.date) <= date({datetimepicker2})

agusecc · 2024-11-18 07:39:41

agusecc
Member
Offline

Registered: 2015-12-19
Posts: 126

Re: I have problem sql query displays empty id in tablegrid

Sparrow, I thank you so much.. there scrip perfect and run well.
You are excelent

agusecc · 2024-11-21 02:45:07

agusecc
Member
Offline

Registered: 2015-12-19
Posts: 126

Re: I have problem sql query displays empty id in tablegrid

Hi Sparrow, I'm having difficulty placing variables for the printout script.

Here are the variables I have:

('{EDIT10}') AS direktur,
('{EDIT11}') AS manajerkeuangan,
('{EDIT13}') AS kabagkeuangan,
('{EDIT14}') AS kabagumum,

('{EDIT2}') AS bulan,
('{EDIT5}') AS tahun,
('{EDIT7}') AS tanggal,
('{EDIT19}') AS ddmmyyyy,

if placed in the following script:

SELECT distinct 
"$autoinc",                         
  TRIM(ifnull(employees.firstname||' ','')||ifnull(employees.lastname,'')) as Nama,                                 
  jabatan.jabatan as Jabatan,
  pangkat.pangkat as Pangkat,                        
  unitkerja.unitkerja as UnitKerja,                                                
  statuskeluarga.statuskeluarga as Status_keluarga,
  statuspegawai.statuspegawai as Status_pegawai,
  gajipokok as GajiPokok,                         
  tarip as Tarip,            
  irja as IRJA,                                                   
  Tkj as TKJ,                                                            
  pajak as Pajak,
  khspajak as KhsPajak,                  
  Keluarga as Tunj_Keluarga,   
  Sandangpangan as SandangPangan,  
  
  kreditbank,
  kreditkoperasi,
  bpjsKerja,
  bpjsKesehatan,
  pensiun, 
  pemakaianair,
  Koperasi,  
                                                                  
  a.harikerja as HariKerja,
  Hadir,
  Alpha,      
  sakit,                                                                               
  IjinCuti, 
  Terlambat,
  SanksiDisiplin,
  Lembur,
  uanglembur,
  transport,              
  perusahaan,
  bruto,
  GajiKotor,
  potongan,
  TerimaBersih
  
FROM (SELECT id_employees,                                                       
        harikerja, SUM(hadir) as Hadir, SUM(alpha) as Alpha, SUM(sakit) as sakit, SUM(ijincuti) as IjinCuti, 
        SUM(terlambat) as Terlambat, (SUM(sanksiapel) + SUM(sanksiterlambat)) as SanksiDisiplin, SUM(lembur) as Lembur, SUM(uanglembur) as UangLembur, 
        SUM(transport)as Transport, SUM(perusahaan)as Perusahaan,   
        ((select gajipokok from employees where id = id_employees) + (select irja from employees where id = id_employees) + 
        (select tkj from employees where id = id_employees) + (select khspajak from employees where id = id_employees) + 
        (select khspajak from employees where id = id_employees) + (select keluarga from employees where id = id_employees) + 
        (select sandangpangan from employees where id = id_employees)+ SUM(uanglembur) + SUM(transport) + SUM(perusahaan) - 
        (select pajak from employees where id = id_employees)) as bruto,   
        
        ((select gajipokok from employees where id = id_employees) + (select irja from employees where id = id_employees) + 
        (select tkj from employees where id = id_employees) + (select khspajak from employees where id = id_employees) + 
        (select khspajak from employees where id = id_employees) + (select keluarga from employees where id = id_employees) + 
        (select sandangpangan from employees where id = id_employees)+ SUM(uanglembur) + SUM(transport) + SUM(perusahaan) ) as GajiKotor,
        
        ((select kreditbank from employees where id = id_employees) + (select kreditkoperasi from employees where id = id_employees) + 
        (select bpjsKerja from employees where id = id_employees) + (select bpjsKesehatan from employees where id = id_employees) + 
        (select pensiun from employees where id = id_employees) + (select pemakaianair from employees where id = id_employees) + 
        (select Koperasi from employees where id = id_employees) + SUM(sanksiapel) + SUM(sanksiterlambat) ) as Potongan, 
        
        (((select gajipokok from employees where id = id_employees) + (select irja from employees where id = id_employees) + 
        (select tkj from employees where id = id_employees) + (select khspajak from employees where id = id_employees) + 
        (select khspajak from employees where id = id_employees) + (select keluarga from employees where id = id_employees) + 
        (select sandangpangan from employees where id = id_employees)+ SUM(uanglembur) + SUM(transport) + SUM(perusahaan) - 
        (select pajak from employees where id = id_employees)) -
        ((select kreditbank from employees where id = id_employees) + (select kreditkoperasi from employees where id = id_employees) + 
        (select bpjsKerja from employees where id = id_employees) + (select bpjsKesehatan from employees where id = id_employees) + 
        (select pensiun from employees where id = id_employees) + (select pemakaianair from employees where id = id_employees) + 
        (select Koperasi from employees where id = id_employees) + SUM(sanksiapel) + SUM(sanksiterlambat) ) ) as TerimaBersih
                         
                                                   
      FROM attendancedays          
      WHERE date(attendancedays.date) >= date({datetimepicker1})                                                                                                                
            AND date(attendancedays.date) <= date({datetimepicker2})             
      GROUP BY harikerja, id_employees) as a                       
      
      
   LEFT JOIN employees on a.id_employees = employees.id    
   LEFT JOIN unitkerja on employees.id_unitkerja = unitkerja.id
   LEFT JOIN jabatan on jabatan.id = employees.id_jabatan 
   left outer join statuspegawai on statuspegawai.id   =   employees.id_statuspegawai  
   left outer join statuskeluarga on statuskeluarga.id = employees.id_statuskeluarga  
   left outer join pangkat on pangkat.id   =   employees.id_pangkat  
   left outer join active on active.id   =   employees.id_active  
   
    WHERE employees.id_active  = 1  
        AND employees.id_statuspegawai = 2   
        
ORDER BY unitkerja.id asc

thanks you

agusecc · 2024-11-21 04:33:32

agusecc
Member
Offline

Registered: 2015-12-19
Posts: 126

Re: I have problem sql query displays empty id in tablegrid

Thank you, my problem has been resolved well

sparrow · 2024-11-21 10:53:28

sparrow
Member
Offline

From: Рідна МАТИ, UKRAINE
Registered: 2018-06-02
Posts: 1,077

Re: I have problem sql query displays empty id in tablegrid

Several ways to pass variables to the Report depending on the task.

Post's attachments

Variabl.rar 7.74 kb, 27 downloads since 2024-11-21

agusecc · 2024-11-22 01:31:32

agusecc
Member
Offline

Registered: 2015-12-19
Posts: 126

Re: I have problem sql query displays empty id in tablegrid

Sparrow, Thanks Verry much. app Done.

I have problem sql query displays empty id in tablegrid

Posts: 12

1 Topic by agusecc 2024-11-15 09:00:38 (edited by agusecc 2024-11-15 09:02:33)

Topic: I have problem sql query displays empty id in tablegrid

2 Reply by sparrow 2024-11-15 11:50:08

Re: I have problem sql query displays empty id in tablegrid

3 Reply by agusecc 2024-11-16 03:16:47

Re: I have problem sql query displays empty id in tablegrid

4 Reply by agusecc 2024-11-17 00:12:12 (edited by agusecc 2024-11-17 00:12:50)

Re: I have problem sql query displays empty id in tablegrid

5 Reply by sparrow 2024-11-17 07:28:00 (edited by sparrow 2024-11-17 08:54:05)

Re: I have problem sql query displays empty id in tablegrid

6 Reply by agusecc 2024-11-17 18:53:58 (edited by agusecc 2024-11-17 18:54:23)

Re: I have problem sql query displays empty id in tablegrid

7 Reply by sparrow 2024-11-17 19:11:15

Re: I have problem sql query displays empty id in tablegrid

8 Reply by agusecc 2024-11-18 07:39:41 (edited by agusecc 2024-11-18 07:44:48)

Re: I have problem sql query displays empty id in tablegrid

9 Reply by agusecc 2024-11-21 02:45:07

Re: I have problem sql query displays empty id in tablegrid

10 Reply by agusecc 2024-11-21 04:33:32

Re: I have problem sql query displays empty id in tablegrid

11 Reply by sparrow 2024-11-21 10:53:28

Re: I have problem sql query displays empty id in tablegrid

12 Reply by agusecc 2024-11-22 01:31:32

Re: I have problem sql query displays empty id in tablegrid

Posts: 12