/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/
外部SQL
/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/

-- #UkagaiKensaku#

-- !df:pmb extends Paging!
-- !!String Syokokai_cd!!
-- !!String Kaikei_cd!!
-- !!String Nendo!!
-- !!long? Denpyo_no_ST!!
-- !!long? Denpyo_no_ED!!
-- !!int? Denpyo_kbn!!
-- !!String Kian_user!!
-- !!DateTime? Kian_ymd_ST:fromDate!!
-- !!DateTime? Kian_ymd_ED:toDate!!
-- !!int? Kessai!!

/*IF pmb.IsPaging*/
select * from (select plain.*, row_number() over(order by DENPYO_NO DESC, DENPYO_KBN ASC) as rn from (
-- ELSE select count(*) from (select plain.*, row_number() over(order by DENPYO_NO DESC, DENPYO_KBN ASC) as rn from ( /*END*/
Select 
A.DENPYO_NO,
A.DENPYO_KBN,
D.DENPYO_KBN_NM,
A.KARIKATA_KAMOKU_NM,
A.KASIKATA_KAMOKU_NM,
A.TEKIYO_1,
A.KIAN_YMD,
A.KIAN_USER,
E.SYOKUIN_NM,
A.KESSAI,
A.KESSAI_YMD,
A.KESSAI_JIKKO_YMD,
A.SEKOU_YMD,
C.SUMKINGAKU,
A.SIWAKE_DENPYO_NO,
A.VERSION_NO 
FROM T_UKAGAI A 
		LEFT OUTER JOIN M_SYOKUIN E ON (A.SYOKOKAI_CD = E.SYOKOKAI_CD AND A.KIAN_USER = E.SYOKUIN_ID) , M_DENPYOKBN D, 
(SELECT SYOKOKAI_CD,KAIKEI_CD,NENDO,DENPYO_NO,SUM(KARIKATA_KIN) AS SUMKINGAKU FROM T_UKAGAI WK 

WHERE 
/*IF pmb.Syokokai_cd != null*/WK.SYOKOKAI_CD = /*pmb.Syokokai_cd*/'2391'/*END*/
/*IF pmb.Kaikei_cd != null*/AND WK.KAIKEI_CD = /*pmb.Kaikei_cd*/'01'/*END*/
/*IF pmb.Nendo != null*/AND WK.NENDO = /*pmb.Nendo*/'2017'/*END*/
/*IF pmb.Denpyo_no_ST != null*/AND WK.DENPYO_NO >= /*pmb.Denpyo_no_ST*/1/*END*/
/*IF pmb.Denpyo_no_ED != null*/AND WK.DENPYO_NO <= /*pmb.Denpyo_no_ED*/999999999999999/*END*/
/*IF pmb.Denpyo_kbn != null*/AND WK.DENPYO_KBN = /*pmb.Denpyo_kbn*/3/*END*/
/*IF pmb.Kian_user != null*/AND WK.KIAN_USER = /*pmb.Kian_user*/'0027'/*END*/
/*IF pmb.Kian_ymd_ST != null*/AND WK.KIAN_YMD >= /*pmb.Kian_ymd_ST*/'2013/01/01'/*END*/
/*IF pmb.Kian_ymd_ED != null*/AND WK.KIAN_YMD < /*pmb.Kian_ymd_ED*/'2099/12/31'/*END*/
/*IF pmb.Kessai != null*/AND WK.KESSAI = /*pmb.Kessai*/1/*END*/

GROUP BY WK.SYOKOKAI_CD,WK.KAIKEI_CD,WK.NENDO,WK.DENPYO_NO) C

WHERE 
exists
(SELECT SYOKOKAI_CD,KAIKEI_CD,NENDO,DENPYO_NO FROM T_UKAGAI B 
WHERE
A.SYOKOKAI_CD = B.SYOKOKAI_CD 
AND A.KAIKEI_CD = B.KAIKEI_CD 
AND A.NENDO = B.NENDO 
AND A.DENPYO_NO = B.DENPYO_NO 
AND A.DENPYO_ROW = 1 
AND A.DENPYO_KBN = B.DENPYO_KBN
/*IF pmb.Syokokai_cd != null*/AND B.SYOKOKAI_CD = /*pmb.Syokokai_cd*/'2391'/*END*/
/*IF pmb.Kaikei_cd != null*/AND B.KAIKEI_CD = /*pmb.Kaikei_cd*/'01'/*END*/
/*IF pmb.Nendo != null*/AND B.NENDO = /*pmb.Nendo*/'2017'/*END*/
/*IF pmb.Denpyo_no_ST != null*/AND B.DENPYO_NO >= /*pmb.Denpyo_no_ST*/1/*END*/
/*IF pmb.Denpyo_no_ED != null*/AND B.DENPYO_NO <= /*pmb.Denpyo_no_ED*/999999999999999/*END*/
/*IF pmb.Denpyo_kbn != null*/AND B.DENPYO_KBN = /*pmb.Denpyo_kbn*/3/*END*/
/*IF pmb.Kian_user != null*/AND B.KIAN_USER = /*pmb.Kian_user*/'0027'/*END*/
/*IF pmb.Kian_ymd_ST != null*/AND B.KIAN_YMD >= /*pmb.Kian_ymd_ST*/'2013/01/01'/*END*/
/*IF pmb.Kian_ymd_ED != null*/AND B.KIAN_YMD < /*pmb.Kian_ymd_ED*/'2099/12/31'/*END*/
/*IF pmb.Kessai != null*/AND B.KESSAI = /*pmb.Kessai*/1/*END*/
GROUP BY B.SYOKOKAI_CD,B.KAIKEI_CD,B.NENDO,B.DENPYO_NO) 
AND 
A.SYOKOKAI_CD = C.SYOKOKAI_CD AND
A.KAIKEI_CD = C.KAIKEI_CD AND
A.NENDO = C.NENDO AND 
A.DENPYO_NO = C.DENPYO_NO AND 
A.SYOKOKAI_CD = D.SYOKOKAI_CD AND
A.DENPYO_KBN = D.DENPYO_KBN 

) plain
) ext
/*IF pmb.IsPaging*/
 where ext.rn > /*pmb.PageStartIndex*/0
   and ext.rn <= /*pmb.PageEndIndex*/10
/*END*/





/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/
実行結果　２ページ目
/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/

-- #UkagaiKensaku#

-- !df:pmb extends Paging!
-- !!String Syokokai_cd!!
-- !!String Kaikei_cd!!
-- !!String Nendo!!
-- !!longQ Denpyo_no_ST!!
-- !!longQ Denpyo_no_ED!!
-- !!intQ Denpyo_kbn!!
-- !!String Kian_user!!
-- !!DateTimeQ Kian_ymd_ST:fromDate!!
-- !!DateTimeQ Kian_ymd_ED:toDate!!
-- !!intQ Kessai!!

select count(*) from (select plain.*, row_number() over(order by DENPYO_NO DESC, DENPYO_KBN ASC) as rn from ( 
Select 
A.DENPYO_NO,
A.DENPYO_KBN,
D.DENPYO_KBN_NM,
A.KARIKATA_KAMOKU_NM,
A.KASIKATA_KAMOKU_NM,
A.TEKIYO_1,
A.KIAN_YMD,
A.KIAN_USER,
E.SYOKUIN_NM,
A.KESSAI,
A.KESSAI_YMD,
A.KESSAI_JIKKO_YMD,
A.SEKOU_YMD,
C.SUMKINGAKU,
A.SIWAKE_DENPYO_NO,
A.VERSION_NO 
FROM T_UKAGAI A 
		LEFT OUTER JOIN M_SYOKUIN E ON (A.SYOKOKAI_CD = E.SYOKOKAI_CD AND A.KIAN_USER = E.SYOKUIN_ID) , M_DENPYOKBN D, 
(SELECT SYOKOKAI_CD,KAIKEI_CD,NENDO,DENPYO_NO,SUM(KARIKATA_KIN) AS SUMKINGAKU FROM T_UKAGAI WK 

WHERE 
WK.SYOKOKAI_CD = '2391'
AND WK.KAIKEI_CD = '01'
AND WK.NENDO = '2017'




AND WK.KIAN_YMD >= '2017-04-01'
AND WK.KIAN_YMD < '2018-04-01'
AND WK.KESSAI = 1

GROUP BY WK.SYOKOKAI_CD,WK.KAIKEI_CD,WK.NENDO,WK.DENPYO_NO) C

WHERE 
exists
(SELECT SYOKOKAI_CD,KAIKEI_CD,NENDO,DENPYO_NO FROM T_UKAGAI B 
WHERE
A.SYOKOKAI_CD = B.SYOKOKAI_CD 
AND A.KAIKEI_CD = B.KAIKEI_CD 
AND A.NENDO = B.NENDO 
AND A.DENPYO_NO = B.DENPYO_NO 
AND A.DENPYO_ROW = 1 
AND A.DENPYO_KBN = B.DENPYO_KBN
AND B.SYOKOKAI_CD = '2391'
AND B.KAIKEI_CD = '01'
AND B.NENDO = '2017'




AND B.KIAN_YMD >= '2017-04-01'
AND B.KIAN_YMD < '2018-04-01'
AND B.KESSAI = 1
GROUP BY B.SYOKOKAI_CD,B.KAIKEI_CD,B.NENDO,B.DENPYO_NO) 
AND 
A.SYOKOKAI_CD = C.SYOKOKAI_CD AND
A.KAIKEI_CD = C.KAIKEI_CD AND
A.NENDO = C.NENDO AND 
A.DENPYO_NO = C.DENPYO_NO AND 
A.SYOKOKAI_CD = D.SYOKOKAI_CD AND
A.DENPYO_KBN = D.DENPYO_KBN 

) plain
) ext


*******************************************************************************************

-- #UkagaiKensaku#

-- !df:pmb extends Paging!
-- !!String Syokokai_cd!!
-- !!String Kaikei_cd!!
-- !!String Nendo!!
-- !!longQ Denpyo_no_ST!!
-- !!longQ Denpyo_no_ED!!
-- !!intQ Denpyo_kbn!!
-- !!String Kian_user!!
-- !!DateTimeQ Kian_ymd_ST:fromDate!!
-- !!DateTimeQ Kian_ymd_ED:toDate!!
-- !!intQ Kessai!!


select * from (select plain.*, row_number() over(order by DENPYO_NO DESC, DENPYO_KBN ASC) as rn from (

Select 
A.DENPYO_NO,
A.DENPYO_KBN,
D.DENPYO_KBN_NM,
A.KARIKATA_KAMOKU_NM,
A.KASIKATA_KAMOKU_NM,
A.TEKIYO_1,
A.KIAN_YMD,
A.KIAN_USER,
E.SYOKUIN_NM,
A.KESSAI,
A.KESSAI_YMD,
A.KESSAI_JIKKO_YMD,
A.SEKOU_YMD,
C.SUMKINGAKU,
A.SIWAKE_DENPYO_NO,
A.VERSION_NO 
FROM T_UKAGAI A 
		LEFT OUTER JOIN M_SYOKUIN E ON (A.SYOKOKAI_CD = E.SYOKOKAI_CD AND A.KIAN_USER = E.SYOKUIN_ID) , M_DENPYOKBN D, 
(SELECT SYOKOKAI_CD,KAIKEI_CD,NENDO,DENPYO_NO,SUM(KARIKATA_KIN) AS SUMKINGAKU FROM T_UKAGAI WK 

WHERE 
WK.SYOKOKAI_CD = '2391'
AND WK.KAIKEI_CD = '01'
AND WK.NENDO = '2017'




AND WK.KIAN_YMD >= '2017-04-01'
AND WK.KIAN_YMD < '2018-04-01'
AND WK.KESSAI = 1

GROUP BY WK.SYOKOKAI_CD,WK.KAIKEI_CD,WK.NENDO,WK.DENPYO_NO) C

WHERE 
exists
(SELECT SYOKOKAI_CD,KAIKEI_CD,NENDO,DENPYO_NO FROM T_UKAGAI B 
WHERE
A.SYOKOKAI_CD = B.SYOKOKAI_CD 
AND A.KAIKEI_CD = B.KAIKEI_CD 
AND A.NENDO = B.NENDO 
AND A.DENPYO_NO = B.DENPYO_NO 
AND A.DENPYO_ROW = 1 
AND A.DENPYO_KBN = B.DENPYO_KBN
AND B.SYOKOKAI_CD = '2391'
AND B.KAIKEI_CD = '01'
AND B.NENDO = '2017'




AND B.KIAN_YMD >= '2017-04-01'
AND B.KIAN_YMD < '2018-04-01'
AND B.KESSAI = 1
GROUP BY B.SYOKOKAI_CD,B.KAIKEI_CD,B.NENDO,B.DENPYO_NO) 
AND 
A.SYOKOKAI_CD = C.SYOKOKAI_CD AND
A.KAIKEI_CD = C.KAIKEI_CD AND
A.NENDO = C.NENDO AND 
A.DENPYO_NO = C.DENPYO_NO AND 
A.SYOKOKAI_CD = D.SYOKOKAI_CD AND
A.DENPYO_KBN = D.DENPYO_KBN 

) plain
) ext

 where ext.rn > 10
   and ext.rn <= 20