Query to get # of days in mutiple year range

[sam]

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!

[adrian.oprea]

Wrong answer ! I totally misread your question.

[NorbertKrupa]

A calendar table would be useful in these types of questions

[JimKnicely]

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:

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)

[sam]

Thanks, all! That query is pretty cool knicely87. It works fast enough for my needs.