Case Statement for on same month i have a CASE statement, which classify my Customer-Revenue and set Flag, when Revenue &lt;> 0 is. But my problem i should search also same month record on my case statement. Logic : if two records (Revenue &amp; Credit) exist on same month and sum of those 2 records are zero then put flag. 

my Script : 

    SELECT CUSTOMER, 
CASE WHEN SUM(FLAG) &gt; 0 THEN 'X' ELSE 'Y' END FLAG 
FROM (
    SELECT CUSTOMER, CALMONTH, SUM(INVCD_VAL),
    CASE WHEN INVCD_VAL &lt;&gt; 0 THEN 1 ELSE 0 END AS FLAG
     FROM 
        (
        SELECT CALMONTH, CUSTOMER, INVCD_VAL FROM CUSTOMER_HISTORY
        UNION ALL
        SELECT LEFT(CALDAY, 6), SOLD_TO, REVENUE FROM CUST_HISTORY_MIS
        )
    GROUP BY CUSTOMER, CALMONTH, INVCD_VAL
    ) 
GROUP BY CUSTOMER
ORDER BY CUSTOMER


My Source and Target tables look like :



Thanks alot for your answer.
Posting the create table statements and insert statements instead of pictures could really help  the next time you ask a question. Less trouble for both parties as you will get a faster answer.

Having said that, below is a way to do it but there are a few considerations / uncertanties due:


the datatypes not being known
'?' returning 'Y' in your example resultset
...


Calculating it for each customer for each month:

;WITH CTE AS
(
        SELECT CALMONTH as CALMONTH, CUSTOMER, INVCD_VAL FROM CUSTOMER_HISTORY
        UNION ALL
        SELECT LEFT(CALDAY, 6) as CALMONTH, SOLD_TO, REVENUE as INVCD_VAL FROM CUST_HISTORY_MIS
)

SELECT DISTINCT CALMONTH,
                CUSTOMER, 
                CASE WHEN SUM(INVCD_VAL) OVER(PARTITION BY CALMONTH,CUSTOMER) = 0 then 'Y' else 'X' END AS HISTORY_FLAG
FROM CTE;


But as you will notice, this returns some more values than the result set specified, I also added CALMONTH.

Resultset

CALMONTH    CUSTOMER    HISTORY_FLAG
201701      2340002          Y
201701      2340003          X
201701      2340004          Y
201702      2340001          X
201703      2340001          X
201703      2340016          Y
2019-0      2340001          X
2019-0      2340017          X
2019-0      2340018          X
2019-0      2340019          X
2019-0      2340020          X


To get closer to your resulset, I decided that whenever a customer has a revenue that is &lt;> 0, the flag for that customer is set to 'X', even if he has a revenue of 0 in one of the months.
Query

;WITH CTE AS
(
        SELECT CALMONTH as CALMONTH, CUSTOMER, INVCD_VAL FROM CUSTOMER_HISTORY
        UNION ALL
        SELECT LEFT(CALDAY, 6) as CALMONTH, SOLD_TO, REVENUE as INVCD_VAL FROM CUST_HISTORY_MIS
)
, CTE2 AS
(
SELECT CALMONTH,CUSTOMER, CASE WHEN SUM(INVCD_VAL) OVER(PARTITION BY CALMONTH,CUSTOMER) = 0 then 'Y' else 'X' END AS HISTORY_FLAG
FROM CTE
)

SELECT CUSTOMER, MIN(HISTORY_FLAG) as HISTORY_FLAG
FROM CTE2 
GROUP BY CUSTOMER;


Resultset

CUSTOMER    HISTORY_FLAG
2340001         X
2340002         Y
2340003         X
2340004         Y
2340016         Y
2340017         X
2340018         X
2340019         X
2340020         X




Dataset used

CREATE TABLE Customer_history(CALMONTH char(6),CUSTOMER bigint,INVCD_VAL INT);

CREATE TABLE CUST_HISTORY_MIS(CALDAY date,SOLD_TO bigint, REVENUE   INT);

INSERT INTO Customer_history(CALMONTH,CUSTOMER,INVCD_VAL)
VALUES('201702',2340001,10),('201703',2340001,-10)
,('201701',2340002,40)
,('201701',2340002,-40)
,('201701',2340004,0)
,('201701',2340003,-20)
,('201703',2340016,0);


INSERT INTO CUST_HISTORY_MIS(CALDAY,SOLD_TO,REVENUE)
VALUES('20190203',2340001,5),
('20190203',2340001,5),
('20190301',2340019,11),
('20190330',2340017,12),
('20190311',2340018,NULL),
('20190313',2340020,NULL);