?
Solved

Oracle Query Count by Week Start on Tuesday

Posted on 2015-02-19
3
Medium Priority
?
422 Views
Last Modified: 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
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.
0
Comment
Question by:bassman592
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 12

Expert Comment

by:FarWest
ID: 40619969
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
0
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 2000 total points
ID: 40620060
See if this works for you:
SELECT next_day(trunc(col1)-7,'TUESDAY')
	FILE_DT,  COUNT ( * ) AllFiles
FROM files_received
WHERE  FILE_DT >= to_date('30/08/2011', 'dd/mm/yyyy') AND
   file_dt < to_date('28/12/2015', 'dd/mm/yyyy')+1
GROUP BY next_day(trunc(col1)-7,'TUESDAY')
ORDER BY next_day(trunc(col1)-7,'TUESDAY')

Open in new window


Notice I removed the TRUNC in the where clause.  Unless you have a Function-Based Index in it, it will not use any index.  Assuming there is an index on FILE_DT.

>> but if possible, KEEP the group label as Sunday
I'm not sure why you want to call Tuesday Sunday.  Anyway, there are no labels in the query.
0
 

Author Closing Comment

by:bassman592
ID: 40620215
Perfect! Thank you.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to recover a database from a user managed backup
Suggested Courses

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question