0

I have questions around SQL join.

I have 2 tables customer and events One customer can have optin/attend/Cancel multiple events. One customer can have Confirmed/planning/Cancel multiple Orders.

now I am fetching the events, Order, Product details in a target DE with customer details.

    SELECT Acc.*,
    CASE
        WHEN Eve.Status__c ='In Progress' and Eve.Subject ='Event'  THEN 1
        ELSE 0
    END as [HasBookedEvent],


    CASE
        WHEN Eve.Status__c ='Completed' and Eve.Subject ='Event' THEN 1
        ELSE 0
    END as [HasCompletedEvent],


    CASE
        WHEN (Ord.Status='Confirmed' OR Ord.Status='planning') AND Ord.Type='Fleet' and Prod.Model__c='P2' THEN 1
        ELSE 0
    END as [HasActive_P2_FleetOrder],
    CASE
        WHEN (Ord.Status='Confirmed' OR Ord.Status='planning') AND Ord.Type IN('Order','configured','Remarketed') and Prod.Model__c='P2' THEN 1
        ELSE 0
    END as [HasActive_P2_RetailOrder],
    CASE
        WHEN (Ord.Status='Delivered' OR Ord.Status='Partially') AND Ord.Type IN('Order','configured','Remarketed') and Prod.Model__c='P2' THEN 1
        ELSE 0
    END as [HasDelivered_P2]


    FROM [Account_Salesforce] Acc
    LEFT JOIN [Event_Salesforce] Eve ON Eve.AccountId = Acc.Id
    LEFT JOIN [Order_Salesforce] Ord ON Ord.AccountId= Acc.Id
    LEFT JOIN [Product_Salesforce] Prod ON Prod.Id= Ord.Product__c

One of the customer has two events, one is Inprogress and other one is completed. In this case 'HasCompletedEvent' is becoming '1' but not HasBookedEvent.

Same way One of the customer has two Orders(confirmed and Planning). In this case 'HasActive_P2_FleetOrder' should become '1' but it is not happening.

These problem arises only when customer has more then one rows in event/order table.

Do you know why ? How can I make both values 1 when customer has multiple rows in events/Order using CASE statement.

Target DE fields are CID, Cemail,HasBookedEvent,HasCompletedEvent,HasActive_P2_FleetOrder,HasActive_P2_RetailOrder,HasDelivered_P2

Thanks

2
  • Why don't you use inner join ? Mar 26, 2021 at 10:44
  • @SwatiMishra It will generate only matching records but I was all records from table1 and matching rows from table2. Mar 26, 2021 at 11:01

1 Answer 1

0

Please try below SQL.

SELECT Acc.Id
    ,MAX(CASE 
            WHEN Eve.Status__c = 'In Progress'
                AND Eve.Subject = 'Event'
                THEN 1
            ELSE 0
            END) AS [HasBookedEvent]
    ,MAX(CASE 
            WHEN Eve.Status__c = 'Completed'
                AND Eve.Subject = 'Event'
                THEN 1
            ELSE 0
            END) AS [HasCompletedEvent]
    ,MAX(CASE 
            WHEN (
                    Ord.STATUS = 'Confirmed'
                    OR Ord.STATUS = 'planning'
                    )
                AND Ord.Type = 'Fleet'
                AND Prod.Model__c = 'P2'
                THEN 1
            ELSE 0
            END) AS [HasActive_P2_FleetOrder]
    ,MAX(CASE 
            WHEN (
                    Ord.STATUS = 'Confirmed'
                    OR Ord.STATUS = 'planning'
                    )
                AND Ord.Type IN (
                    'Order'
                    ,'configured'
                    ,'Remarketed'
                    )
                AND Prod.Model__c = 'P2'
                THEN 1
            ELSE 0
            END) AS [HasActive_P2_RetailOrder]
    ,MAX(CASE 
            WHEN (
                    Ord.STATUS = 'Delivered'
                    OR Ord.STATUS = 'Partially'
                    )
                AND Ord.Type IN (
                    'Order'
                    ,'configured'
                    ,'Remarketed'
                    )
                AND Prod.Model__c = 'P2'
                THEN 1
            ELSE 0
            END) AS [HasDelivered_P2]
FROM [Account_Salesforce] Acc
LEFT JOIN [Event_Salesforce] Eve ON Eve.AccountId = Acc.Id
LEFT JOIN [Order_Salesforce] Ord ON Ord.AccountId = Acc.Id
LEFT JOIN [Product_Salesforce] Prod ON Prod.Id = Ord.Product__c
GROUP BY Acc.Id
3
  • Small change in my query, I want to select all field from account and case statement fields from event and order table. Select Acc.* Mar 26, 2021 at 10:57
  • then add that list in select clause as well as in Group by clause. Mar 26, 2021 at 13:12
  • Can you first check above SQL is working or not? if it is working then you can add the extra fields in Select and Group by Clause. Mar 26, 2021 at 13:14

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.