Oracle Query Count by Week Start on Tuesday
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
FROM files_received
WHERE TRUNC (FILE_DT) between
to_date('30/08/2011', 'dd/mm/yyyy') AND
to_date('28/12/2015', 'dd/mm/yyyy')
GROUP BY TRUNC (FILE_DT, 'W')
ORDER BY TRUNC (FILE_DT, 'W')
Here is sample output:
WK_START FILES
12/15/2014 80
12/22/2014 76
12/29/2014 45
1/1/2015 76
1/8/2015 83
1/15/2015 96
1/22/2015 93
1/29/2015 19
2/1/2015 97
2/8/2015 100
2/15/2015 83
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.
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
FROM files_received
WHERE TRUNC (FILE_DT) between
to_date('30/08/2011', 'dd/mm/yyyy') AND
to_date('28/12/2015', 'dd/mm/yyyy')
GROUP BY TRUNC (FILE_DT, 'W')
ORDER BY TRUNC (FILE_DT, 'W')
Here is sample output:
WK_START FILES
12/15/2014 80
12/22/2014 76
12/29/2014 45
1/1/2015 76
1/8/2015 83
1/15/2015 96
1/22/2015 93
1/29/2015 19
2/1/2015 97
2/8/2015 100
2/15/2015 83
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.
I think it is better to create a table that contains 3 columns week lable,week start . week end and use it in you query. You can easily create rows for all required years with a prober week labels
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Perfect! Thank you.