1

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

my Script :

    SELECT CUSTOMER, 
CASE WHEN SUM(FLAG) > 0 THEN 'X' ELSE 'Y' END FLAG 
FROM (
    SELECT CUSTOMER, CALMONTH, SUM(INVCD_VAL),
    CASE WHEN INVCD_VAL <> 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 :

enter image description here

Thanks alot for your answer.

1

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 <> 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);
  • thx a lot Randi. this solved my issue. – fersps Mar 26 at 18:35
  • @fersps that is good to hear! Happy to help, glad it solved your issue =) – Randi Vertongen Mar 26 at 19:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.