Counting working days in a month

Question

I wrote this query that suppose to count the working days in a month but I get the following error:

[42883] ERROR: operator does not exist: double precision + interval Hinweis: No operator matches the given name and argument types. You might need to add explicit type casts. Position: 305

The date is a timestamp and this is the function below serves as the denominator to normalize sales:

(count(((date_part('month',date))+ Interval '1 month'- Interval '1 day')-(date_part('month',date)))
           filter (where date_part('dow', date) not in (6,0)))

In the end I will have:

select date_part('month', date) as month, 
sum(sales)/denominator as Monthly_noralized_sales
from sales;

Answer 1

I'm not sure if that is what you're trying to achieve, but to count working days you need a range of dates.

IMHO the best way to deal with dates is by using a calendar table. Keep in mine that you need to add holidays and days off to your calendar. You can find a lot of samples googling a bit.

If you don't have/want to use a calendar table, you can take advantage of generate_series function.

create table t (dt timestamp);
insert into t values ('20200110'),('20200203'),('20200715');

select 
    date_part('month', dts) mt,
    count(*) as wd
from
    (select
        generate_series(date_trunc('month', dt)::date,
                        (date_trunc('month', dt)::date 
                                    + interval '1 month - 1 day')::date,
                        interval '1 day') dts
     from 
        t) t2
where
    date_part('dow', dts) <> 6
group by
    date_part('month', dts);

mt | wd
:- | -:
7  | 27
1  | 27
2  | 24

db<>fiddle here