CODEWITHSUNDEEP
sqlamazon-redshift
Carlos Siestrup
Carlos Siestrup

Reputation: 1216

Group By Week on Redshift

I'm trying to count my table by week but the DATE_TRUNCT('week',date) function considers Monday as the start of the week but I need for the week to start in Sunday.

This is the query, which runs properly but with starting in Mondays...

SELECT DATE_TRUNC('week',myDate) AS Reference,
       column1 AS Item1,
       column2 AS Item2,
       COUNT(*) AS Volume,
       COUNT(CASE WHEN status = 'status1' THEN 1 END) AS Status1,
       COUNT(CASE WHEN status = 'status2' THEN 1 END) AS Status2,
       COUNT(CASE WHEN status = 'status2' AND fase = '1' THEN 1 END) AS Fase1,
       COUNT(CASE WHEN status = 'status2' AND fase = '2' THEN 1 END) AS Fase2,
       COUNT(CASE WHEN status = 'status2' AND fase = '3' THEN 1 END) AS Fase3
FROM myTable
WHERE DATE_TRUNC('week',myDate) = DATE_TRUNC('week',TO_DATE('12/25/2016 00:00:00','MM/dd/yyyy'))
GROUP BY 1,
         2,
         3;

So far I only tried another query which doesnt even run and I dont know why, it just says "syntax error at or near "integer" :

SELECT DATE_TRUNC('week',myDate) - integer '1' AS Reference,
       column1 AS Item1,
       column2 AS Item2,
       COUNT(*) AS Item3,
       COUNT(CASE WHEN status = 'status1' THEN 1 END) AS Status1,
       COUNT(CASE WHEN status = 'status2' THEN 1 END) AS Status2,
       COUNT(CASE WHEN status = 'status2' AND fase = '1' THEN 1 END) AS Fase1,
       COUNT(CASE WHEN status = 'status2' AND fase = '2' THEN 1 END) AS Fase2,
       COUNT(CASE WHEN status = 'status2' AND fase = '3' THEN 1 END) AS Fase3
FROM myTable
WHERE myDate between ( DATE_TRUNC('week', TO_DATE('12/25/2016 00:00:00','MM/dd/yyyy' ) - integer '1' ) and ( DATE_TRUNC('week', TO_DATE('12/25/2016 00:00:00','MM/dd/yyyy' ) ) + integer '5' )
GROUP BY 1,
         2,
         3;

Also, even if this query runned propely, it would show the count of the week 18/Dec - 24/Dec and not the week 25/Dec - 31/Dec in the case 25/Dec. The same would happen in other days if they are Sundays.

EDIT: I just found the solution in this blog: https://blog.modeanalytics.com/date-trunc-sql-timestamp-function-count-on/ It was introducing the date_trunct function and someone asked the same question in the comments. This is my solved query for future reference to others:

SELECT date_trunc('WEEK',(myDate + interval '1 day'))- interval '1 day' AS Reference
       column1 AS Item1,
       column2 AS Item2,
       COUNT(*) AS Volume,
       COUNT(CASE WHEN status = 'status1' THEN 1 END) AS Status1,
       COUNT(CASE WHEN status = 'status2' THEN 1 END) AS Status2,
       COUNT(CASE WHEN status = 'status2' AND fase = '1' THEN 1 END) AS Fase1,
       COUNT(CASE WHEN status = 'status2' AND fase = '2' THEN 1 END) AS Fase2,
       COUNT(CASE WHEN status = 'status2' AND fase = '3' THEN 1 END) AS Fase3
FROM myTable
WHERE  ( date_trunc('WEEK',(myDate + interval '1 day'))- interval '1 day') = ( DATE_TRUNC('week',TO_DATE('12/24/2016 00:00:00','MM/dd/yyyy') + interval '1 day'  ) - interval '1 day' )
GROUP BY 1,
         2,
         3;

Upvotes: 1

Views: 12032

Answers (1)

ntalbs
ntalbs

Reputation: 29448

I couldn't find any simple way to set week as Sunday to Saturday. But you can try this:

select date_trunc('week', myDate + 1) - 1 as Reference,
       ...
from myTable
where ...
group by date_trunc('week', myDate + 1), ...

The trick here is just shift by one day while doing group by.

Upvotes: 7

Related Questions