Revision as of 10:46, 17 April 2021

Queries

Archive Info

SELECT ARCHIVE_FLAG, count(1) as CNT FROM ARCHIVE_INFO GROUP BY ARCHIVE_FLAG ORDER BY ARCHIVE_FLAG

result:

ARCHIVE_FLAG	CNT
-1	310
0	83910
1	912571
2	4198

SELECT ARCHIVE_FLAG,GROUP_ID,MIN(ARCHIVE_DATE),MAX(ARCHIVE_DATE) FROM ARCHIVE_INFO where ARCHIVE_FLAG >=0 GROUP BY ARCHIVE_FLAG, GROUP_ID

result:

ARCHIVE_FLAG	GROUP_ID	MIN(ARCHIVE_DATE)	MAX(ARCHIVE_DATE)
2	1	2030-10-19 16:59:41.0	2031-04-07 19:11:17.0
1	4	2021-04-19 11:31:44.0	2021-04-30 16:56:05.0
1	2	2021-04-17 07:37:36.0	2021-04-19 07:36:06.0

Messages on Mailboxes by Year

select to_char(mm.CREATED_DATETIME, 'YYYY') as YEAR1, count(1) as CONT

from MBX_MESSAGE mm
group by to_char(mm.CREATED_DATETIME, 'YYYY')
order by to_char(mm.CREATED_DATETIME, 'YYYY')

result:

YEAR1	CNT
2018	9943
2019	14530
2020	265567
2021	4198

Messages on mailboxes by created date

select mm.MESSAGE_ID,mm.CREATED_DATETIME,mm.MESSAGE_SIZE,mm.MAILBOX_ID,mb.PATH,mm.DOC_ID
from MBX_MESSAGE mm, MBX_MAILBOX mb
where mm.MAILBOX_ID = mb.MAILBOX_ID
order by mm.CREATED_DATETIME

Messages on mailboxes group by year with count and sum

select to_char(mm.CREATED_DATETIME, 'YYYY') as ANO, mb.PATH as mbx, count(1) as CNT, SUM(mm.MESSAGE_SIZE) as SUM1
from MBX_MESSAGE mm, MBX_MAILBOX mb
where mm.MAILBOX_ID = mb.MAILBOX_ID
group by to_char(mm.CREATED_DATETIME, 'YYYY'), mb.PATH

Ver também

@@ Line 18: / Line 18: @@
 |-
 | 2 || 4198
+|}
+ SELECT ARCHIVE_FLAG,GROUP_ID,MIN(ARCHIVE_DATE),MAX(ARCHIVE_DATE) FROM ARCHIVE_INFO where ARCHIVE_FLAG >=0 GROUP BY ARCHIVE_FLAG, GROUP_ID
+result:
+{| class="wikitable"
+|-
+! ARCHIVE_FLAG !! GROUP_ID !! MIN(ARCHIVE_DATE) !! MAX(ARCHIVE_DATE)
+|-
+| 2 || 1 || 2030-10-19 16:59:41.0 || 2031-04-07 19:11:17.0
+|-
+| 1 || 4 || 2021-04-19 11:31:44.0 || 2021-04-30 16:56:05.0
+|-
+| 1 || 2 || 2021-04-17 07:37:36.0 || 2021-04-19 07:36:06.0
 |}