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
Sub-set based on 1st and 2nd occurrence of day of week?
Moderator: NorbertKrupa
-
- 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?
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.
- JimKnicely
- 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?
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:
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
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.