query to run billing-CodePudding

I have a monthly billing report that runs against a huge table like

select * from where date between (1-apr-2022) and (10-apr-2022) and I do it three time to cover the whole month, what I trying to do is to run it one time or to join the tree results at one

CodePudding user response：

What's wrong with fetching data for the whole April at once?

select *
from your_table
where date_column between date '2022-04-01' and date '2022-04-30';

column name most probably isn't date (as your example shows; that name is reserved for datatype)
if that column's datatype really is date (should be), then don't compare it to strings. 1-apr-2022 is wrong anyway, you missed to use single quotes. Use date datatype values as date literals (as my example shows) or use to_date function with appropriate format model
it looks as if that date column contains dates only (with no time component). If there is time, then boundaries have to be modified as you'll miss the whole last day

Also:

why do you run query 3 times anyway? Because the table is "huge"? How huge is it?
did you gather statistics (and do it regularly)?
is date column (used in where clause) indexed?
what does explain plan say?

CodePudding user response：

Just query the table with the entire month range:

SELECT *
FROM   your_table
WHERE  date_column >= :month_start
AND    date_column <  ADD_MONTHS(:month_start, 1)

Or, with hardcoded dates:

SELECT *
FROM   your_table
WHERE  date_column >= DATE '2022-04-01'
AND    date_column <  DATE '2022-05-01'

Do not use BETWEEN with a range from the first to the last day like:

SELECT *
FROM   your_table
WHERE  date_column BETWEEN DATE '2022-04-01' AND DATE '2022-04-30';

Because a DATE is a binary data type that consists of 7 bytes representing: century, year-of-century, month. day, hour, minute and second. It ALWAYS has those components (some client applications may default to only showing the date component; but that does not mean the time component does not exist, it just is not shown).

This means that you will get results between 2022-04-01 00:00:00 and 2022-04-30 00:00:00 and you will miss results between 2022-04-30 00:00:01 and 2022-04-30 23:59:59.

You need to select the entire range that ends before midnight of the first day of the next month.