Good morning!
Can someone help me write a query that can be used to get the following:
1. Total number of days
2. Total number of week days (M-F)
3. Total number of weekend days (S-Su)
...that occur between two year ranges (i.e. 2012 - 2015).
I am not sure how to handle leap years.
Thanks in advance!
Query to get # of days in mutiple year range
Moderator: NorbertKrupa
-
- Intermediate
- Posts: 163
- Joined: Tue Jun 19, 2012 2:44 pm
- Location: Rio de Janeiro
- Contact:
Re: Query to get # of days in mutiple year range
Wrong answer ! I totally misread your question.
Last edited by adrian.oprea on Fri Mar 27, 2015 12:36 pm, edited 1 time in total.
trying so hard !!!
-
- GURU
- Posts: 527
- Joined: Tue Oct 22, 2013 9:36 pm
- Location: Chicago, IL
- Contact:
Re: Query to get # of days in mutiple year range
A calendar table would be useful in these types of questions
Checkout vertica.tips for more Vertica resources.
- JimKnicely
- Site Admin
- Posts: 1825
- Joined: Sat Jan 21, 2012 4:58 am
- Contact:
Re: Query to get # of days in mutiple year range
I completely agree with norbertk in that a date dimension table is needed here.
But just for fun, here is one way you can do it in Vertica:
But just for fun, here is one way you can do it in Vertica:
Code: Select all
dbadmin=> SELECT COUNT(*) AS total_days,
dbadmin-> SUM(CASE WHEN TO_CHAR(ts, 'D')::INT BETWEEN 2 AND 6 THEN 1 ELSE 0 END) AS total_week_days,
dbadmin-> SUM(CASE WHEN TO_CHAR(ts, 'D')::INT IN (1, 7) THEN 1 ELSE 0 END) AS total_weekend_days
dbadmin-> FROM (SELECT ts
dbadmin(> FROM (SELECT '2012-01-01'::TIMESTAMP AS tm
dbadmin(> UNION ALL
dbadmin(> SELECT '2015-12-31'::TIMESTAMP AS tm) AS t
dbadmin(> TIMESERIES ts AS '1 day' OVER (ORDER BY tm)) foo;
total_days | total_week_days | total_weekend_days
------------+-----------------+--------------------
1461 | 1044 | 417
(1 row)
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.
Re: Query to get # of days in mutiple year range
Thanks, all! That query is pretty cool knicely87. It works fast enough for my needs.