Geek Answers Handbook

How to choose consecutive dates?

I have a query that looks something like this:

SELECT DISTINCT S.date1 AS date1, S.date2 AS date2, S.period AS period FROM portfolio.scenario S WHERE S.date1 >= '2010-06-01' AND S.date2 <= '2010-07-01' AND S.period = 'WEEK'

Output:

 "2010-06-01 00:00:00","2010-06-08 00:00:00",WEEK "2010-06-02 00:00:00","2010-06-09 00:00:00",WEEK "2010-06-03 00:00:00","2010-06-10 00:00:00",WEEK "2010-06-04 00:00:00","2010-06-11 00:00:00",WEEK "2010-06-07 00:00:00","2010-06-14 00:00:00",WEEK "2010-06-08 00:00:00","2010-06-15 00:00:00",WEEK "2010-06-09 00:00:00","2010-06-16 00:00:00",WEEK "2010-06-10 00:00:00","2010-06-17 00:00:00",WEEK "2010-06-11 00:00:00","2010-06-18 00:00:00",WEEK "2010-06-14 00:00:00","2010-06-21 00:00:00",WEEK "2010-06-15 00:00:00","2010-06-22 00:00:00",WEEK "2010-06-16 00:00:00","2010-06-23 00:00:00",WEEK "2010-06-17 00:00:00","2010-06-24 00:00:00",WEEK "2010-06-18 00:00:00","2010-06-25 00:00:00",WEEK "2010-06-21 00:00:00","2010-06-28 00:00:00",WEEK "2010-06-22 00:00:00","2010-06-29 00:00:00",WEEK "2010-06-23 00:00:00","2010-06-30 00:00:00",WEEK "2010-06-24 00:00:00","2010-07-01 00:00:00",WEEK

I need only the most consistent dates starting at the earliest date1;

  "2010-06-01 00:00:00","2010-06-08 00:00:00",WEEK "2010-06-08 00:00:00","2010-06-15 00:00:00",WEEK "2010-06-15 00:00:00","2010-06-22 00:00:00",WEEK "2010-06-22 00:00:00","2010-06-29 00:00:00",WEEK

Appreciate any help on the topic :)

+4
source share
2 answers

Using performance suggestions is inefficient for performance, but this will do the trick while S.period stays a week.

 SELECT DISTINCT S.date1 AS date1, S.date2 AS date2, S.period AS period FROM portfolio.scenario S WHERE S.date1 >= '2010-06-01' AND S.date2 <= '2010-07-01' AND S.period = 'WEEK' AND DAYOFWEEK(S.date1) = DAYOFWEEK('2010-06-01');
+4
source

EDIT Sorry, I did not look at your desired result close enough. This JOIN will sort those dates that have no ending and start on the same day.

You need to cross join the table like this:

 SELECT DISTINCT S.date1 AS date1, S.date2 AS date2, S.period AS period FROM portfolio.scenario S, portfolio.scenario T WHERE S.date1 >= '2010-06-01' AND S.date2 <= '2010-07-01' AND S.period = 'WEEK' AND ( T.date2 = S.date1 OR S.date1 = '2010-06-01' )

The last condition requires that OR include the first row, since it cannot be joined using T.date2 = S.date1 .

Here you go.

0
source

More articles:


All Articles