0
\$\begingroup\$

I have a table that look like this

ID      NIP       SHIFT DATE
289305  01000267    M   2015-12-01 00:00:00.000
289306  01000267    P   2015-12-01 00:00:00.000
289307  01000267    S   2015-12-01 00:00:00.000
289127  01020358    M   2015-12-01 00:00:00.000
289128  01020358    P   2015-12-01 00:00:00.000
289129  01020358    S   2015-12-01 00:00:00.000
284686  01020386    P   2015-12-01 00:00:00.000
290925  01030389    F   2015-12-01 00:00:00.000
290685  01030390    F   2015-12-01 00:00:00.000
289485  01000306    P   2015-12-01 00:00:00.000
283069  01030391    P   2015-12-01 00:00:00.000
290715  01030392    M   2015-12-01 00:00:00.000
290716  01030392    P   2015-12-01 00:00:00.000
290717  01030392    S   2015-12-01 00:00:00.000
289605  01040399    M   2015-12-01 00:00:00.000
289606  01040399    P   2015-12-01 00:00:00.000
289607  01040399    S   2015-12-01 00:00:00.000
281490  01050401    M   2015-12-01 00:00:00.000
281488  01050401    P   2015-12-01 00:00:00.000
281489  01050401    S   2015-12-01 00:00:00.000
281953  01060545    P   2015-12-01 00:00:00.000
289217  01060549    M   2015-12-01 00:00:00.000
289218  01060549    P   2015-12-01 00:00:00.000
289219  01060549    S   2015-12-01 00:00:00.000
289515  01060671    M   2015-12-01 00:00:00.000
289516  01060671    P   2015-12-01 00:00:00.000
289517  01060671    S   2015-12-01 00:00:00.000
289785  01110963    M   2015-12-01 00:00:00.000
289786  01110963    P   2015-12-01 00:00:00.000
289787  01110963    S   2015-12-01 00:00:00.000
290415  01120632    M   2015-12-01 00:00:00.000
290416  01120632    P   2015-12-01 00:00:00.000
290417  01120632    S   2015-12-01 00:00:00.000
290235  01120964    M   2015-12-01 00:00:00.000
290236  01120964    P   2015-12-01 00:00:00.000
290237  01120964    S   2015-12-01 00:00:00.000
289037  01130654    M   2015-12-01 00:00:00.000
289038  01130654    P   2015-12-01 00:00:00.000
289039  01130654    S   2015-12-01 00:00:00.000
290595  01130655    M   2015-12-01 00:00:00.000
290596  01130655    P   2015-12-01 00:00:00.000
290597  01130655    S   2015-12-01 00:00:00.000
291045  01130658    M   2015-12-01 00:00:00.000
291046  01130658    P   2015-12-01 00:00:00.000
291047  01130658    S   2015-12-01 00:00:00.000
291135  01131020    M   2015-12-01 00:00:00.000
291136  01131020    P   2015-12-01 00:00:00.000
291137  01131020    S   2015-12-01 00:00:00.000
290835  01141011    M   2015-12-01 00:00:00.000
290836  01141011    P   2015-12-01 00:00:00.000
290837  01141011    S   2015-12-01 00:00:00.000
...

Now, I want to display a view that something like this:

