Sebastian Bagya Sebastian Bagya - 15 days ago 5
SQL Question

How to make a schedule table query from SQL Server 2012?

I need help to make a schedule table for employee from my table the main problem is the row not start from first row on every column so the row keep continue, see picture below:

Table

we can see that the next column is null on the first row and continue from the last row. this is my query and table relations, you can see it below.

enter image description here

SELECT (SELECT PEGAWAI.NAMAPEGAWAI
WHERE (JADWAL.HARIKERJA = 'Tuesday')) AS Selasa,
(SELECT PEGAWAI.NAMAPEGAWAI
WHERE (JADWAL.HARIKERJA = 'Wednesday')) AS Rabu,
(SELECT PEGAWAI.NAMAPEGAWAI
WHERE (JADWAL.HARIKERJA = 'Thursday')) AS Kamis,
(SELECT PEGAWAI.NAMAPEGAWAI
WHERE (JADWAL.HARIKERJA = 'Friday')) AS Jumat,
(SELECT PEGAWAI.NAMAPEGAWAI
WHERE (JADWAL.HARIKERJA = 'Sabtu')) AS Saturday,
(SELECT PEGAWAI.NAMAPEGAWAI
WHERE (JADWAL.HARIKERJA = 'Monday')) AS Minggu FROM JADWAL INNER JOIN
JADWALPEGAWAI ON JADWAL.IDJADWAL = JADWALPEGAWAI.IDJADWAL INNER JOIN
PEGAWAI ON JADWALPEGAWAI.IDPEGAWAI = PEGAWAI.IDPEGAWAI WHERE (JADWAL.SHIFT = 'I')

Answer

What seems to be causing your query not to behave as you expect, is that you have no GROUP BY clause in your query, which means that you get one row of output for each for (in this case) each row of the JadwalPegawai table.

At a minimum, adding GROUP BY Pegawai.NamaPegawai after your WHERE … clause should fix this, but I think that we can do even better:

Select Min(iif(j.Harikerja = 'Tuesday', p.NamaPegawai, Null)) As Selasa,
    Min(iif(j.Harikerja = 'Wednesday', p.NamaPegawai, Null)) As Rabu,
    …
  From Jadwal As j
  Join JadwalPegawai As jp On j.IdJadwal = jp.IdJadwal
  Join Pegawai As p On jp.IdPegawai = p.IdPegawai
  Where j.Shift = 'I'
  Group By p.NamaPegawai;

This should be logically equivalent, but doesn't use subqueries. Hope I didn't make any spelling errors, I'm not too good at bahasa Indonesia...

Comments