Oracle Query Count by Week Start on Tuesday
Posted on 2015-02-19
I have a table that contains information on files received over time. One of the columns is FILE_DT.
I also have a query that returns the count of files received per week. Right now, it is set up to count files received (using FILE_DT) by a "standard" week - Sunday to Saturday. I would like to modify the query to count by a "non-standard" week - Tuesday to Monday.
Here is the query:
SELECT TRUNC (FILE_DT, 'W') FILE_DT, COUNT ( * ) AllFiles
WHERE TRUNC (FILE_DT) between
to_date('30/08/2011', 'dd/mm/yyyy') AND
GROUP BY TRUNC (FILE_DT, 'W')
ORDER BY TRUNC (FILE_DT, 'W')
Here is sample output:
As you can see, the group is identified using the week start date, which is currently a Sunday. I would like to change the week start to Tuesday, but if possible, KEEP the group label as Sunday, for convenience (so if the group starts on Tuesday 2/10/15, the associated group label would be Sunday 2/8/15). If that is not possible, then using the Tuesday date as a label is perfectly acceptable. The important thing is that the counts reflect a Tuesday to Monday grouping, instead of Sunday to Saturday.