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 :
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 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.
;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;
CUSTOMER HISTORY_FLAG 2340001 X 2340002 Y 2340003 X 2340004 Y 2340016 Y 2340017 X 2340018 X 2340019 X 2340020 X
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);