Could you please help me to correct the script as below:
set LastDay=SELECT LAST_DAY(SYSDATE) FROM dual;
set FirstDay=Select trunc((sysdate),'month') as First_day_of_month from dual;
SELECT count(*) FROM tab1 g , h.ab1 LEFT JOIN tab2 h ON g.bba = h.bba
WHERE 1 = 1
AND g.DATE_ BETWEEN TO_DATE('FirstDay', 'YYYYMMDD') AND TO_DATE('LastDay', 'YYYYMMDD');
CodePudding user response:
In Oracle, a DATE
ALWAYS has year, month, day, hour, minute and second components. Using LAST_DAY(SYSDATE)
only sets the year-month-day component of a date and does not modify the time component so if you filter from the start of the month to LAST_DAY(SYSDATE)
then you will exclude any values from the last day of the month with a time component between the current time and 23:59:59.
What you want is to use:
SELECT count(*)
FROM tab1 g
CROSS JOIN h.ab1 -- your query is confusing around the joins
-- and may need fixing
LEFT JOIN tab2 h
ON g.bba = h.bba
WHERE g.DATE_ >= TRUNC(SYSDATE, 'MM')
AND g.DATE_ < ADD_MONTHS(TRUNC(SYSDATE, 'MM'), 1);
CodePudding user response:
That's just
select count(*)
from tab1 g left join tab2 h on g.bba = h.bba
and g.date_ between trunc(sysdate, 'month') and last_day(sysdate);
Isn't it?