Was reading through archived posts and found this one:
rp0428 Oct 19, 2012 3:12 AM (in response to 737273)>
Hi all he lp me in getting a oracle query to find out the days between 2 input dates excluding the weekends...
>
Something like this should work
with dates as (select sysdate fromDate, sysdate + 10 toDate from dual), days as (select sysdate + level - 0 myDay from dates connect by level < toDate - fromDate) select count(*) from days where to_char(myDay, 'Dy') not in ('Sat', 'Sun') COUNT(*) 6
The 'dates' query just provides the start and end dates. The 'days' query produces a date value for each day in the range between the start and end.
Then the last query counts those days if they are not a Saturday or Sunday.
You can adjust the 'days' query depending on whether you want both the start and end date included. For example if start is today and end is tomorrow (Friday) the above query result will be '1'.
Are you forgetting to mark your questions ANSWERED when they have been? Total Questions: 12 (10 unresolved)
I pasted the code above into Pentaho 3.8 and query worked fine, returning 6. When I tried entering sample dates however, it returned 0.
with dates as (select 01/10/2014 fromDate, 01/18/2014 toDate from dual),
days as (select sysdate + level - 0 myDay from dates
connect by level < toDate - fromDate)
select count(*) from days where to_char(myDay, 'Dy') not in ('Sat', 'Sun')
Can anyone spot what I am doing wrong? I am trying to enter sample dates so I can verify functionality. This will allow me to move closer to using the fields I actually need for report. fromDate will eventually be CREATED_DATE and toDate will be DATE_FIRST_SIGNED.