CODEWITHSUNDEEP
sqlpostgresqlcommon-table-expression
quba
quba

Reputation: 123

PostgreSQL Update with date range

I have this statement that works, but it's unbridled, as in it SUMs up the whole column, I want introduce date range but I'm getting an error.

HERE IS WHAT IS WORKING

UPDATE payroll_employee e
    SET hours = l.total
FROM (SELECT employee, SUM(end_date - start_date) AS total
    FROM payroll_timelog
    GROUP BY employee) l
WHERE e.id = l.employee

I want to introduce a date filter. Then I tried this CTE,

WITH cte AS (
  SELECT employee_id, end_date SUM(end_date - start_date) AS total
    FROM payroll_timelog
    WHERE employee_id = 1, AND end_date > 2020-09-01
)
UPDATE payroll_employee e
   SET hours = total
FROM cte
WHERE e.id = 1;

Tried casting date to 2020-09-01::date, 2020-09-01::timestamp etc, still won't work. any help will be appreciated.

Upvotes: 2

Views: 421

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271171

I think you just have syntax errors:

WITH cte AS (
      SELECT employee_id, SUM(end_date - start_date) AS total
      FROM payroll_timelog
      WHERE employee_id = 1 AND end_date > '2020-09-01'
      GROUP BY employee_id
)
UPDATE payroll_employee e
   SET hours = cte.total
FROM cte
WHERE e.id = 1;

Upvotes: 2

Related Questions