Posto izgleda da ima neki patern, i predpostavljam da radis sa PHP, mozda je bolje da generises query njime, nesto ovako:
Code:
$begin = new DateTime( "2021-01-29" );
$end = new DateTime( "2021-01-27" );
$appendWhere = [];
for($i = $begin; $i >= $end; $i->modify('-1 day')){
$e = clone($i);
$e->modify('-1 day');
$appendWhere[] = " AND created_at NOT BETWEEN '" . $i->format('Y-m-d')." 07:00:00' AND '" . $i->format('Y-m-d') ." 17:00:00'";
$appendWhere[] = " AND created_at NOT BETWEEN '" . $e->format('Y-m-d')." 21:00:00' AND '" . $i->format('Y-m-d') ." 06:00:00'";
}
$sql = "
SELECT tab1, tab2, COUNT(*) AS count_tab, created_at, max(created_at) as max_tab
FROM cust_tab
WHERE created_at between adddate(now(),-6) and now()
AND tab2 NOT LIKE '%In%'
AND tab1 NOT LIKE '1_'
AND tab1 NOT LIKE ''
" . implode ("\n", $appendWhere) . "
GROUP BY tab2,tab1
HAVING (COUNT(*) > 3)
ORDER BY count_tab DESC, max_tab DESC
LIMIT 10
";
Dobices tipa:
Code:
$sql = "
SELECT tab1, tab2, COUNT(*) AS count_tab, created_at, max(created_at) as max_tab
FROM cust_tab
WHERE created_at between adddate(now(),-6) and now()
AND tab2 NOT LIKE '%In%'
AND tab1 NOT LIKE '1_'
AND tab1 NOT LIKE ''
AND created_at NOT BETWEEN '2021-01-29 07:00:00' AND '2021-01-29 17:00:00'
AND created_at NOT BETWEEN '2021-01-28 21:00:00' AND '2021-01-29 06:00:00'
AND created_at NOT BETWEEN '2021-01-28 07:00:00' AND '2021-01-28 17:00:00'
AND created_at NOT BETWEEN '2021-01-27 21:00:00' AND '2021-01-28 06:00:00'
AND created_at NOT BETWEEN '2021-01-27 07:00:00' AND '2021-01-27 17:00:00'
AND created_at NOT BETWEEN '2021-01-26 21:00:00' AND '2021-01-27 06:00:00'
GROUP BY tab2,tab1
HAVING (COUNT(*) > 3)
ORDER BY count_tab DESC, max_tab DESC
LIMIT 10
";
[Ovu poruku je menjao ntesic dana 30.01.2021. u 19:38 GMT+1]