oracle現在の期間における週、日、月の取引統計

日:

SELECT  COUNT(1)
		FROM STUDENT_BILL  WHERE 1=1
		
			AND SUB_MERCHANT_ID = #{merchantId}
		
		
			AND to_char(PAY_TIME,'yyyy-mm-dd')= to_char(to_date(#{date}, 'yyyy-mm-dd hh24:mi:ss')-#{type},'yyyy-mm-dd') AND STATUS = '1'
		
		
			AND to_char(PAY_TIME,'yyyy-mm-dd')= to_char(to_date(#{date}, 'yyyy-mm-dd hh24:mi:ss'),'yyyy-mm-dd') AND STATUS = '1'
		

 
週:

SELECT  COUNT(1)
		FROM STUDENT_BILL  WHERE 1=1
		
			AND SUB_MERCHANT_ID = #{merchantId}
		
		
			AND PAY_TIME between trunc(next_day(to_date(#{date}, 'yyyy-mm-dd hh24:mi:ss') - 1, 1)-6) AND  trunc(next_day(to_date(#{date}, 'yyyy-mm-dd hh24:mi:ss') - 1, 1)+1) AND STATUS = '1'
		
		
			AND PAY_TIME between trunc(next_day(to_date(#{date}, 'yyyy-mm-dd hh24:mi:ss') -(1+#{type}*7), 1)-6) AND  trunc(next_day(to_date(#{date}, 'yyyy-mm-dd hh24:mi:ss') - (1+#{type}*7), 1)+1) AND STATUS = '1'
		

月:

SELECT  COUNT(1)
		FROM STUDENT_BILL  WHERE 1=1
		
			AND SUB_MERCHANT_ID = #{merchantId}
		
		
			AND to_char(PAY_TIME,'yyyy-mm')= to_char(add_months(to_date(#{date}, 'yyyy-mm-dd hh24:mi:ss'),0),'yyyy-mm') AND STATUS = '1'
		
		
			AND to_char(PAY_TIME,'yyyy-mm')= to_char(add_months(to_date(#{date}, 'yyyy-mm-dd hh24:mi:ss'),-#{type}),'yyyy-mm') AND STATUS = '1'
		

備考:typeの値:0は当日を表して、1は昨日を表して、順次前へ行って、数日一回typeの値を増加したいです
週間は月と同じで、0は当月当週、1は前月の先週などを表します
週の時間は月曜日から日曜日までの取引区間です
日付の取得方法:

// 

select  next_day(to_date('2018-8-3','yyyy-MM-dd'),1) from dual;//         ，             
select  next_day(to_date('2018-8-3','yyyy-MM-dd')-8,1) from dual;//       ，      
select  trunc(next_day(to_date('2018-8-3','yyyy-MM-dd')-1,1)-6) from dual;//        ，      
select  trunc(next_day(to_date('2018-8-3','yyyy-MM-dd')-1,1)+1) from dual;//         ，      
select  trunc(next_day(to_date('2018-8-3','yyyy-MM-dd')-8,1)-6) from dual;//        ，      
//         00：00:00     00:00:00，        23:59:59

// 

select  to_char(to_date('2018-8-3', 'yyyy-mm-dd hh24:mi:ss')-3,'yyyy-mm-dd') from dual;//                    

// 

select  to_char(add_months(to_date('2018-8-3', 'yyyy-mm-dd hh24:mi:ss'),-2),'yyyy-mm') from dual;//     ，