Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 246
  • Last Modified:

oracle sql - occurance of day in month

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
tonMachine100
Asked:
tonMachine100
  • 2
  • 2
1 Solution
 
Wasim Akram ShaikCommented:
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
 
sdstuberCommented:
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
 
sdstuberCommented:
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
 
Wasim Akram ShaikCommented:
True Sdstuber. . forgot that scenario..!! Thanks for pointing.
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now