with dater as (select decode('',null,trunc(sysdate),'') daterep from dual)
select a.daterep,FDATE,ldate
,sum(decode(to_char((a.FDATE+level-1), 'd'),6,0,7,0,1)) s
from
(SELECT daterep
,trunc(daterep-to_char (daterep, 'd')-6,'MM') FDATE
,daterep-to_char (daterep, 'd')-2 LDATE
,last_day(trunc(daterep-to_char (daterep, 'd')-6,'MM')) ENDDATE
,last_day(trunc(daterep-to_char (daterep, 'd')-6,'MM'))-trunc(daterep-to_char (daterep, 'd')-6,'MM')+1 days
,daterep-to_char (daterep, 'd')-2-trunc(daterep-to_char (daterep, 'd')-6,'MM')+1 days1
FROM dater)a connect by level<=days
group by a.daterep,FDATE,ldate
|