Sub-set based on 1st and 2nd occurrence of day of week?

Moderator: NorbertKrupa

Post Reply
Brett
Beginner
Beginner
Posts: 49
Joined: Fri Oct 11, 2013 1:19 am

Sub-set based on 1st and 2nd occurrence of day of week?

Post by Brett » Sun Jul 06, 2014 6:34 pm

Hi,

What's the best performing way that I can write a query to only give me the data for the first and second Monday of every month? The data in my table has rows for every day, but I (actually my boss) only wants to see the first and second Mondays :)

NorbertKrupa
GURU
GURU
Posts: 527
Joined: Tue Oct 22, 2013 9:36 pm
Location: Chicago, IL
Contact:

Re: Sub-set based on 1st and 2nd occurrence of day of week?

Post by NorbertKrupa » Sun Jul 06, 2014 11:37 pm

Typically this is handled with a calendar table. Otherwise, create a table with dates that match your first & second Monday criteria (have an intern make it), then join to your source table. I would replicate the table with dates on all nodes.
Checkout vertica.tips for more Vertica resources.

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1825
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: Sub-set based on 1st and 2nd occurrence of day of week?

Post by JimKnicely » Tue Jul 08, 2014 2:02 pm

Brett,

I think that norbertk is right about a calendar or date dimension table being the best solution, but, here might be a simple solution you might use:

Code: Select all

dbadmin=> SELECT the_date, TO_CHAR(the_date, 'Day') dow
dbadmin->   FROM hmm
dbadmin->  ORDER BY the_date;
  the_date  |    dow
------------+-----------
 2014-06-01 | Sunday
 2014-06-02 | Monday
 2014-06-04 | Wednesday
 2014-06-09 | Monday
 2014-06-14 | Saturday
 2014-06-30 | Monday
 2014-07-04 | Friday
 2014-07-07 | Monday
 2014-07-11 | Friday
 2014-07-14 | Monday
 2014-07-20 | Sunday
(11 rows)

Code: Select all

dbadmin=> SELECT the_date, TO_CHAR(the_date, 'Day')
dbadmin->   FROM hmm WHERE the_date IN (NEXT_DAY(TRUNC(the_date, 'Month'), 'MON'), NEXT_DAY(NEXT_DAY(TRUNC(the_date, 'Month'), 'MON'), 'MON'))
dbadmin->  ORDER BY the_date;
  the_date  |  TO_CHAR
------------+-----------
 2014-06-02 | Monday
 2014-06-09 | Monday
 2014-07-07 | Monday
 2014-07-14 | Monday
(4 rows)
Jim Knicely

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

Post Reply

Return to “New to Vertica SQL”