SHARE
TWEET

Untitled

a guest May 21st, 2019 70 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. update dt_date set flg_holiday = 'Y' where string_date in ('20190403','20190417','20190419', '20190501');
  2.  
  3. WITH one_month_absen AS (
  4.          SELECT a_1.user_id,
  5.             b_1.string_date,
  6.             a_1.full_name,
  7.             b_1.flg_holiday
  8.            FROM t_user a_1,
  9.             dt_date b_1
  10.           WHERE (a_1.user_id <> ALL (ARRAY[1::bigint, 2::bigint, 16::bigint])) AND a_1.active::text = 'Y'::text
  11.         ),
  12.   raw_data AS (
  13.  SELECT a.string_date, a.full_name,
  14.     to_char(to_timestamp(a.string_date::text, 'YYYYMMDD'::text), 'FMDay, DD Mon YYYY'::text) AS date,
  15.         CASE
  16.             WHEN b.checkin_datetime IS NOT NULL THEN to_char(to_timestamp(b.checkin_datetime::text, 'YYYYMMDDHH24MISS'::text), 'HH24:MI:SS'::text)
  17.             ELSE '-'::text
  18.         END AS checkin,
  19.         CASE
  20.             WHEN b.checkout_datetime IS NOT NULL AND b.checkout_datetime::text <> ''::text AND b.checkout_datetime::text <> '-'::text THEN to_char(to_timestamp(b.checkout_datetime::text, 'YYYYMMDDHH24MISS'::text), 'HH24:MI:SS'::text)
  21.             ELSE '-'::text
  22.         END AS checkout,
  23.         CASE
  24.             WHEN a.flg_holiday::text = 'N'::text AND b.checkin_datetime IS NULL AND a.string_date::text <= to_char('now'::text::date::timestamp with time zone, 'YYYYMMDD'::text) THEN 'N'::text
  25.             WHEN a.string_date::text > to_char('now'::text::date::timestamp with time zone, 'YYYYMMDD'::text) THEN '-'::text
  26.             ELSE 'Y'::text
  27.         END AS status_masuk,
  28.         CASE
  29.             WHEN a.flg_holiday::text = 'Y'::text AND b.checkin_datetime IS NOT NULL THEN 'Y'::text
  30.             WHEN a.string_date::text > to_char('now'::text::date::timestamp with time zone, 'YYYYMMDD'::text) THEN '-'::text
  31.             ELSE 'N'::text
  32.         END AS status_lembur,
  33.         CASE
  34.             WHEN b.checkout_datetime IS NOT NULL AND b.checkout_datetime::text <> ''::text AND b.checkout_datetime::text <> '-'::text AND a.flg_holiday::text <> 'Y'::text THEN 'N'::text
  35.             ELSE
  36.             CASE
  37.                 WHEN a.string_date::text > to_char('now'::text::date::timestamp with time zone, 'YYYYMMDD'::text) OR a.flg_holiday::text = 'Y'::text THEN '-'::text
  38.                 ELSE 'Y'::text
  39.             END
  40.         END AS status_absen_kosong,
  41.         CASE
  42.             WHEN b.checkin_datetime IS NOT NULL AND a.flg_holiday::text <> 'Y'::text THEN
  43.             CASE
  44.                 WHEN trunc(date_part('epoch'::text, to_timestamp(b.checkin_datetime::text, 'YYYYMMDDHH24MISS'::text) - to_timestamp(to_char(to_timestamp(b.checkin_datetime::text, 'YYYYMMDDHH24MISS'::text), 'YYYYMMDD'::text) || '08300000'::text, 'YYYYMMDDHH24MISS'::text)) / 60::double precision) < 1::double precision THEN 0
  45.                 ELSE trunc(date_part('epoch'::text, to_timestamp(b.checkin_datetime::text, 'YYYYMMDDHH24MISS'::text) - to_timestamp(to_char(to_timestamp(b.checkin_datetime::text, 'YYYYMMDDHH24MISS'::text), 'YYYYMMDD'::text) || '08300000'::text, 'YYYYMMDDHH24MISS'::text)) / 60::double precision)
  46.             END
  47.             ELSE 0
  48.         END AS telat,
  49.         CASE
  50.             WHEN b.checkout_datetime IS NOT NULL AND b.checkout_datetime::text <> ''::text AND b.checkout_datetime::text <> '-'::text AND a.flg_holiday::text <> 'Y'::text THEN
  51.             CASE
  52.                 WHEN to_char(to_timestamp(b.checkin_datetime::text, 'YYYYMMDDHH24MISS'::text), 'HH24MISS'::text)::integer > 830 THEN
  53.                 CASE
  54.                     WHEN (- (trunc(date_part('epoch'::text, to_timestamp(b.checkout_datetime::text, 'YYYYMMDDHH24MISS'::text) - to_timestamp(b.checkin_datetime::text, 'YYYYMMDDHH24MISS'::text)) / 60::double precision) - 540::double precision)) < 1::double precision THEN 0
  55.                     ELSE (- (trunc(date_part('epoch'::text, to_timestamp(b.checkout_datetime::text, 'YYYYMMDDHH24MISS'::text) - to_timestamp(b.checkin_datetime::text, 'YYYYMMDDHH24MISS'::text)) / 60::double precision) - 540::double precision))
  56.                 END
  57.                 ELSE
  58.                 CASE
  59.                     WHEN (- (trunc(date_part('epoch'::text, to_timestamp(b.checkout_datetime::text, 'YYYYMMDDHH24MISS'::text) - to_timestamp(to_char(to_timestamp(b.checkout_datetime::text, 'YYYYMMDDHH24MISS'::text), 'YYYYMMDD'::text) || '08300000'::text, 'YYYYMMDDHH24MISS'::text)) / 60::double precision) - 540::double precision)) < 1::double precision THEN 0
  60.                     ELSE (- (trunc(date_part('epoch'::text, to_timestamp(b.checkout_datetime::text, 'YYYYMMDDHH24MISS'::text) - to_timestamp(to_char(to_timestamp(b.checkout_datetime::text, 'YYYYMMDDHH24MISS'::text), 'YYYYMMDD'::text) || '08300000'::text, 'YYYYMMDDHH24MISS'::text)) / 60::double precision) - 540::double precision))
  61.                 END
  62.             END
  63.             ELSE 0
  64.         END AS kurang,
  65.         CASE
  66.             WHEN b.checkin_datetime IS NOT NULL AND b.checkin_datetime::text <> ''::text AND b.checkin_datetime::text <> '-'::text AND (b.checkout_datetime IS NULL OR b.checkout_datetime::text = ''::text OR b.checkout_datetime::text = '-'::text) THEN 'Lupa checkout'::character varying
  67.             ELSE COALESCE(d.reason_name, '-'::character varying)
  68.         END AS description,
  69.         CASE
  70.             WHEN COALESCE(d.reason_code, ''::character varying)::text = ANY (ARRAY['C'::character varying, 'DL'::character varying, 'DD'::character varying]::text[]) THEN 'Y'::text
  71.             ELSE 'N'::text
  72.         END AS escape_denda
  73.    FROM one_month_absen a
  74.      LEFT JOIN at_attendance b ON a.string_date::text = "substring"(b.checkin_datetime::text, 1, 8) AND a.user_id = b.user_id
  75.      LEFT JOIN t_manage_lost_checkin c ON a.string_date::text = c.checkin_date::text AND a.user_id = c.user_id
  76.      LEFT JOIN t_reason d ON c.reason_id = d.reason_id
  77.   WHERE a.flg_holiday::text = 'N'::text OR a.flg_holiday::text = 'Y'::text AND b.checkin_datetime IS NOT NULL
  78. )
  79. SELECT full_name, "date", checkin, checkout, status_masuk, status_lembur, status_absen_kosong, telat, kurang,
  80.     telat+kurang AS "telat+kurang",
  81.     CASE WHEN escape_denda = 'N' THEN
  82.         CASE WHEN status_absen_kosong = 'Y' THEN 'Golongan 4'
  83.         ELSE
  84.             CASE
  85.                 WHEN telat BETWEEN 1 AND 15 THEN 'Golongan 0'
  86.                 WHEN telat BETWEEN 16 AND 30 THEN 'Golongan 1'
  87.                 WHEN telat BETWEEN 31 AND 45 THEN 'Golongan 2'
  88.                 WHEN telat BETWEEN 46 AND 60 THEN 'Golongan 3'
  89.                 WHEN telat > 60 THEN 'Golongan 4'
  90.                 ELSE '-'
  91.             END
  92.         END
  93.     ELSE '-' END AS golongan_denda,
  94.     description
  95. FROM raw_data
  96. WHERE string_date between '20190321' and '20190420'
  97. ORDER BY full_name, string_date;
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top