PostgreSQL Update with date range

Question

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.

What does it mean "won't work"? Add input parameters and expected result. — Andronicus
– Andronicus, Commented Oct 9, 2020 at 0:23

Gordon Linoff · Accepted Answer · 2020-10-09 01:32:05Z

2

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;

edited Oct 9, 2020 at 1:32

answered Oct 9, 2020 at 0:51

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

quba Over a year ago

Got : syntax error at or near "(" LINE 2: SELECT employee_id, end_date SUM(end_date - start_date...

Collectives™ on Stack Overflow

PostgreSQL Update with date range

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related