Ich sah ein Problem bei csdn, dachte darüber nach und schrieb eines. Bitte korrigieren Sie mich, wenn ich falsch liege.
Die Bildschirmfunktion erfordert eine statistische Tabellendefinition für Daten in einer Tabelle (ohne nicht verwendete Felder).
LB01_PURRECEIVEBOOK
(
PURRECEIVEDATE DATE, --Format: 01.11.2009
EMPFANGSNUMMER
)
① Wenn bei der Zählung nach Jahr ein leeres Jahr vorhanden ist, ist der Betrag für dieses Jahr 0
Beispiel:
01.09.2009 1.000
01.11.2009 3.000
01.12.2007 2.000
Extrakt:
2009 4.000
2008 0
2007 2.000
②Wenn bei der Berechnung nach Monaten ein leerer Monat vorhanden ist, wird der Betrag dieses Monats auf 0 gesetzt
01.09.2009 1.000
01.11.2009 3.000
01.12.2009 2.000
Extrakt:
2009/09 1.000
2009/10 0
2009/11 3.000
2009/12 2.000
Lässt sich das relativ einfach bewerkstelligen?
Laut Jahresstatistik
Klartext in Zwischenablage anzeigen und drucken?
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
VON LB01_PURRECEIVEBOOK T
),
select_table AS (
WÄHLEN Sie 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 JAHR,
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
VON LB01_PURRECEIVEBOOK T) TMP
WO 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
)
WÄHLEN
ct.tmp_year,
NVL(st.YEARSUM, 0) AS YEARSUM
AUS
creatyear_table ct,
select_table st
WO
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
VON LB01_PURRECEIVEBOOK T
),
select_table AS (
WÄHLEN Sie 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 JAHR,
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
VON LB01_PURRECEIVEBOOK T) TMP
WO 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
)
WÄHLEN
ct.tmp_year,
NVL(st.YEARSUM, 0) AS YEARSUM
AUS
creatyear_table ct,
select_table st
WO
ct.tmp_year = st.year(+)
Statistiken pro Monat
Klartext in Zwischenablage anzeigen und drucken?
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
VON LB01_PURRECEIVEBOOK T
),
select_table AS (
WÄHLEN Sie 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
VON LB01_PURRECEIVEBOOK T) TMP
WO 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
VON DUAL, tmp_table
CONNECT BY LEVEL <= tmp_table.NUM + 1
)
WÄHLEN
ct.TMP_YM,
TO_CHAR(NVL(ST.MMSUM, 0), '9,999') AS MMSUM
AUS
creatyear_table CT,
select_table ST
WO
CT.TMP_MM = ST.MM(+)
BESTELLEN NACH
ct.TMP_YM
-