0
votes

I'm trying to improve my execution time for my query.

I have to extract rows where the last day of the month is between the start date and the end date.

In my table, the data is setup by weeks example : start 2018-11-11 end 2018-11-17, start 2018-11-18 end 2018-11-24 and so on.

my query looks like this :

select field1, field2,
max(case when '2018-01-31' between start and end then 1 else 0 end) as M1,
max(case when '2018-02-28' between start and end then 1 else 0 end) as M2,
....
max(case when '2018-12-31' between start and end then 1 else 0 end) as M12
from tableX
where start between '2018-01-01' and '2018-12-31'
group by field1, field2

So I only want to consider rows that includes the end of the months. Any Idea how I could improve this ?

I'm thinking of doing something like this in the where clause :

where '2018-01-31' between start and end
or '2018-02-28' between start and end
...
or '2018-12-31' between start and end

is it possible to do something like this :

where ('2018-01-31','2018-02-28',....,'2018-12-31') between start and end

Thanks

Franck

2
1). If a row if for the time range '2018-01-01' to '2018-01-28' it won't appear in any of your columns. Is that correct? 2). If a row is for the time range '2018-01-01' to'2019-01-01' is will appear in all of your columns. Is that correct? 3). What is the maximum span of time a single row would ever cover? - MatBailie
What is your Db2-server operating-system and Db2-server version? What indexes exist? Have you used any of the query-optimization tools for your platform? - mao
Every row represent a week. What we want to do is check if an account exists at the end of the month. So I want to have for every month the number of account for the whole year - FranckH
For the DB2 server, I'm not able to answer that question. Sorry :( - FranckH

2 Answers

3
votes

Depending on the Db2 version and platform the function LAST_DAY could do the trick. Consider something like this:

where LAST_DAY(start) between start and end
0
votes

The accepted answer minimises the number of rows being processed.

where LAST_DAY(start) between start and end

This, however, will still process rows outside of the year you want. So you should still use boundary conditions. Such as...

where LAST_DAY(start) between start and end
  and start between '2018-01-01' and '2018-12-31'

That still has a downside that no index can satisfy the LAST_DAY() condition and a scan of the table or index will occur.

With the information you gave in comments, use of an index lookup can be re-established.

Each row is always for a week, and starts on a Monday. So, for any given month end, if we work back to the Monday, we get the start that matches.

WHERE
    start IN (
        DATE_TRUNC(WEEK, LAST_DAY('2018-01-01')), -- find last day in jan, round down to Monday
        DATE_TRUNC(WEEK, LAST_DAY('2018-02-01')),
        DATE_TRUNC(WEEK, LAST_DAY('2018-03-01')),
        DATE_TRUNC(WEEK, LAST_DAY('2018-04-01')),
        DATE_TRUNC(WEEK, LAST_DAY('2018-05-01')),
        DATE_TRUNC(WEEK, LAST_DAY('2018-06-01')),
        DATE_TRUNC(WEEK, LAST_DAY('2018-07-01')),
        DATE_TRUNC(WEEK, LAST_DAY('2018-08-01')),
        DATE_TRUNC(WEEK, LAST_DAY('2018-09-01')),
        DATE_TRUNC(WEEK, LAST_DAY('2018-10-01')),
        DATE_TRUNC(WEEK, LAST_DAY('2018-11-01')),
        DATE_TRUNC(WEEK, LAST_DAY('2018-12-01'))
    )

That's significantly longer.

That can be reduced by using a calendar table, with a list of dates and meta-data about those dates that help you do quick searches.

For now, create an index on start and try the explicit where clause above. It should be even faster than what you've tried so far.


EDIT:

Similarly, you could write your CASE expressions is a way that's "easier to read for a human"...

MAX(CASE WHEN start = DATE_TRUNC(WEEK, LAST_DAY('2018-01-01')) THEN 1 ELSE 0 END)   AS M01