I am hoping someone can help me with this query. If I only use one select it will work but slow. When I use all of them it times out and crashes.
SELECT
job_ID,
shop_provided,
weld_type,
(
SELECT
COUNT(vt_date) AS `COUNT(vt_date)`
FROM shopweldlog sw2
WHERE (shot_req NOT LIKE 'C/O,QLF') AND (shot_req NOT LIKE 'C/O') AND (vt_date > DATE_SUB(NOW(), INTERVAL 1 DAY))
AND sw1.shop_provided = sw2.shop_provided AND sw1.weld_type = sw2.weld_type AND sw1.job_id = sw2.job_id
)AS welds_today,
(
SELECT
COUNT(vt_date) AS `COUNT(vt_date)`
FROM shopweldlog sw2
WHERE (shot_req NOT LIKE 'C/O,QLF') AND (shot_req NOT LIKE 'C/O') AND (vt_date) = CURDATE() - INTERVAL 1 DAY
AND sw1.shop_provided = sw2.shop_provided AND sw1.weld_type = sw2.weld_type AND sw1.job_id = sw2.job_id
)AS welds_yesterday,
(
SELECT
COUNT(vt_date) AS `COUNT(vt_date)`
FROM shopweldlog sw2
WHERE (shot_req NOT LIKE 'C/O,QLF') AND (shot_req NOT LIKE 'C/O') AND ( YEARWEEK(vt_date) = YEARWEEK(CURRENT_DATE))
AND sw1.shop_provided = sw2.shop_provided AND sw1.weld_type = sw2.weld_type AND sw1.job_id = sw2.job_id
)AS weekly_welds,
(
SELECT
COUNT(vt_date) AS `COUNT(vt_date)`
FROM shopweldlog sw2
WHERE (shot_req NOT LIKE 'C/O,QLF') AND (shot_req NOT LIKE 'C/O') AND YEARWEEK(vt_date ) = YEARWEEK(CURRENT_DATE - INTERVAL 7 DAY)
AND sw1.shop_provided = sw2.shop_provided AND sw1.weld_type = sw2.weld_type AND sw1.job_id = sw2.job_id
)AS last_week_welds,
(
SELECT
COUNT(vt_date) AS `COUNT(vt_date)`
FROM shopweldlog sw2
WHERE (shot_req NOT LIKE 'C/O,QLF') AND (shot_req NOT LIKE 'C/O')AND YEAR(vt_date) = YEAR(CURDATE()) AND MONTH(vt_date) = MONTH(CURDATE())
AND sw1.shop_provided = sw2.shop_provided AND sw1.weld_type = sw2.weld_type AND sw1.job_id = sw2.job_id
)AS monthly_welds,
(
SELECT
COUNT(vt_date) AS `COUNT(vt_date)`
FROM shopweldlog sw2
WHERE (shot_req NOT LIKE 'C/O,QLF') AND (shot_req NOT LIKE 'C/O')AND YEAR(vt_date) = YEAR(CURRENT_DATE - INTERVAL 1 MONTH)
AND MONTH(vt_date) = MONTH(CURRENT_DATE - INTERVAL 1 MONTH)
AND sw1.shop_provided = sw2.shop_provided AND sw1.weld_type = sw2.weld_type AND sw1.job_id = sw2.job_id
)AS last_months_welds
FROM shopweldlog sw1
GROUP BY job_ID, shop_provided, weld_type