NIP         P   S   M   C   D   I   K   L   R
03950086    21  0   0   3   0   0   0   7   0
03070445    3   11  9   0   0   0   0   8   0
32041041    7   9   6   6   0   0   0   6   0
32980188    25  0   0   0   0   0   0   6   0
03000255    6   12  6   0   0   0   0   7   0
03020366    8   9   6   0   0   0   0   8   0
30000237    24  0   0   0   0   0   0   7   0
32920073    11  10  0   0   0   2   0   8   0
03020365    0   0   0   0   0   0   0   0   0
03140836    8   10  6   0   0   0   0   7   0
01030391    25  0   0   0   0   0   0   6   0
32151002    8   6   8   0   0   0   0   9   0
32150929    5   9   9   0   0   0   0   8   0
03000294    7   10  6   0   0   0   0   8   0
03010339    6   9   8   0   0   0   0   8   0
03950115    22  0   0   0   0   1   0   8   0
03091087    6   10  7   0   0   0   0   8   0
03100508    2   10  6   6   0   0   0   7   0
32100618    23  0   1   0   0   0   0   8   0
03140474    8   6   9   0   0   0   0   8   0
32090500    21  0   3   0   0   0   0   10  0
03060415    4   11  8   3   0   0   0   5   0
03020369    22  0   0   2   0   0   0   7   0
03950096    20  3   0   0   0   0   0   8   0
03130639    9   7   6   0   0   0   0   9   0
03141056    4   9   9   0   0   0   0   9   0
01000306    31  0   0   0   0   0   0   0   0
03100607    7   7   8   0   1   0   0   8   0
03141050    7   8   8   0   0   0   0   8   0
03080475    4   8   7   6   0   0   0   6   0
50140009    15  25  15  0   0   0   0   6   0
31950104    10  11  5   0   0   0   0   5   0
03041026    11  12  0   0   2   0   0   6   0
32151118    11  8   2   0   0   0   2   8   0
05150750    18  3   3   0   0   0   0   7   0
03000261    13  11  0   0   0   0   0   7   0
07151125    13  0   0   0   0   0   0   4   0
03100509    4   7   7   4   0   0   0   9   0
01060548    0   0   0   0   0   0   0   0   0
03060417    5   10  8   0   0   0   0   8   0
32151124    24  0   0   0   0   0   0   7   0
03080476    5   8   9   3   0   0   0   6   0
11110677    6   16  2   0   0   0   0   7   0
03970169    0   0   0   31  0   0   0   0   0
32890042    18  0   0   6   0   0   0   7   0
03990229    7   0   15  0   0   0   0   9   0
32890043    24  0   0   5   0   0   0   2   0
01970201    25  0   0   0   0   0   0   6   0
03140007    7   8   8   0   1   0   0   7   0
32150930    11  5   6   0   0   0   0   9   0

where P,S,M, etc, is the count number, and date is between startdate and enddate, this is my attempt so far

