DROP TEMPORARY TABLE IF EXISTS adunits_to_disable;
CREATE TEMPORARY TABLE adunits_to_disable
SELECT DISTINCT sa.site_area_id, bsa.user_id, sa.`create_date`, bsa.adv_expense_gbp_30, bsa.requests_30 FROM rabota_db.site_area sa
JOIN bi.site_area bsa ON bsa.site_area_id = sa.site_area_id
LEFT JOIN rabota_db.attached_site_areas basa ON sa.site_area_id = basa.site_area_id # with attached site area
LEFT JOIN bi.site_area asai ON asai.site_area_id = basa.attached_site_area_id # attached site areas info
WHERE
(
(
basa.site_area_id IS NOT NULL # is base area
AND ((asai.adv_expense_gbp_30 = 0 #and have no attached areas with stats
AND asai.requests_30 = 0 )
OR asai.site_area_id IS NULL) # or have no attached areas
)
OR
(
basa.site_area_id IS NULL # is attached area itself
)
)
AND bsa.adv_expense_gbp_30 = 0
AND bsa.requests_30 = 0
AND sa.`create_date` < ( NOW() - INTERVAL 3 MONTH )
AND bsa.user_id IN (
143718,
130905,
142171,
143779,
143174,
111441,
145613,
145397,
143885,
103491,
119997,
142788,
144892,
145417,
128837,
116214,
145592,
141408,
143924,
114289,
125676,
143727,
128317,
117170,
130221,
127672,
113821,
122065,
145832,
118982,
132210,
130273,
136691,
122345,
140542,
129927,
108350,
128603,
142114,
142210,
146294,
125922,
143448,
114096,
146466,
123677,
124567,
127942,
130955,
134435,
143263,
144357,
111604,
140209,
102556,
129416,
131176,
141405,
141406,
145591,
108589,
141407,
117333,
122213,
123741,
126356,
140846,
143637,
143764,
144135,
146308,
116989,
109931,
111040,
118339,
120761,
126239,
131337,
132502,
134210,
141900,
143139,
108973,
113159,
142135
);
UPDATE site_area sa
JOIN adunits_to_disable dsa ON sa.`site_area_id` = dsa.`site_area_id`
SET sa.active = 0,
sa.modify_date = NOW() #,
#sa.deleted = 1
;
Comments
0 B
|0 👍
/0 👎
0 B
|0 👍
/0 👎