Solved

oracle sql - occurance of day in month

Posted on 2014-10-30
4
213 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 73

Accepted Solution

by:
sdstuber earned 500 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 73

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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

760 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now