Advertisement
raden_rangga

kalkulasi_beda_tanggal

Sep 29th, 2022
803
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
MySQL 1.48 KB | Source Code | 0 0
  1. CREATE
  2.     ALGORITHM = UNDEFINED
  3.     DEFINER = `root`@`localhost`
  4.     SQL SECURITY DEFINER
  5. VIEW `haifa_store`.`beda_tgl` AS
  6.     SELECT
  7.         CAST(`d`.`orderDate` AS DATE) AS `order_date`,
  8.         CAST(`d`.`shipDate` AS DATE) AS `ship_date`,
  9.         (TO_DAYS(`d`.`shipDate`) - TO_DAYS(`d`.`orderDate`)) AS `beda_hari`,
  10.         CAST((`d`.`shipDate` + INTERVAL 1 YEAR) AS DATE) AS `nextYear`,
  11.         (TO_DAYS((`d`.`shipDate` + INTERVAL 1 YEAR)) - TO_DAYS(`d`.`orderDate`)) AS `bedaHari`,
  12.         CAST((`d`.`shipDate` + INTERVAL 2 YEAR) AS DATE) AS `next2Year`,
  13.         TIMESTAMPDIFF(YEAR, `d`.`orderDate`, (`d`.`shipDate` + INTERVAL 2 YEAR)) AS `selisihThn`,
  14.         TIMESTAMPDIFF(MONTH, `d`.`orderDate`, (`d`.`shipDate` + INTERVAL 2 YEAR)) AS `selisihBln`,
  15.         TIMESTAMPDIFF(DAY, `d`.`orderDate`, (`d`.`shipDate` + INTERVAL 2 YEAR)) AS `selisihHari`,
  16.         CAST(NOW() AS DATE) AS `now`, TIMESTAMPDIFF(YEAR, `d`.`orderDate`, NOW()) AS `bedaThn`,
  17.         (TIMESTAMPDIFF(MONTH, `d`.`orderDate`, NOW()) % 12) AS `bedaBln`,
  18.         ((TIMESTAMPDIFF(DAY, `d`.`orderDate`, NOW()) % 365) % 30) AS `beda_Hr`,
  19.         FLOOR(((TO_DAYS(NOW()) - TO_DAYS(`d`.`orderDate`)) / 365)) AS `thnBeda`,
  20.         FLOOR((((TO_DAYS(NOW()) - TO_DAYS(`d`.`orderDate`)) % 365) / 30)) AS `blnBeda`,
  21.         (((TO_DAYS(NOW()) - TO_DAYS(`d`.`orderDate`)) % 365) % 30) AS `hrBeda`
  22.     FROM
  23.         `haifa_store`.`detail` `d`
  24.     ORDER BY (TO_DAYS((`d`.`shipDate` + INTERVAL 1 YEAR)) - TO_DAYS(`d`.`orderDate`)) DESC
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement