Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

oracle sql - occurance of day in month

Posted on 2014-10-30
4
Medium Priority
?
245 Views
Last Modified: 2014-10-30
Please see the attached data which has two columns (dt and dy) which are generated through the query:

select
to_char(dt, 'Day') dy,
dt
from
(select 
(sysdate - 100) + rownum -1 dt 
from 
dual 
connect by level <= sysdate - (sysdate - 100) + 1)

Open in new window


I need to add into the query another column (calc_field) which gives the count of the number of times that day has happened in that month (up to that date).

 any help is appreciated
ee-example.xlsx
0
Comment
Question by:tonMachine100
  • 2
  • 2
4 Comments
 
LVL 16

Expert Comment

by:Wasim Akram Shaik
ID: 40413065
Try this..

select
to_char(dt, 'Day') dy,
dt,count(1) over(partition by to_char(dt,'Day'),to_char(dt,'Mon')) as Tot
from
(select 
(sysdate - 100) + rownum -1 dt 
from 
dual 
connect by level <= sysdate - (sysdate - 100) + 1);

Open in new window

0
 
LVL 74

Accepted Solution

by:
sdstuber earned 2000 total points
ID: 40413068
select
to_char(dt, 'Day') dy,
dt,
ceil(to_number(to_char(dt,'dd'))/7) calc_field
from
(select
(sysdate - 100) + rownum -1 dt
from
dual
connect by level <= sysdate - (sysdate - 100) + 1)
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 40413073
40413065 doesn't work

The COUNT function will only be able to return counts of data in the results, which does not necessarily reflect the calendar.

For example, if you run the query today (Oct 30, 2014)  then there will only be one week of July data in the results thus producing

Friday         2014-07-25 08:11:05      1  

when it should be

Friday         2014-07-25 08:09:50      4


also, the partitioning isn't correct so even if you have a full month, the counts are for the entire month so you get results like this...

Friday         2014-09-12 08:11:05      4

which should be

Friday         2014-09-12 08:09:50      2


the simple division and CEIL function will work regardless of the result set.
0
 
LVL 16

Expert Comment

by:Wasim Akram Shaik
ID: 40413079
True Sdstuber. . forgot that scenario..!! Thanks for pointing.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Via a live example, show how to take different types of Oracle backups using RMAN.
Suggested Courses

971 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