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
'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