SQL Server – Case Statement for Same Month

casesql server

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.

Best 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);
Related Question