Week interval query starting on mondays

Question

I need to do a JasperReport. what I need to display is the total number of accounts processes, broken down into weekly intervals with the number of activated and declined accounts.

For the weekly interval query I got thus far:

SELECT *
FROM account_details
WHERE DATE date_opened = DATE_ADD(2014-01-01, INTERVAL(1-DAYOFWEEK(2014-01-01)) +1 DAY)

This seems to be correct, but not POSTGRES correct. It keeps complaining about the 1-DAYOFWEEK. Here is what I will hopefully achieve:

enter image description here

UPDATE It is pretty ugly, but I dont know of any better. Id does the job though. But dont know if it can be re-factored to look better at least. I also dont know how to handle division by zero at the moment.

        SELECT to_char(d.day, 'YYYY/MM/DD  -  ') || to_char(d.day + 6, 'YYYY/MM/DD') AS Month
         , SUM(CASE WHEN LOWER(situation) LIKE '%active%' THEN 1 ELSE 0 END)     AS Activated
         , SUM(CASE WHEN LOWER(situation) LIKE '%declined%' THEN 1 ELSE 0 END) AS Declined
         , SUM(CASE WHEN LOWER(situation) LIKE '%declined%' OR LOWER(situation) LIKE '%active%' THEN 1 ELSE 0 END) AS Total
         , to_char( 100.0 *( (SUM(CASE WHEN LOWER(situation) LIKE '%active%' THEN 1 ELSE 0 END)) / (SUM(CASE WHEN LOWER(situation) LIKE '%declined%' OR LOWER(situation) LIKE '%active%' THEN 1 ELSE 0 END))::real) , '99.9') AS percent_activated
         , to_char( 100.0 *( (SUM(CASE WHEN LOWER(situation) LIKE '%declined%' THEN 1 ELSE 0 END)) / (SUM(CASE WHEN LOWER(situation) LIKE '%declined%' OR LOWER(situation) LIKE '%active%' THEN 1 ELSE 0 END))::real) , '99.9') AS percent_declined
    FROM   (
       SELECT day::date
       FROM   generate_series('2014-08-01'::date, '2014-09-14'::date, interval '1 week') day
       ) d
    JOIN   account_details a ON a.date_opened >= d.day 
                            AND a.date_opened <  d.day + 6
    GROUP  BY d.day;

Literal date and timestamp values go between single quotes, like `'2014-01-01'`. — Mike Sherrill 'Cat Recall', Sep 15 '14 at 15:18
while `SELECT 2014-01-01` *is valid*, it evaluates to `2012` -- also you completely misunderstood what is a literal, and what is a function call -- have a look around here: http://www.postgresql.org/docs/current/static/sql-syntax-lexical.html#SQL-SYNTAX-CONSTANTS-GENERIC -- column values & expressions already have a type, they don't need to be *noted*, but sometimes they need to be *casted* http://www.postgresql.org/docs/current/static/sql-expressions.html#SQL-SYNTAX-TYPE-CASTS — pozs, Sep 15 '14 at 15:42
The "Fiddle" link is not a fiddle, but another image. Probably mix-up of links? — Erwin Brandstetter, Sep 16 '14 at 10:55
OK, there is a fiddle now, but it is unrelated to the question. — Erwin Brandstetter, Sep 16 '14 at 11:45

score 1 · Accepted Answer · edited May 23 '17 at 11:54

1

SELECT to_char(d.day, 'YYYY/MM/DD" - "')
    || to_char(d.day + 6, 'YYYY/MM/DD') AS week
     , count(situation ILIKE '%active%' OR NULL) AS activated
     , ...
FROM   (
   SELECT day::date
   FROM   generate_series('2014-08-11'::date
                        , '2014-09-14'::date
                        , '1 week'::interval) day
   ) d
LEFT   JOIN account_details a ON a.date_opened >= d.day 
                             AND a.date_opened <  d.day + 7  -- 7, not 6!
GROUP  BY d.day;

Related answers:

More about counting specific values:

Aside: You would typically use an enum or a look-up table and just store an ID for situation, not a lengthy text redundantly.

edited May 23 '17 at 11:54

Community

1
1

answered Sep 15 '14 at 17:30

Erwin Brandstetter

605,456
145
1,078
1,228

Erwin, thanks for that, but I have a question please. in the `account_details` table the column to clarify if the account is `active` or `declined` is a string. for example `"Active - No TopUp Allowed"` and `"Application - Declined Cust No Contact"`. How would you sum the string values? something like `sum(LIKE('active')) AS activated`? – morne Sep 16 '14 at 07:11
or even `sum(SELECT regexp_matches(situation, 'active')) AS activated` where `situation` is the column name – morne Sep 16 '14 at 07:28
if I can refer you to. http://stackoverflow.com/questions/25863179/capturing-and-summing-string-values-from-one-column/25863456#25863456 – morne Sep 16 '14 at 08:06
@mornenel: I added some more. – Erwin Brandstetter Sep 16 '14 at 10:40

Week interval query starting on mondays

1 Answers1