23 lines
1.1 KiB
SQL
23 lines
1.1 KiB
SQL
DELIMITER $$
|
|
|
|
ALTER ALGORITHM=UNDEFINED DEFINER=`apzl`@`%` SQL SECURITY DEFINER VIEW `apzl`.`vi_distributor_lines` AS (
|
|
SELECT
|
|
`di`.`distributor_no` AS `DISTRIBUTOR_NO`,
|
|
`di`.`Total` AS `TOTAL`,
|
|
IFNULL(`vlrp`.`corpus_over`,0) AS `USEDLINES`,
|
|
(CASE WHEN ((`di`.`Total` = '') OR (`di`.`Total` = NULL)) THEN '' ELSE ROUND((IFNULL(`di`.`Total`,0) - IFNULL(`vlrp`.`corpus_over`,0)),2) END) AS `RELINES`,
|
|
`di`.`channeltype` AS `CHANNELTYPE`
|
|
FROM (`apzl`.`distributor_info` `di`
|
|
LEFT JOIN (SELECT
|
|
SUM(`vlrp`.`corpus_over`) AS `corpus_over`,
|
|
`di`.`distributor_no` AS `distributor_no`
|
|
FROM ((`apzl`.`vi_lc_rent_plan11` `vlrp`
|
|
LEFT JOIN `apzl`.`lb_contract_info` `lci`
|
|
ON ((`vlrp`.`contract_id` = `lci`.`ID`)))
|
|
LEFT JOIN `apzl`.`distributor_info` `di`
|
|
ON ((`lci`.`distributor_id` = `di`.`distributor_no`)))
|
|
GROUP BY `di`.`distributor_no`) `vlrp`
|
|
ON ((`vlrp`.`distributor_no` = `di`.`distributor_no`)))
|
|
GROUP BY `di`.`distributor_no`)$$
|
|
|
|
DELIMITER ; |