Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- CREATE
- ALGORITHM = UNDEFINED
- DEFINER = `root`@`localhost`
- SQL SECURITY DEFINER
- VIEW `haifa_store`.`beda_tgl` AS
- SELECT
- CAST(`d`.`orderDate` AS DATE) AS `order_date`,
- CAST(`d`.`shipDate` AS DATE) AS `ship_date`,
- (TO_DAYS(`d`.`shipDate`) - TO_DAYS(`d`.`orderDate`)) AS `beda_hari`,
- CAST((`d`.`shipDate` + INTERVAL 1 YEAR) AS DATE) AS `nextYear`,
- (TO_DAYS((`d`.`shipDate` + INTERVAL 1 YEAR)) - TO_DAYS(`d`.`orderDate`)) AS `bedaHari`,
- CAST((`d`.`shipDate` + INTERVAL 2 YEAR) AS DATE) AS `next2Year`,
- TIMESTAMPDIFF(YEAR, `d`.`orderDate`, (`d`.`shipDate` + INTERVAL 2 YEAR)) AS `selisihThn`,
- TIMESTAMPDIFF(MONTH, `d`.`orderDate`, (`d`.`shipDate` + INTERVAL 2 YEAR)) AS `selisihBln`,
- TIMESTAMPDIFF(DAY, `d`.`orderDate`, (`d`.`shipDate` + INTERVAL 2 YEAR)) AS `selisihHari`,
- CAST(NOW() AS DATE) AS `now`, TIMESTAMPDIFF(YEAR, `d`.`orderDate`, NOW()) AS `bedaThn`,
- (TIMESTAMPDIFF(MONTH, `d`.`orderDate`, NOW()) % 12) AS `bedaBln`,
- ((TIMESTAMPDIFF(DAY, `d`.`orderDate`, NOW()) % 365) % 30) AS `beda_Hr`,
- FLOOR(((TO_DAYS(NOW()) - TO_DAYS(`d`.`orderDate`)) / 365)) AS `thnBeda`,
- FLOOR((((TO_DAYS(NOW()) - TO_DAYS(`d`.`orderDate`)) % 365) / 30)) AS `blnBeda`,
- (((TO_DAYS(NOW()) - TO_DAYS(`d`.`orderDate`)) % 365) % 30) AS `hrBeda`
- FROM
- `haifa_store`.`detail` `d`
- ORDER BY (TO_DAYS((`d`.`shipDate` + INTERVAL 1 YEAR)) - TO_DAYS(`d`.`orderDate`)) DESC
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement