Postgresql – Counting working days in a month

countdatepostgresqlrange-types

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;

Best Answer

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

Related Solutions

SQL Server – How to Find Number of Days Since Beginning of Fiscal Month

This will do it:

DECLARE @d DATE = '20140502', @dm TINYINT = 26;
DECLARE @Start_Of_Fiscal_Month DATE

SET @Start_Of_Fiscal_Month = DATEADD(DAY, @dm-1, DATEADD(MONTH, -1 + DATEPART(dd, @d)/26, DATEADD(DAY, 1-DAY(@d), @d)));
SELECT @Start_Of_Fiscal_Month
SELECT DATEDIFF(dd, @Start_Of_Fiscal_Month, @d) + 1

Postgresql – Aggregate sales of the past 12 months for the current row date

Something like this should work..

-- IN A CTE
-- Grab the idclient, and the monthly range needed
-- We need the range because you can't sum over NULL (yet, afaik).
WITH idclient_month AS (
  SELECT idclient, month_transac
  FROM (
    SELECT idclient, min(month_transac), max(month_transac)
    FROM foo
    GROUP BY idclient
  ) AS t
  CROSS JOIN LATERAL generate_series(min::date, max::date, '1 month')
    AS gs(month_transac)
)
-- If we move this where clause down the rows get filtered /before/ the window function
SELECT *
FROM (

  SELECT
    idclient,
    month_transac,
    monthly_sales,
    sum(monthly_sales) OVER (
      PARTITION BY idclient
      ORDER BY month_transac
      ROWS 12 PRECEDING
    )
      - monthly_sales
      AS sales_ttm

  -- Here, we sum up the sales by idclient, and month
  -- We coalesce to 0 so we can use this in a window function
  FROM (
    SELECT idclient, month_transac, coalesce(sum(sales), 0) AS monthly_sales
    FROM foo
    RIGHT OUTER JOIN idclient_month
      USING (idclient,month_transac)
    GROUP BY idclient, month_transac
    ORDER BY idclient, month_transac
  ) AS t

) AS g
WHERE g.monthly_sales > 0;

Here we

Calculate the date-range for the idclient in a CTE.

SELECT idclient, month_transac
FROM (
  SELECT idclient, min(month_transac), max(month_transac)
  FROM foo
  GROUP BY idclient
) AS t
CROSS JOIN LATERAL generate_series(min::date, max::date, '1 month')
  AS gs(month_transac)
 idclient  |     month_transac      
-----------+------------------------
 511656A75 | 2010-06-01 00:00:00-05
 511656A75 | 2010-07-01 00:00:00-05
 511656A75 | 2010-08-01 00:00:00-05
 511656A75 | 2010-09-01 00:00:00-05
 511656A75 | 2010-10-01 00:00:00-05
 511656A75 | 2010-11-01 00:00:00-05
 511656A75 | 2010-12-01 00:00:00-06
 511656A75 | 2011-01-01 00:00:00-06
 [....]

RIGHT OUTER that CTE to a our sample dataset. We do this so we grow our sample dataset and we have entries with monthly_sales = 0 where needed.
Use a window function that uses windows over ROWS 12 PRECEDING. That's the key. That's the past 12 months. The window function can't operate on rows that are null, so we set them to 0 before we get to this step.
Select just the rows where monthly_sales > 0. We have to do this after the window function so as not to much with what is available for calculation (the window).

Output,

 idclient  |     month_transac      | monthly_sales | sales_ttm 
-----------+------------------------+---------------+-----------
 511656A75 | 2010-06-01 00:00:00-05 |         68.57 |      0.00
 511656A75 | 2010-07-01 00:00:00-05 |         88.63 |     68.57
 511656A75 | 2010-08-01 00:00:00-05 |         94.91 |    157.20
 511656A75 | 2010-09-01 00:00:00-05 |         70.66 |    252.11
 511656A75 | 2010-10-01 00:00:00-05 |         28.84 |    322.77
 511656A75 | 2015-10-01 00:00:00-05 |         85.00 |      0.00
 511656A75 | 2015-12-01 00:00:00-06 |        114.42 |     85.00
 511656A75 | 2016-01-01 00:00:00-06 |        137.08 |    199.42
 511656A75 | 2016-03-01 00:00:00-06 |        172.92 |    336.50
 511656A75 | 2016-04-01 00:00:00-05 |        125.00 |    509.42
 511656A75 | 2016-05-01 00:00:00-05 |        127.08 |    634.42
 511656A75 | 2016-06-01 00:00:00-05 |        104.17 |    761.50
 511656A75 | 2016-07-01 00:00:00-05 |         98.22 |    865.67
 511656A75 | 2016-08-01 00:00:00-05 |         37.08 |    963.89
 511656A75 | 2016-10-01 00:00:00-05 |        108.33 |   1000.97
 511656A75 | 2016-11-01 00:00:00-05 |        104.17 |   1024.30
 511656A75 | 2017-01-01 00:00:00-06 |        201.67 |   1014.05
(17 rows)

Best Answer

Related Solutions

SQL Server – How to Find Number of Days Since Beginning of Fiscal Month

Postgresql – Aggregate sales of the past 12 months for the current row date

Related Question