I saw a problem on csdn and thought about it and wrote one. Please correct me if I am wrong.
The screen function requires statistical table definition for data in a table (omitting unused fields)
LB01_PURRECEIVEBOOK
(
PURRECEIVEDATE DATE, --format: 2009/11/01
RECEIVEAMT NUMBER
)
① When counting by year, if there is a blank year, the amount for that year will be 0
example:
2009/09/01 1,000
2009/11/01 3,000
2007/12/01 2,000
Extract:
2009 4,000
2008 0
2007 2,000
②When calculating by month, if there is an empty month, the amount of that month will be set to 0
2009/09/01 1,000
2009/11/01 3,000
2009/12/01 2,000
Extract:
2009/09 1,000
2009/10 0
2009/11 3,000
2009/12 2,000
Can this be done relatively simply?
According to annual statistics
view plaincopy to clipboardprint?
WITH tmp_table AS (SELECT MAX(TO_CHAR(T.PURRECEIVEDATE, 'yyyy')) -
MIN(TO_CHAR(T.PURRECEIVEDATE, 'yyyy')) AS NUM,
MIN(TO_CHAR(T.PURRECEIVEDATE, 'yyyy')) AS MINYEAR
FROM LB01_PURRECEIVEBOOK T
),
select_table AS (
SELECT TMP.YEARSUM, TMP.YEAR, TMP.YMD
FROM (SELECT SUM(T.RECEIVEAMT) OVER(PARTITION BY TO_CHAR(T.PURRECEIVEDATE, 'yyyy') ORDER BY T.PURRECEIVEDATE DESC) AS YEARSUM,
TO_CHAR(T.PURRECEIVEDATE, 'yyyy') AS YEAR,
TO_CHAR(T.PURRECEIVEDATE, 'yyyy/mm/dd') AS YMD,
ROW_NUMBER() OVER(PARTITION BY TO_CHAR(T.PURRECEIVEDATE, 'yyyy') ORDER BY T.PURRECEIVEDATE) AS RN
FROM LB01_PURRECEIVEBOOK T) TMP
WHERE TMP.RN = 1
),
creatyear_table AS (
SELECT tmp_table.MINYEAR + LEVEL - 1 AS tmp_year FROM DUAL, tmp_table
CONNECT BY LEVEL <= tmp_table.NUM + 1
)
SELECT
ct.tmp_year,
NVL(st.YEARSUM, 0) AS YEARSUM
FROM
creatyear_table ct,
select_table st
WHERE
ct.tmp_year = st.year(+)
WITH tmp_table AS (SELECT MAX(TO_CHAR(T.PURRECEIVEDATE, 'yyyy')) -
MIN(TO_CHAR(T.PURRECEIVEDATE, 'yyyy')) AS NUM,
MIN(TO_CHAR(T.PURRECEIVEDATE, 'yyyy')) AS MINYEAR
FROM LB01_PURRECEIVEBOOK T
),
select_table AS (
SELECT TMP.YEARSUM, TMP.YEAR, TMP.YMD
FROM (SELECT SUM(T.RECEIVEAMT) OVER(PARTITION BY TO_CHAR(T.PURRECEIVEDATE, 'yyyy') ORDER BY T.PURRECEIVEDATE DESC) AS YEARSUM,
TO_CHAR(T.PURRECEIVEDATE, 'yyyy') AS YEAR,
TO_CHAR(T.PURRECEIVEDATE, 'yyyy/mm/dd') AS YMD,
ROW_NUMBER() OVER(PARTITION BY TO_CHAR(T.PURRECEIVEDATE, 'yyyy') ORDER BY T.PURRECEIVEDATE) AS RN
FROM LB01_PURRECEIVEBOOK T) TMP
WHERE TMP.RN = 1
),
creatyear_table AS (
SELECT tmp_table.MINYEAR + LEVEL - 1 AS tmp_year FROM DUAL, tmp_table
CONNECT BY LEVEL <= tmp_table.NUM + 1
)
SELECT
ct.tmp_year,
NVL(st.YEARSUM, 0) AS YEARSUM
FROM
creatyear_table ct,
select_table st
WHERE
ct.tmp_year = st.year(+)
Statistics by month
view plaincopy to clipboardprint?
WITH tmp_table AS (SELECT MAX(TO_CHAR(T.PURRECEIVEDATE, 'mm')) -
MIN(TO_CHAR(T.PURRECEIVEDATE, 'mm')) AS NUM,
MIN(TO_CHAR(T.PURRECEIVEDATE, 'mm')) AS MINMM,
MIN(TO_CHAR(T.PURRECEIVEDATE, 'YYYY')) AS MINY
FROM LB01_PURRECEIVEBOOK T
),
select_table AS (
SELECT TMP.MMSUM, TMP.MM, TMP.YMD
FROM (SELECT SUM(T.RECEIVEAMT) OVER(PARTITION BY TO_CHAR(T.PURRECEIVEDATE, 'MM') ORDER BY T.PURRECEIVEDATE DESC) AS MMSUM,
TO_CHAR(T.PURRECEIVEDATE, 'MM') AS MM,
TO_CHAR(T.PURRECEIVEDATE, 'yyyy/mm/dd') AS YMD,
ROW_NUMBER() OVER(PARTITION BY TO_CHAR(T.PURRECEIVEDATE, 'MM') ORDER BY T.PURRECEIVEDATE) AS RN
FROM LB01_PURRECEIVEBOOK T) TMP
WHERE TMP.RN = 1
),
creatyear_table AS (
SELECT tmp_table.MINMM + LEVEL - 1 AS TMP_MM,
TO_CHAR(TO_DATE(MINY || tmp_table.MINMM + LEVEL - 1, 'YYYY-MM'), 'YYYY-MM') AS TMP_YM
FROM DUAL, tmp_table
CONNECT BY LEVEL <= tmp_table.NUM + 1
)
SELECT
ct.TMP_YM,
TO_CHAR(NVL(ST.MMSUM, 0), '9,999') AS MMSUM
FROM
creatyear_table CT,
select_table ST
WHERE
CT.TMP_MM = ST.MM(+)
ORDER BY
ct.TMP_YM
-