2

I have a table employee in Postgres:

Query:

SELECT DISTINCT month_last_date,number_of_cases,reopens,csat
FROM employee
WHERE month_last_date >=(date('2017-01-31') - interval '6 month')
AND   month_last_date <= date('2017-01-31')
AND agent_id='analyst' 
AND name='SAM';

Output:

Output I get

But if data is not in table for other month I want column value as 0.

Required output

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
prashant srivastava
  • 21
  • 1
  • 2

2 Answers2

2

Generate all dates you are interested in, LEFT JOIN to the table and default to 0 with COALESCE:

SELECT DISTINCT  -- see below
       i.month_last_date
     , COALESCE(number_of_cases, 0) AS number_of_cases  -- see below
     , COALESCE(reopens, 0)         AS reopens
     , COALESCE(csat, 0)            AS csat
FROM  (
   SELECT date '2017-01-31' - i * interval '1 mon' AS month_last_date
   FROM   generate_series(0, 5) i  -- see below
   ) i
LEFT   JOIN employee e ON e.month_last_date = i.month_last_date
                      AND e.agent_id = 'analyst'  -- see below
                      AND e.name = 'SAM';

Notes

If you add or subtract an interval of 1 month and the same day does not exist in the target month, Postgres defaults to the latest existing day of that moth. So this works as desired, you get the last day of each month:

SELECT date '2017-12-31' - i * interval '1 mon'  -- note 31
FROM   generate_series(0,11) i;

But this does not, you'd get the 28th of each month:

SELECT date '2017-02-28' - i * interval '1 mon'  -- note 28
FROM   generate_series(0,11) i;

The safe alternative is to subtract 1 day from the first day of the next month, like @Oto demonstrated. Related:

Here are two optimized ways to generate a series of last days of the month - up to and including a given month:

1.

SELECT (timestamp '2017-01-01' - i * interval '1 month')::date - 1 AS month_last_date
FROM   generate_series(-1, 10) i;  -- generate 12 months, off-by-1

Input is the first day of the month - or calculate it from a given date or timestamp with date_trunc():

SELECT date_trunc('month', timestamp '2017-01-17')::date AS this_mon1

Subtracting an interval from a date produces a timestamp. After the cast back to date we can simply subtract an integer to subtract days.

2.

SELECT m::date - 1 AS month_last_date
FROM   generate_series(timestamp '2017-02-01' - interval '11 month'  -- for 12 months
                     , timestamp '2017-02-01'
                     , interval '1 mon') m;

Input is the first day of the next month - or calculate it from any given date or timestamp with:

SELECT date_trunc('month', timestamp '2017-01-17' + interval '1 month')::date AS next_mon1

Related:

Not sure you actually need DISTINCT. Typically, (agent_id, month_last_date) would be defined unique, then remove DISTINCT ...

Be sure to use the LEFT JOIN correctly. Join conditions go into the join clause, not the WHERE clause:

Finally, default to 0 with COALESCE where NULL values are filled in by the LEFT JOIN.
Note that COALESCE cannot distinguish between actual NULL values from the right table and NULL values filled in for missing rows. If your columns are not defined NOT NULL, there may be ambiguity to address.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
1

As I see, you need generate last days of all last 6 months, before certain date. (before "2017-01-31" in this case).

If I correctly understand, then you can use this query, which generates all of these days

 SELECT (date_trunc('MONTH', mnth) + INTERVAL '1 MONTH - 1 day')::DATE 
 FROM 
 generate_series('2017-01-31'::date - interval '6 month', '2017-01-31'::date, '1 month') as mnth;

You just need LEFT JOIN this query to your existing query, and you get desirable result

Please note that this will returns 7 record (days), not 6.

Oto Shavadze
  • 40,603
  • 55
  • 152
  • 236