How to caluclate the outcome based on multiple records for same Customer

Question

Good Morning,

Trying to find out something here with this sample data below.

ISFUNDINGCUSTOMER = if a customer id has any of 4 cost fields has anynumber >= 0.01 then ISFUNDINGCUSTOMER = Y, for example Customer ID 100 has 3 records only one has 4.25 it means Custid100 is funding
ActiveFunding = IF any of CustomerStatus= " Active" and funding then Y esle N. for example cust100 Active recordes shows no payment so outcome is "N" where as custid 300 is "Y"

Can you please help me with this

Create Table Customers (CustomerID varchar(10), Subid varchar(10), cityid varchar(10), workid varchar(10), SubscriptionYear varchar(5), Cost1 varchar(10), Cost2 varchar(10), Cost3 varchar(10), NewCost4 varchar(10), CustomerStatus varchar(6))

INSERT INTO CUSTOMERS VALUES (100, 11,12, NULL, '20-21','','','','','CANCEL')
INSERT INTO CUSTOMERS VALUES (100, 11,12, NULL, '20-21','4.25','','','','CANCEL')
INSERT INTO CUSTOMERS VALUES (100, 11,12, 8762, '20-21','0.0','','','','ACTIVE')

INSERT INTO CUSTOMERS VALUES (200, 21,40, NULL, '20-21','','','','','CANCEL')
INSERT INTO CUSTOMERS VALUES (200, 21,40, NULL, '20-21','','','','','ACTIVE')

INSERT INTO CUSTOMERS VALUES (300, 251,12, NULL, '20-21','','','','','CANCEL')
INSERT INTO CUSTOMERS VALUES (300, 251,12, NULL, '20-21','','','','','CANCEL')
INSERT INTO CUSTOMERS VALUES (300, 145,12, 12 , '20-21','','3','','','ACTIVE')

INSERT INTO CUSTOMERS VALUES (400, 95,12, NULL, '20-21','','','20','','CANCEL')
INSERT INTO CUSTOMERS VALUES (400, 95,12, NULL, '20-21','','','','','CANCEL')
INSERT INTO CUSTOMERS VALUES (400, 95,12, 1030, '20-21',','10','','','ACTIVE')
INSERT INTO CUSTOMERS VALUES (400, 95,40, NULL, '20-21','','','','','CANCEL')
INSERT INTO CUSTOMERS VALUES (400, 95,40, 2040, '20-21','10','','','','ACTIVE')

INSERT INTO CUSTOMERS VALUES (500, 105,12, NULL, '20-21','','0.0','','','CANCEL')
INSERT INTO CUSTOMERS VALUES (500, 105,13, NULL, '20-21','','','','','CANCEL')
INSERT INTO CUSTOMERS VALUES (500, 105,14, 12, '20-21',','','','','ACTIVE')
INSERT INTO CUSTOMERS VALUES (500, 105,4, NULL, '20-21','','','','0.0','CANCEL')
INSERT INTO CUSTOMERS VALUES (500, 105,30, 20, '20-21','0.0','','','','ACTIVE')

INSERT INTO CUSTOMERS VALUES (600, 87,32, 138, '20-21','400','','','','ACTIVE')

INSERT INTO CUSTOMERS VALUES (700, 17,12, 18, '20-21','110','','','','CANCEL')

INSERT INTO CUSTOMERS VALUES (800, 317,324, 66, '20-21','','','','','CANCEL')

Expecting Outcome is

CREATE TABLE OUTCOME (CUSTOMERID varchar(10), ISFUNDINGCUSTOMER bit, FundingLevel int, ActiveFunding bit)

INSERT INTO OUTCOME VALUES (100,'Y',NULL,'N')
INSERT INTO OUTCOME VALUES (200,'N',NULL,'N')
INSERT INTO OUTCOME VALUES (300,'Y',NULL,'Y')
INSERT INTO OUTCOME VALUES (400,'Y',NULL,'Y')
INSERT INTO OUTCOME VALUES (500,'N',NULL,'N')
INSERT INTO OUTCOME VALUES (600,'Y',NULL,'Y')
INSERT INTO OUTCOME VALUES (700,'Y',NULL,'N')
INSERT INTO OUTCOME VALUES (800,'N',NULL,'N')

Thank you in advance
ASiti

Answer 1

For example:

;
with Q1 as
(
    select CustomerID, CustomerStatus,
        cast(Cost1 as money) as Cost1,
        cast(Cost2 as money) as Cost2,
        cast(Cost3 as money) as Cost3,
        cast(NewCost4 as money) as Cost4
    from Customers
),
Q2 as
(
    select *, 
        case when Cost1 >= 0.01 or Cost2 >= 0.01 or Cost3 >= 0.01 or Cost4 >= 0.01 then 1 else 0 end as f,
        case when CustomerStatus = 'ACTIVE' and (Cost1 > 0 or Cost2 > 0 or Cost3 > 0 or Cost4 > 0) then 1 else 0 end as af
    from Q1
)
select CustomerID,
    case when max(f) = 1 then 'Y' else 'N' end as ISFUNDINGCUSTOMER,
    null as FundingLevel,
    case when max(af) = 1 then 'Y' else 'N' end as ActiveFunding
from Q2
group by CustomerID
order by CustomerID

Answer 2

Hi @milan i ,

The datatype of ISFUNDINGCUSTOMER and ActiveFunding are bit, it could report 'Conversion failed when converting the varchar value 'Y' to data type bit.' error if you insert 'Y' or 'N' into these two columns. You could insert 1 or 0 instead.

Please also refer below:

;with cte as (  
select *, case when cast(Cost1 as money) >= 0.01 or cast(Cost2  as money) >= 0.01 or cast(Cost3 as money) >= 0.01 or cast(NewCost4 as money) >= 0.01 then 1 else 0 end isfund  
from CUSTOMERS)  
select CustomerID,max(case when isfund=1 then 1 else 0 end) ISFUNDINGCUSTOMER, NULL FundingLevel,  
 max(case when isfund=1 and CustomerStatus='ACTIVE' then 1 else 0 end) ActiveFunding  
from cte  
group by CustomerID  

Output:

CUSTOMERID	ISFUNDINGCUSTOMER	FundingLevel	ActiveFunding  
100	1	NULL	0  
200	0	NULL	0  
300	1	NULL	1  
400	1	NULL	1  
500	0	NULL	0  
600	1	NULL	1  
700	1	NULL	0  
800	0	NULL	0  

Best regards,
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.