select distinct
COUNT(*) as COUNT , ---笔数
---ebankdzd.count,
ebankdzd.curacc,
org_orgs.name as curname,
bd_defdoc.def2 as SSBK,----所属板块
ebankdzd.trans_date,
'久悬账户'as JXZH,
'YQZL' as tablefalg,
org_orgs.pk_org,
org_orgs.name as orgsname
from bd_bankaccbas bd_bankaccbas
left join org_orgs on org_orgs.pk_org= bd_bankaccbas.pk_org
left join bd_defdoc bd_defdoc on bd_defdoc.def1=bd_bankaccbas.pk_org and bd_defdoc.pk_defdoclist in (select pk_defdoclist from bd_defdoclist where bd_defdoclist.code ='SK04')
left join (SELECT distinct
---COUNT(*) as COUNT , ---笔数
ebank_dzd.curacc,---本方账号
---ebank_dzd.curname,---本方户名
substr( ebank_dzd.trans_date, 1, 7 ) as trans_date,---交易日期
max ( ebank_dzd.trans_date ) as last_trans_date,---交易日期
---TRUNC(SYSDATE)-TRUNC(to_date(max ( ebank_dzd.trans_date ) , 'YYYY-MM-DD HH24:MI:SS')) as CE,
CASE
---WHEN TRUNC(TO_DATE(parameter('cxsj'), 'YYYY-MM-DD HH24:MI:SS'))-TRUNC(to_date(max ( ebank_dzd.trans_date ), 'YYYY-MM-DD HH24:MI:SS')) > 90 THEN '未发生交易周期（天）>90'
when TRUNC(SYSDATE)-TRUNC(to_date(max ( ebank_dzd.trans_date ), 'YYYY-MM-DD HH24:MI:SS')) > 90 THEN '未发生交易周期（天）>90'
ELSE '未发生交易周期（天）<90'
END AS WFSJYZQ, ---未发生交易周期
ROW_NUMBER() OVER (
PARTITION BY ebank_dzd.curacc
ORDER BY TO_DATE(MAX(ebank_dzd.trans_date), 'YYYY-MM-DD HH24:MI:SS') DESC
) AS rn
FROM ebank_dzd ebank_dzd
left join org_orgs org_orgs on org_orgs.pk_org= ebank_dzd.pk_org
WHERE (org_orgs.pk_org='0001Q7100000000021CL' or org_orgs.isbusinessunit ='Y')
and org_orgs.dr=0
and ebank_dzd.dr=0
and ebank_dzd.trans_abstr not like '%结息%'
---and ebank_dzd.pk_org ='0001SW1000000000DI99'
and ebank_dzd.curacc='121100500000012544'
---and substr( ebank_dzd.trans_date, 1, 7 )='2024-12'
group by ebank_dzd.curacc,ebank_dzd.curname,substr( ebank_dzd.trans_date, 1, 7 ),ebank_dzd.cdflag) ebankdzd on ebankdzd.CURACC = bd_bankaccbas.accnum
where (org_orgs.pk_org='0001Q7100000000021CL' or org_orgs.isbusinessunit ='Y') and (org_orgs.code <'1203' or org_orgs.code >'1216') and org_orgs.dr=0 and bd_bankaccbas.dr=0 and bd_bankaccbas.accclass !=0 and bd_bankaccbas.enablestate=2 and bd_bankaccbas.accstate=0
and bd_bankaccbas.accclass=2
---and ebankdzd.WFSJYZQ='未发生交易周期（天）>90'
and ebankdzd.rn=1
and TRUNC(SYSDATE)-TRUNC(to_date(ebankdzd.last_trans_date, 'YYYY-MM-DD HH24:MI:SS')) > 90
---and TRUNC(TO_DATE(parameter('cxsj'), 'YYYY-MM-DD HH24:MI:SS')) -TRUNC(to_date(ebankdzd.last_trans_date, 'YYYY-MM-DD HH24:MI:SS')) > 90
group by
ebankdzd.curacc,
bd_defdoc.def2,----所属板块
ebankdzd.trans_date,
org_orgs.pk_org,
org_orgs.name
union all
-----------其余的取科目发生额
select distinct
COUNT(*) as COUNT , ---笔数
gldetail.accnum as CURACC, gldetail.orgname as CURNAME,
---gldetail.accname as CURNAME,
bd_defdoc.def2 as SSBK,----所属板块
max(gldetail.preparedMM) as TRANS_DATE,----制单日期
---gldetail.preparedDD as cxsj,----查询日期
---gldetail.DBZDJYJE,
---gldetail.JYBS, ---交易笔数
'久悬账户'as JXZH,
gldetail.tablefalg,
gldetail.pk_org,
gldetail.orgname as orgsname
from (select distinct
---COUNT(*) as COUNT , ---笔数
gl_detail.pk_accasoa,
---gl_detail.pk_accountingbook,
gl_detail.pk_org,
org_orgs.code orgcode,
org_orgs.name orgname,
---gl_detail.yearv yearv,
gl_docfree1.F11 pk_bankaccbas,
bd_bankaccsub.accnum,
bd_bankaccsub.accname,
substr( gl_detail.prepareddatev, 1, 7 ) as preparedMM,----制单日期
max(substr( gl_detail.prepareddatev, 1, 10 )) as preparedDD,----制单日期
---gl_detail.voucherkindv , ---凭证类型
CASE
WHEN ---TRUNC(TO_DATE(parameter('cxsj'), 'YYYY-MM-DD HH24:MI:SS'))-TRUNC(to_date(max ( gl_detail.prepareddatev), 'YYYY-MM-DD HH24:MI:SS')) > 90 THEN '未发生交易周期（天）>90'
TRUNC(SYSDATE)-TRUNC(to_date(max ( gl_detail.prepareddatev), 'YYYY-MM-DD HH24:MI:SS')) > 90 THEN '未发生交易周期（天）>90'
ELSE '未发生交易周期（天）<90'
END AS WFSJYZQ, ---未发生交易周期
'detail' tablefalg,
-- 窗口函数：按pk_accasoa分组，按日期倒序排名
ROW_NUMBER() OVER (
PARTITION BY gl_detail.pk_accasoa
ORDER BY TO_DATE(MAX(gl_detail.prepareddatev), 'YYYY-MM-DD HH24:MI:SS') DESC
) AS rn
from gl_detail gl_detail
left join org_orgs org_orgs on org_orgs.pk_org= gl_detail.pk_org
left join gl_docfree1 gl_docfree1 on gl_detail.assid = gl_docfree1.assid
left join bd_accasoa bd_accasoa on bd_accasoa.pk_accasoa = gl_detail.pk_accasoa and bd_accasoa.dr = 0 ---会计科目
left join bd_account bd_account on bd_account.pk_account = bd_accasoa.pk_account and bd_account.dr = 0 --会计科目基本信息
left join bd_bankaccsub bd_bankaccsub on bd_bankaccsub.pk_bankaccsub=gl_docfree1.F11 and bd_bankaccsub.fronzenstate=0---银行账户子户
left join bd_bankaccbas bd_bankaccbas on bd_bankaccsub.pk_bankaccbas=bd_bankaccbas.pk_bankaccbas and bd_bankaccbas.accstate=0---银行账户子户
where
bd_account.code like ('1002%')
---and gl_detail.yearv =TO_CHAR(TRUNC(SYSDATE), 'YYYY')
and gl_detail.adjustperiod >= '00'
and gl_detail.adjustperiod <= '12'
and gl_detail.discardflagv <> 'Y' ---作废标志
AND gl_detail.tempsaveflag <> 'Y' ---暂存标志
and gl_detail.dr = 0
and gl_detail.voucherkindv !=2
and gl_detail.explanation not like '%结息%' ----摘要
--and gl_detail.voucherkindv <> 255
---and gl_detail.pk_managerv = 'N/A'
-- and gl_detail.voucherkindv <> 5
and gl_docfree1.F11 <> 'NN/A'
and 1 = 1
and bd_bankaccsub.pk_bankaccbas not in (select ebank_balance.pk_bankaccbas---银行账户主户pk
from ebank_balance ebank_balance ----银行账户余额表
WHERE ebank_balance.dr=0 )
and (org_orgs.code <'1203' or org_orgs.code >'1216')
---and gl_detail.pk_org ='0001SW1000000000DI99'
---and substr( gl_detail.prepareddatev, 1, 7 )='2024-12'
and bd_bankaccbas.accstate=0 and bd_bankaccbas.enablestate=2
group by gl_detail.pk_accasoa,
gl_detail.pk_org,
org_orgs.code,
org_orgs.name,
---gl_detail.pk_accountingbook,
---gl_detail.yearv,
gl_docfree1.F11,
bd_bankaccsub.accnum,
bd_bankaccsub.accname,
---gl_detail.debitamount,
---gl_detail.creditamount,
---gl_detail.voucherkindv,
---gl_detail.prepareddatev
substr(gl_detail.prepareddatev, 1, 7) ) gldetail
left join bd_defdoc bd_defdoc on bd_defdoc.def1=gldetail.pk_org and bd_defdoc.pk_defdoclist in (select pk_defdoclist from bd_defdoclist where bd_defdoclist.code ='SK04')
where ---TRUNC(TO_DATE(parameter('cxsj'), 'YYYY-MM-DD HH24:MI:SS')) -TRUNC(to_date(gldetail.preparedDD, 'YYYY-MM-DD HH24:MI:SS')) > 90
TRUNC(SYSDATE)-TRUNC(to_date(gldetail.preparedDD, 'YYYY-MM-DD HH24:MI:SS')) > 90
---and gldetail.pk_org ='0001SW1000000000DI99'
and gldetail.rn=1
group by ----gldetail.COUNT , ---笔数
gldetail.accnum,
gldetail.accname,
bd_defdoc.def2,----所属板块
gldetail.preparedMM,----制单日期
gldetail.tablefalg,
gldetail.pk_org,
gldetail.orgname 这个语句在总账和银行对账单的表里都有按照这个银行账号的所属组织去判断，现在想更改下这逻辑，只判断这个账号有没有在总账和银行对账单有没有发生过交易（90天），不需要按照银行所属组织去判断了
本文介绍了如何在特定格式下使用日期转换函数，并通过实例展示了转换过程及结果。
扫一扫
5622
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