SELECT        
                             (SELECT        COUNT(shift) AS Expr1
                               FROM            dbo.JadwalKerja
                               WHERE        (nip = dbo.JadwalDinas_DaftarKaryawan.NIP) AND (shift = 'B' OR
                                                         shift = 'E' OR
                                                         shift = 'F' OR
                                                         shift = 'G' OR
                                                         shift = 'H' OR
                                                         shift = 'J' OR
                                                         shift = 'P') AND (date >= CONVERT(DATETIME, '2015-12-01 00:00:00', 102)) AND (date <= CONVERT(DATETIME, '2015-12-31 00:00:00', 102))) AS P,
                             (SELECT        COUNT(shift) AS Expr1
                               FROM            dbo.JadwalKerja AS JadwalKerja_8
                               WHERE        (nip = dbo.JadwalDinas_DaftarKaryawan.NIP) AND (shift = 'B' OR
                                                         shift = 'N' OR
                                                         shift = 'O' OR
                                                         shift = 'S' OR
                                                         shift = 'T') AND (date >= CONVERT(DATETIME, '2015-12-01 00:00:00', 102)) AND (date <= CONVERT(DATETIME, '2015-12-31 00:00:00', 102))) AS S,
                             (SELECT        COUNT(shift) AS Expr1
                               FROM            dbo.JadwalKerja AS JadwalKerja_7
                               WHERE        (nip = dbo.JadwalDinas_DaftarKaryawan.NIP) AND (shift = 'B' OR
                                                         shift = 'M' OR
                                                         shift = 'Q') AND (date >= CONVERT(DATETIME, '2015-12-01 00:00:00', 102)) AND (date <= CONVERT(DATETIME, '2015-12-31 00:00:00', 102))) AS M,
                             (SELECT        COUNT(shift) AS Expr1
                               FROM            dbo.JadwalKerja AS JadwalKerja_6
                               WHERE        (nip = dbo.JadwalDinas_DaftarKaryawan.NIP) AND (shift = 'C') AND (date >= CONVERT(DATETIME, '2015-12-01 00:00:00', 102)) AND (date <= CONVERT(DATETIME, '2015-12-31 00:00:00', 102))) AS C,
                             (SELECT        COUNT(shift) AS Expr1
                               FROM            dbo.JadwalKerja AS JadwalKerja_5
                               WHERE        (nip = dbo.JadwalDinas_DaftarKaryawan.NIP) AND (shift = 'D') AND (date >= CONVERT(DATETIME, '2015-12-01 00:00:00', 102)) AND (date <= CONVERT(DATETIME, '2015-12-31 00:00:00', 102))) AS D,
                             (SELECT        COUNT(shift) AS Expr1
                               FROM            dbo.JadwalKerja AS JadwalKerja_4
                               WHERE        (nip = dbo.JadwalDinas_DaftarKaryawan.NIP) AND (shift = 'I') AND (date >= CONVERT(DATETIME, '2015-12-01 00:00:00', 102)) AND (date <= CONVERT(DATETIME, '2015-12-31 00:00:00', 102))) AS I,
                             (SELECT        COUNT(shift) AS Expr1
                               FROM            dbo.JadwalKerja AS JadwalKerja_3
                               WHERE        (nip = dbo.JadwalDinas_DaftarKaryawan.NIP) AND (shift = 'K') AND (date >= CONVERT(DATETIME, '2015-12-01 00:00:00', 102)) AND (date <= CONVERT(DATETIME, '2015-12-31 00:00:00', 102))) AS K,
                             (SELECT        COUNT(shift) AS Expr1
                               FROM            dbo.JadwalKerja AS JadwalKerja_2
                               WHERE        (nip = dbo.JadwalDinas_DaftarKaryawan.NIP) AND (shift = 'L') AND (date >= CONVERT(DATETIME, '2015-12-01 00:00:00', 102)) AND (date <= CONVERT(DATETIME, '2015-12-31 00:00:00', 102))) AS L,
                             (SELECT        COUNT(shift) AS Expr1
                               FROM            dbo.JadwalKerja AS JadwalKerja_1
                               WHERE        (nip = dbo.JadwalDinas_DaftarKaryawan.NIP) AND (shift = 'R') AND (date >= CONVERT(DATETIME, '2015-12-01 00:00:00', 102)) AND (date <= CONVERT(DATETIME, '2015-12-31 00:00:00', 102))) 
                         AS R
FROM            dbo.JadwalDinas_DaftarKaryawan
ORDER BY Nama

I am not sure if this is the correct way to do this.

I use SQL server 2000, so PIVOT isn't an option.

\$\endgroup\$
1
  • \$\begingroup\$ So, in your result table, you are combining shifts? The only obvious chanegs I see: use IN ('A', 'B', ...) instead of OR. A temporary table (or nested table) may help too. \$\endgroup\$ – user1149 Jan 23 '16 at 16:50
3
\$\begingroup\$

At first you'd choose indentation to make the whole query more easily readable:

