Untitled

From anonymous, 6 Months ago, written in SQL, viewed 81 times.
URL https://paste.codexterous.com/view/62339bb6 Embed
Download Paste or View Raw
  1. SELECT SQL_NO_CACHE
  2.     `credit_union`,
  3.     (SELECT COUNT(`s1`.`loannumber`)
  4.           FROM `cuactivity` AS `s1`
  5.  
  6.           LEFT OUTER JOIN `main_dealership` AS `d` ON `d`.`id` = `s1`.`dealer_id`
  7.           LEFT OUTER JOIN `data_one_vehiclemodel` AS `vmd` ON `vmd`.`vehicle_id` = `s1`.`vehicle_id`
  8.           LEFT OUTER JOIN `data_one_vehiclemake` AS `vmk` ON `vmk`.`id` = `vmd`.`make_id`
  9.           INNER JOIN `reports_importedfile` AS `fl` ON `fl`.`id` = `s1`.`imported_file`
  10.         WHERE CASE
  11.                 WHEN `credit_union` = 'Total' THEN `s1`.`lendername` LIKE '%'
  12.                 ELSE `s1`.`lendername` = `credit_union`
  13.               END
  14.               AND `s1`.`appreceiveddate` >= '2020-03-16 00:00:00' AND `s1`.`appreceiveddate` <= '2020-03-31 23:59:59'
  15.                AND (LOWER(`s1`.`lendername`) = 'island') AND (`s1`.`fundingloantype` = 'used-lease' OR `s1`.`fundingloantype` = 'new-lease')
  16.     ) AS `applications`,
  17.     (SELECT COUNT(`s1`.`loannumber`)
  18.           FROM `cuactivity` AS `s1`
  19.  
  20.           LEFT OUTER JOIN `main_dealership` AS `d` ON `d`.`id` = `s1`.`dealer_id`
  21.           LEFT OUTER JOIN `data_one_vehiclemodel` AS `vmd` ON `vmd`.`vehicle_id` = `s1`.`vehicle_id`
  22.           LEFT OUTER JOIN `data_one_vehiclemake` AS `vmk` ON `vmk`.`id` = `vmd`.`make_id`
  23.           INNER JOIN `reports_importedfile` AS `fl` ON `fl`.`id` = `s1`.`imported_file`
  24.         WHERE CASE
  25.                 WHEN `credit_union` = 'Total' THEN `s1`.`lendername` LIKE '%'
  26.                 ELSE `s1`.`lendername` = `credit_union`
  27.               END
  28.               AND `s1`.`vehiclevin` = ''
  29.  
  30.               AND `s1`.`fundingdate` IS NOT NULL AND `s1`.`fundingdate` >= '2020-03-16 00:00:00' AND `s1`.`fundingdate` <= '2020-03-31 23:59:59' AND `s1`.`loanstatus` IN ('booked')
  31.                AND (LOWER(`s1`.`lendername`) = 'island') AND (`s1`.`fundingloantype` = 'used-lease' OR `s1`.`fundingloantype` = 'new-lease')
  32.     ) AS `missing_vins`,
  33.     (SELECT COUNT(`s1`.`loannumber`)
  34.           FROM `cuactivity` AS `s1`
  35.  
  36.           LEFT OUTER JOIN `main_dealership` AS `d` ON `d`.`id` = `s1`.`dealer_id`
  37.           LEFT OUTER JOIN `data_one_vehiclemodel` AS `vmd` ON `vmd`.`vehicle_id` = `s1`.`vehicle_id`
  38.           LEFT OUTER JOIN `data_one_vehiclemake` AS `vmk` ON `vmk`.`id` = `vmd`.`make_id`
  39.           INNER JOIN `reports_importedfile` AS `fl` ON `fl`.`id` = `s1`.`imported_file`
  40.         WHERE CASE
  41.                 WHEN `credit_union` = 'Total' THEN `s1`.`lendername` LIKE '%'
  42.                 ELSE `s1`.`lendername` = `credit_union`
  43.               END
  44.               AND `s1`.`term` IS NULL
  45.               AND `s1`.`appreceiveddate` >= '2020-03-16 00:00:00' AND `s1`.`appreceiveddate` <= '2020-03-31 23:59:59'
  46.                AND (LOWER(`s1`.`lendername`) = 'island') AND (`s1`.`fundingloantype` = 'used-lease' OR `s1`.`fundingloantype` = 'new-lease')
  47.     ) AS `missing_term`,
  48.     (SELECT COUNT(`s1`.`loannumber`)
  49.           FROM `cuactivity` AS `s1`
  50.  
  51.           LEFT OUTER JOIN `main_dealership` AS `d` ON `d`.`id` = `s1`.`dealer_id`
  52.           LEFT OUTER JOIN `data_one_vehiclemodel` AS `vmd` ON `vmd`.`vehicle_id` = `s1`.`vehicle_id`
  53.           LEFT OUTER JOIN `data_one_vehiclemake` AS `vmk` ON `vmk`.`id` = `vmd`.`make_id`
  54.           INNER JOIN `reports_importedfile` AS `fl` ON `fl`.`id` = `s1`.`imported_file`
  55.         WHERE CASE
  56.                 WHEN `credit_union` = 'Total' THEN `s1`.`lendername` LIKE '%'
  57.                 ELSE `s1`.`lendername` = `credit_union`
  58.               END
  59.               AND `s1`.`dealer_id` IS NULL
  60.               AND `s1`.`appreceiveddate` >= '2020-03-16 00:00:00' AND `s1`.`appreceiveddate` <= '2020-03-31 23:59:59'
  61.                AND (LOWER(`s1`.`lendername`) = 'island') AND (`s1`.`fundingloantype` = 'used-lease' OR `s1`.`fundingloantype` = 'new-lease')
  62.     ) AS `missing_dealer`,
  63.     (SELECT COUNT(`s1`.`loannumber`)
  64.           FROM `cuactivity` AS `s1`
  65.  
  66.           LEFT OUTER JOIN `main_dealership` AS `d` ON `d`.`id` = `s1`.`dealer_id`
  67.           LEFT OUTER JOIN `data_one_vehiclemodel` AS `vmd` ON `vmd`.`vehicle_id` = `s1`.`vehicle_id`
  68.           LEFT OUTER JOIN `data_one_vehiclemake` AS `vmk` ON `vmk`.`id` = `vmd`.`make_id`
  69.           INNER JOIN `reports_importedfile` AS `fl` ON `fl`.`id` = `s1`.`imported_file`
  70.         WHERE CASE
  71.                 WHEN `credit_union` = 'Total' THEN `s1`.`lendername` LIKE '%'
  72.                 ELSE `s1`.`lendername` = `credit_union`
  73.               END
  74.               AND ((`s1`.`vehiclevin` = '') AND (`s1`.`vehicletrim` = '' OR `s1`.`vehicletrim` IS NULL))
  75.               AND `s1`.`appreceiveddate` >= '2020-03-16 00:00:00' AND `s1`.`appreceiveddate` <= '2020-03-31 23:59:59'
  76.                AND (LOWER(`s1`.`lendername`) = 'island') AND (`s1`.`fundingloantype` = 'used-lease' OR `s1`.`fundingloantype` = 'new-lease')
  77.     ) AS `missing_trim`,
  78.     (SELECT COUNT(`s1`.`loannumber`)
  79.           FROM `cuactivity` AS `s1`
  80.  
  81.           LEFT OUTER JOIN `main_dealership` AS `d` ON `d`.`id` = `s1`.`dealer_id`
  82.           LEFT OUTER JOIN `data_one_vehiclemodel` AS `vmd` ON `vmd`.`vehicle_id` = `s1`.`vehicle_id`
  83.           LEFT OUTER JOIN `data_one_vehiclemake` AS `vmk` ON `vmk`.`id` = `vmd`.`make_id`
  84.           INNER JOIN `reports_importedfile` AS `fl` ON `fl`.`id` = `s1`.`imported_file`
  85.         WHERE CASE
  86.                 WHEN `credit_union` = 'Total' THEN `s1`.`lendername` LIKE '%'
  87.                 ELSE `s1`.`lendername` = `credit_union`
  88.               END
  89.               AND (`s1`.`street` IS NULL OR `s1`.`street` = '')
  90.               AND `s1`.`appreceiveddate` >= '2020-03-16 00:00:00' AND `s1`.`appreceiveddate` <= '2020-03-31 23:59:59'
  91.                AND (LOWER(`s1`.`lendername`) = 'island') AND (`s1`.`fundingloantype` = 'used-lease' OR `s1`.`fundingloantype` = 'new-lease')
  92.     ) AS `missing_address`,
  93.     (SELECT COUNT(`s1`.`loannumber`)
  94.           FROM `cuactivity` AS `s1`
  95.  
  96.           LEFT OUTER JOIN `main_dealership` AS `d` ON `d`.`id` = `s1`.`dealer_id`
  97.           LEFT OUTER JOIN `data_one_vehiclemodel` AS `vmd` ON `vmd`.`vehicle_id` = `s1`.`vehicle_id`
  98.           LEFT OUTER JOIN `data_one_vehiclemake` AS `vmk` ON `vmk`.`id` = `vmd`.`make_id`
  99.           INNER JOIN `reports_importedfile` AS `fl` ON `fl`.`id` = `s1`.`imported_file`
  100.         WHERE CASE
  101.                 WHEN `credit_union` = 'Total' THEN `s1`.`lendername` LIKE '%'
  102.                 ELSE `s1`.`lendername` = `credit_union`
  103.               END
  104.               AND `s1`.`appreceiveddate` > `s1`.`approvaldate`
  105.               AND `s1`.`appreceiveddate` >= '2020-03-16 00:00:00' AND `s1`.`appreceiveddate` <= '2020-03-31 23:59:59'
  106.                AND (LOWER(`s1`.`lendername`) = 'island') AND (`s1`.`fundingloantype` = 'used-lease' OR `s1`.`fundingloantype` = 'new-lease')
  107.     ) AS `incorrect_dates`,
  108.     (SELECT COUNT(`s1`.`loannumber`)
  109.           FROM `cuactivity` AS `s1`
  110.  
  111.           LEFT OUTER JOIN `main_dealership` AS `d` ON `d`.`id` = `s1`.`dealer_id`
  112.           LEFT OUTER JOIN `data_one_vehiclemodel` AS `vmd` ON `vmd`.`vehicle_id` = `s1`.`vehicle_id`
  113.           LEFT OUTER JOIN `data_one_vehiclemake` AS `vmk` ON `vmk`.`id` = `vmd`.`make_id`
  114.           INNER JOIN `reports_importedfile` AS `fl` ON `fl`.`id` = `s1`.`imported_file`
  115.         WHERE CASE
  116.                 WHEN `credit_union` = 'Total' THEN `s1`.`lendername` LIKE '%'
  117.                 ELSE `s1`.`lendername` = `credit_union`
  118.               END
  119.               AND `s1`.`membertype` IS NULL
  120.               AND `s1`.`appreceiveddate` >= '2020-03-16 00:00:00' AND `s1`.`appreceiveddate` <= '2020-03-31 23:59:59'
  121.                AND (LOWER(`s1`.`lendername`) = 'island') AND (`s1`.`fundingloantype` = 'used-lease' OR `s1`.`fundingloantype` = 'new-lease')
  122.     ) AS `missing_membertype`,
  123.     (SELECT COUNT(`s1`.`loannumber`)
  124.           FROM `cuactivity` AS `s1`
  125.  
  126.           LEFT OUTER JOIN `main_dealership` AS `d` ON `d`.`id` = `s1`.`dealer_id`
  127.           LEFT OUTER JOIN `data_one_vehiclemodel` AS `vmd` ON `vmd`.`vehicle_id` = `s1`.`vehicle_id`
  128.           LEFT OUTER JOIN `data_one_vehiclemake` AS `vmk` ON `vmk`.`id` = `vmd`.`make_id`
  129.           INNER JOIN `reports_importedfile` AS `fl` ON `fl`.`id` = `s1`.`imported_file`
  130.         WHERE CASE
  131.                 WHEN `credit_union` = 'Total' THEN `s1`.`lendername` LIKE '%'
  132.                 ELSE `s1`.`lendername` = `credit_union`
  133.               END
  134.               AND (
  135.                 (`s1`.`capcost` IS NULL OR `s1`.`capcost` = 0) AND
  136.                 `s1`.`fundingloantype` IN ('new-lease', 'used-lease')
  137.               )
  138.               AND `s1`.`appreceiveddate` >= '2020-03-16 00:00:00' AND `s1`.`appreceiveddate` <= '2020-03-31 23:59:59'
  139.                AND (LOWER(`s1`.`lendername`) = 'island') AND (`s1`.`fundingloantype` = 'used-lease' OR `s1`.`fundingloantype` = 'new-lease')
  140.     ) AS `missing_capcost`,
  141.     (SELECT COUNT(`s1`.`loannumber`)
  142.           FROM `cuactivity` AS `s1`
  143.  
  144.           LEFT OUTER JOIN `main_dealership` AS `d` ON `d`.`id` = `s1`.`dealer_id`
  145.           LEFT OUTER JOIN `data_one_vehiclemodel` AS `vmd` ON `vmd`.`vehicle_id` = `s1`.`vehicle_id`
  146.           LEFT OUTER JOIN `data_one_vehiclemake` AS `vmk` ON `vmk`.`id` = `vmd`.`make_id`
  147.           INNER JOIN `reports_importedfile` AS `fl` ON `fl`.`id` = `s1`.`imported_file`
  148.         WHERE CASE
  149.                 WHEN `credit_union` = 'Total' THEN `s1`.`lendername` LIKE '%'
  150.                 ELSE `s1`.`lendername` = `credit_union`
  151.               END
  152.               AND (
  153.                 (`s1`.`bookvalue` IS NULL OR `s1`.`bookvalue` = 0) AND
  154.                 (`s1`.`msrp` IS NULL OR `s1`.`msrp` = 0)
  155.               )
  156.               AND `s1`.`appreceiveddate` >= '2020-03-16 00:00:00' AND `s1`.`appreceiveddate` <= '2020-03-31 23:59:59'
  157.                AND (LOWER(`s1`.`lendername`) = 'island') AND (`s1`.`fundingloantype` = 'used-lease' OR `s1`.`fundingloantype` = 'new-lease')
  158.     ) AS `missing_value`,
  159.     (SELECT COUNT(`s1`.`loannumber`)
  160.           FROM `cuactivity` AS `s1`
  161.  
  162.           LEFT OUTER JOIN `main_dealership` AS `d` ON `d`.`id` = `s1`.`dealer_id`
  163.           LEFT OUTER JOIN `data_one_vehiclemodel` AS `vmd` ON `vmd`.`vehicle_id` = `s1`.`vehicle_id`
  164.           LEFT OUTER JOIN `data_one_vehiclemake` AS `vmk` ON `vmk`.`id` = `vmd`.`make_id`
  165.           INNER JOIN `reports_importedfile` AS `fl` ON `fl`.`id` = `s1`.`imported_file`
  166.         WHERE CASE
  167.                 WHEN `credit_union` = 'Total' THEN `s1`.`lendername` LIKE '%'
  168.                 ELSE `s1`.`lendername` = `credit_union`
  169.               END
  170.               AND (
  171.                 (`s1`.`fundingdate` = '' OR `s1`.`fundingdate` IS NULL) AND
  172.                 `s1`.`loanstatus` IN ('booked')
  173.               )
  174.               AND `s1`.`appreceiveddate` >= '2020-03-16 00:00:00' AND `s1`.`appreceiveddate` <= '2020-03-31 23:59:59'
  175.                AND (LOWER(`s1`.`lendername`) = 'island') AND (`s1`.`fundingloantype` = 'used-lease' OR `s1`.`fundingloantype` = 'new-lease')
  176.     ) AS `missing_fund_date`
  177.     FROM (
  178.       SELECT DISTINCT `lendername` AS `credit_union` FROM `cuactivity`
  179.       UNION ALL SELECT 'Total' AS `lendername`) AS `credit_union`;

Reply to "Untitled"

Here you can reply to the paste above