Sterling SI/GIS: number of inbound and outbound documents grouped by customer

Just an SQL query, not nice and clear, but it works… It returns number of inbound/outbound documents for a period of time, grouped by customers and document types. Don’t forget that GIS/SI purges the old data so you cannot use it for “retrospective” reports…

SELECT * FROM (

SELECT
    TRUNC(TO_DATE('2012-08-02 07:00:00', 'yyyy-mm-dd HH24:MI:SS') - 1, 'HH24') as START_DATE,
    TO_DATE('2012-08-02 07:00:00', 'yyyy-mm-dd HH24:MI:SS') as END_DATE,
    SUM(cs5.VALUE) as CNT,
    cs1.VALUE as Partner,
    'Inbound' as Direction,
    cs2.VALUE as Status,
    cs4.VALUE as Transaction
FROM
correlation_set cs1,  -- TP
correlation_set cs2,  
correlation_set cs3, -- Direction
correlation_set cs4, -- Transaction
correlation_set cs5 -- TransactionCount
WHERE
    cs1.REC_TIME <= TO_DATE('2012-08-02 07:00:00', 'yyyy-mm-dd HH24:MI:SS')
    AND cs1.REC_TIME >= TRUNC(TO_DATE('2012-08-02 07:00:00', 'yyyy-mm-dd HH24:MI:SS') - 1, 'HH24')
AND cs1.NAME = 'InterchangeSenderID'
AND cs2.NAME = 'Status'
AND cs3.NAME = 'Direction'
AND cs3.VALUE = 'Inbound'
AND cs4.NAME = 'TransactionSetID'
AND cs4.VALUE != '997'
AND cs5.NAME = 'TransactionCount'
AND cs1.OBJECT_ID = cs2.OBJECT_ID
AND cs1.OBJECT_ID = cs3.OBJECT_ID
AND cs1.WF_ID = cs4.WF_ID
AND cs1.OBJECT_ID != cs4.OBJECT_ID -- to exclude duplicate EDIFACTs
AND cs4.OBJECT_ID = cs5.OBJECT_ID -- important!
GROUP BY cs1.VALUE, cs2.VALUE, cs4.VALUE

UNION ALL

SELECT
    TRUNC(TO_DATE('2012-08-02 07:00:00', 'yyyy-mm-dd HH24:MI:SS') - 1, 'HH24') as START_DATE,
    TO_DATE('2012-08-02 07:00:00', 'yyyy-mm-dd HH24:MI:SS') as END_DATE,
    SUM(cs5.VALUE) as Cnt,
    cs1.VALUE as Partner,
    cs2.VALUE as Direction,
    cs3.VALUE as Status,
    cs4.VALUE as Transaction
FROM
    correlation_set cs1,  -- TP
    correlation_set cs2, -- direction
    correlation_set cs3, -- status
    correlation_set cs4, --transaction id
    correlation_set cs5 -- # of transactions
WHERE
    cs1.REC_TIME <= TO_DATE('2012-08-02 07:00:00', 'yyyy-mm-dd HH24:MI:SS')
    AND cs1.REC_TIME >= TRUNC(TO_DATE('2012-08-02 07:00:00', 'yyyy-mm-dd HH24:MI:SS') - 1, 'HH24')
AND cs1.NAME = 'InterchangeReceiverID'
    AND cs2.NAME = 'Direction'
    AND cs2.VALUE = 'Outbound'
    AND cs3.NAME = 'Status' 
    AND cs4.NAME = 'TransactionSetID' 
    AND cs5.NAME = 'TransactionCount' 
    AND cs4.VALUE != '997'
    AND cs1.object_id = cs2.object_id
    AND cs1.object_id = cs3.object_id
    AND cs1.object_id = cs4.object_id
    AND cs1.object_id = cs5.object_id
GROUP BY cs1.VALUE, cs2.VALUE, cs3.VALUE, cs4.VALUE

)

ORDER BY Partner, Transaction, Status

Gennady Kim

Advertisements

One thought on “Sterling SI/GIS: number of inbound and outbound documents grouped by customer

  1. Pingback: GIS/SI: DB: Correlation Set | EDI shortcuts

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s