SELECT        
  (
    SELECT COUNT(shift) AS Expr1
    FROM dbo.JadwalKerja
    WHERE
      (nip = dbo.JadwalDinas_DaftarKaryawan.NIP)
      AND (
        shift = 'B' OR
        shift = 'E' OR
        shift = 'F' OR
        shift = 'G' OR
        shift = 'H' OR
        shift = 'J' OR
        shift = 'P'
      )
      AND (date >= CONVERT(DATETIME, '2015-12-01 00:00:00', 102))
      AND (date <= CONVERT(DATETIME, '2015-12-31 00:00:00', 102))
  ) AS P,
  (
    SELECT COUNT(shift) AS Expr1
    FROM dbo.JadwalKerja AS JadwalKerja_8
    WHERE
      (nip = dbo.JadwalDinas_DaftarKaryawan.NIP)
      AND (
        shift = 'B' OR
        shift = 'N' OR
        shift = 'O' OR
        shift = 'S' OR
        shift = 'T'
      )
      AND (date >= CONVERT(DATETIME, '2015-12-01 00:00:00', 102))
      AND (date <= CONVERT(DATETIME, '2015-12-31 00:00:00', 102))
  ) AS S,
  (
    SELECT COUNT(shift) AS Expr1
    FROM dbo.JadwalKerja AS JadwalKerja_7
    WHERE
      (nip = dbo.JadwalDinas_DaftarKaryawan.NIP)
      AND (
        shift = 'B' OR
        shift = 'M' OR
        shift = 'Q'
      )
      AND (date >= CONVERT(DATETIME, '2015-12-01 00:00:00', 102))
      AND (date <= CONVERT(DATETIME, '2015-12-31 00:00:00', 102))
  ) AS M,
  (
    SELECT COUNT(shift) AS Expr1
    FROM dbo.JadwalKerja AS JadwalKerja_6
    WHERE
      (nip = dbo.JadwalDinas_DaftarKaryawan.NIP)
      AND (shift = 'C')
      AND (date >= CONVERT(DATETIME, '2015-12-01 00:00:00', 102))
      AND (date <= CONVERT(DATETIME, '2015-12-31 00:00:00', 102))
  ) AS C,
  (
    SELECT COUNT(shift) AS Expr1
    FROM dbo.JadwalKerja AS JadwalKerja_5
    WHERE
      (nip = dbo.JadwalDinas_DaftarKaryawan.NIP)
      AND (shift = 'D')
      AND (date >= CONVERT(DATETIME, '2015-12-01 00:00:00', 102))
      AND (date <= CONVERT(DATETIME, '2015-12-31 00:00:00', 102))
  ) AS D,
  (
    SELECT COUNT(shift) AS Expr1
    FROM dbo.JadwalKerja AS JadwalKerja_4
    WHERE
      (nip = dbo.JadwalDinas_DaftarKaryawan.NIP)
      AND (shift = 'I')
      AND (date >= CONVERT(DATETIME, '2015-12-01 00:00:00', 102))
      AND (date <= CONVERT(DATETIME, '2015-12-31 00:00:00', 102))
  ) AS I,
  (
    SELECT COUNT(shift) AS Expr1
    FROM dbo.JadwalKerja AS JadwalKerja_3
    WHERE
      (nip = dbo.JadwalDinas_DaftarKaryawan.NIP)
      AND (shift = 'K')
      AND (date >= CONVERT(DATETIME, '2015-12-01 00:00:00', 102))
      AND (date <= CONVERT(DATETIME, '2015-12-31 00:00:00', 102))
  ) AS K,
  (
    SELECT COUNT(shift) AS Expr1
    FROM dbo.JadwalKerja AS JadwalKerja_2
    WHERE
      (nip = dbo.JadwalDinas_DaftarKaryawan.NIP)
      AND (shift = 'L')
      AND (date >= CONVERT(DATETIME, '2015-12-01 00:00:00', 102))
      AND (date <= CONVERT(DATETIME, '2015-12-31 00:00:00', 102))
  ) AS L,
  (
    SELECT COUNT(shift) AS Expr1
    FROM dbo.JadwalKerja AS JadwalKerja_1
    WHERE
      (nip = dbo.JadwalDinas_DaftarKaryawan.NIP)
      AND (shift = 'R')
      AND (date >= CONVERT(DATETIME, '2015-12-01 00:00:00', 102))
      AND (date <= CONVERT(DATETIME, '2015-12-31 00:00:00', 102))
  ) AS R
FROM dbo.JadwalDinas_DaftarKaryawan
ORDER BY Nama

That's said, its expression may be simplified in several ways.

There is no need of (furthermore unused) aliases like AS Expr1 or AS JadwalKerja_N, and they can be dropped.

All the OR-ed sets of conditions can be more simply expressed using IN, so AND (shift = 'A' OR ... OR shift = 'Z') becomes AND shift IN ('A', ..., 'Z').

The way you expressed the date conditions (CONVERT style = 102) leads to deduct that the date field original content is currently of the form yyyy.mm.dd:

