เริ่มต้น จัดการหาวันที่เกี่ยวข้องกันทั้งหมดก่อน Where clause
Code (SQL)
select * from table
where (period_str between '$date_str' and '$date_end')
or (period_end between '$date_str' and '$date_end')
or ('$date_str' between period_str and period_end)
or ('$date_end' between period_str and period_end)
นำ record ที่ได้ มาหา ระยะห่างของวัน Code (SQL)
select
((period_str<='$date_str')*(DATEDIFF(period_end, '$date_str')+1), # first_period
(('$date_end'<=period_end)*(DATEDIFF('$date_end', period_str)+1), # last_period
(('$date_str'<=period_str and period_end<='$date_end')*(DATEDIFF(period_end,period_str)+1), # mid_period
baht
from table
where (period_str between '$date_str' and '$date_end')
or (period_end between '$date_str' and '$date_end')
or ('$date_str' between period_str and period_end)
or ('$date_end' between period_str and period_end)
นำมา รวมเป็น จำนวนเงิน Code (SQL)
select
sum(
(
((period_str<='$date_str')*(DATEDIFF(period_end, '$date_str')+1)
(('$date_end'<=period_end)*(DATEDIFF('$date_end', period_str)+1)+
(('$date_str'<=period_str and period_end<='$date_end')*(DATEDIFF(period_end,period_str)+1)
) * baht
) as total
from table
where (period_str between '$date_str' and '$date_end')
or (period_end between '$date_str' and '$date_end')
or ('$date_str' between period_str and period_end)
or ('$date_end' between period_str and period_end)
Load balance : Server 05
