Question :
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.
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 <> 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);