AND (date >= CONVERT(DATETIME, '2015-12-01 00:00:00', 102))
AND (date <= CONVERT(DATETIME, '2015-12-31 00:00:00', 102))

So why to use another style for the start/end date? You can merely write (also noting that the 102 style doesn't takes care of hours, and that the parenthesis are not needed):

AND date >= '2015.12.01'
AND date >= '2015.12.31'

More over, this can be expressed within a unique condition, like this:

AND date BETWEEN '2015.12.01' AND '2015.12.31'

With all the changes suggested above, you get a dramatically reduced query:

SELECT        
  (
    SELECT COUNT(shift) FROM dbo.JadwalKerja
    WHERE (nip = dbo.JadwalDinas_DaftarKaryawan.NIP)
      AND shift IN ('B', 'E', 'F', 'G', 'H', 'J', 'P')
      AND date BETWEEN '2015.12.01' AND '2015.12.31'
  ) AS P,
  (
    SELECT COUNT(shift) FROM dbo.JadwalKerja
    WHERE (nip = dbo.JadwalDinas_DaftarKaryawan.NIP)
      AND shift IN ('B', 'N', 'O', 'S', 'T')
      AND date BETWEEN '2015.12.01' AND '2015.12.31'
  ) AS S,
  (
    SELECT COUNT(shift) FROM dbo.JadwalKerja
    WHERE (nip = dbo.JadwalDinas_DaftarKaryawan.NIP)
      AND shift IN ('B', 'M', 'Q')
      AND date BETWEEN '2015.12.01' AND '2015.12.31'
  ) AS M,
  (
    SELECT COUNT(shift) FROM dbo.JadwalKerja
    WHERE (nip = dbo.JadwalDinas_DaftarKaryawan.NIP)
      AND shift = 'C'
      AND date BETWEEN '2015.12.01' AND '2015.12.31'
  ) AS C,
  (
    SELECT COUNT(shift) FROM dbo.JadwalKerja
    WHERE (nip = dbo.JadwalDinas_DaftarKaryawan.NIP)
      AND shift = 'D'
      AND date BETWEEN '2015.12.01' AND '2015.12.31'
  ) AS D,
  (
    SELECT COUNT(shift) FROM dbo.JadwalKerja
    WHERE (nip = dbo.JadwalDinas_DaftarKaryawan.NIP)
      AND shift = 'I'
      AND date BETWEEN '2015.12.01' AND '2015.12.31'
  ) AS I,
  (
    SELECT COUNT(shift) FROM dbo.JadwalKerja
    WHERE (nip = dbo.JadwalDinas_DaftarKaryawan.NIP)
      AND shift = 'K'
      AND date BETWEEN '2015.12.01' AND '2015.12.31'
  ) AS K,
  (
    SELECT COUNT(shift) FROM dbo.JadwalKerja
    WHERE (nip = dbo.JadwalDinas_DaftarKaryawan.NIP)
      AND shift = 'L'
      AND date BETWEEN '2015.12.01' AND '2015.12.31'
  ) AS L,
  (
    SELECT COUNT(shift) FROM dbo.JadwalKerja
    WHERE (nip = dbo.JadwalDinas_DaftarKaryawan.NIP)
      AND shift = 'R'
      AND date BETWEEN '2015.12.01' AND '2015.12.31'
  ) AS R
FROM dbo.JadwalDinas_DaftarKaryawan
ORDER BY Nama

Going further, you might consider using a two-steps query, where the first one would be creating a temp table from:

SELECT shift, nip, date
FROM dbo.JadwalKerja
WHERE date BETWEEN '2015.12.01' AND '2015.12.31'

then using the temp table in place of dbo.JadwalKerja in the second step, where the date condition can be omitted.

Note 1: this alternate way may improve performance or not, depending on the distribution of the involved contents in the table.
Note 2: since I'm not familiar with SQL Server I didn't specify which way could be used: might be using INSERT INTO ... SELECT syntax, or SELECT ... INTO OUTFILE then LOAD DATA INFILE.

On the other hand, unless I'm missing something, I don't see how the ORDER BY Nama can make sense here.

\$\endgroup\$
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.