Sql case

Other SQL Server Topics
1

Hi,

Apologies if this is a stupid question but my brain has decided to switch off and I cant work out how to do what i need.

What am i trying to achieve?
I have 2 tables, one table lets say Accounts and another table called Equipment.

On the accounts there's a marker which will say if that account has a "Cooler" installed (called Cooler_Installed) another marker to say if an "Ice Maker" is installed (called Ice_Installed) and they're both a TRUE or FALSE value.

On the Equipment table it contains Equipment ID, Account ID and Equipment Type (value as 'COOLER' OR 'ICE'). There could be 2 rows of data in this table if an account has both a Cooler installed and also an Ice Maker.

What I'd like to do is run an SQL query which will display all the accounts that have the markers on the account that DONT actually have equipment installed anymore.

i.e. Account ID 1234 has Cooler_Installed = TRUE and Ice_Installed = TRUE on the account, but the ice maker has been removed from that account as no record in the equipment table with the Account ID 1234 and the Equipment Type as ICE exists anymore.

Expect Results:

Based on the example above I'd like the output to display all the Account IDs and the Equipment Type where the equipment is no longer installed. So based on the example above it would be:

Columns:
Account_ID
Equipment_Type

Data:
1234
ICE

I hope that makes sense and thank you in advance.

2

Welcome

There are no stupid questions. In order to help us help you please provide sample data as follows

declare @equipments(id int, namevarchar(10)
insert into @equipments
select 1, 'Cooler' --etc

declare @accounts table(id int, accountnum varchar(10), equipmentid int)
insert into @accounts
select 1, 'SS8989', 1 --etc

with all the relevant columns and data types and near to real data sample

3

Hi,

Thank you for your support. I cant do this as I'm using Salesforce Marketing Cloud but I've provided example data for you (Below). You'll see 3 parts:.

Accounts - Account information
Equipment - Which holds the equipment information
Whats Removed - Shows what has changed between Accounts and Equipment

Accounts

Account_ID	Cooler_Installed	Ice_Installed
1	TRUE	FALSE
2	FALSE	TRUE
3	FALSE	FALSE
4	TRUE	TRUE
5	TRUE	TRUE
6	TRUE	FALSE
7	FALSE	TRUE
8	FALSE	FALSE
9	TRUE	TRUE
10	FALSE	FALSE

Equipment

Equipment_ID	Account_ID	Equipment_Type
435436	1	COOLER
234235	2	ICE
7568768	4	COOLER
75342	5	ICE
436567	6	ICE
78679	7	ICE
89087	9	ICE
754343	4	ICE
575333	9	COOLER

Whats Removed

Account_ID	Equipment_Type
5	COOLER
6	COOLER

The Whats Remove part is what I'm wanting to achieve, to get a list of account IDs that have the "Cooler_Installed" as TRUE but no equipment type under the Account ID is found in the equipment table, the same goes for "Ice_Installed" as TRUE but no equipment type under the account id is found in the equipment table.

I hope that makes sense.

4

You are not being asked to do it in SF. Just here on this forum. that is how one provides sample data so you help us help you.

5 

SELECT e.*
FROM Accounts a
CROSS APPLY (
    SELECT a.Account_ID, 'COOLER' AS Equipment_Type
    WHERE a.Cooler_Installed = 'TRUE' AND NOT EXISTS(
        SELECT 1
        FROM Equipment e
        WHERE e.Account_ID = a.Account_ID AND e.Equipment_Type = 'COOLER')
    UNION ALL
    SELECT a.Account_ID, 'ICE' AS Equipment_Type
    WHERE a.Ice_Installed = 'TRUE' AND NOT EXISTS(
        SELECT 1
        FROM Equipment e
        WHERE e.Account_ID = a.Account_ID AND e.Equipment_Type = 'ICE')
) AS e
WHERE (a.Cooler_Installed = 'TRUE' OR a.Ice_Installed = 'TRUE')
6

Thank you, I'll give this a try over the weekend and let you know how I get on. Thanks again :slight_smile:

7

Hi Scott,

Thank for you this, I've just been looking at this and there seems to be a syntax issue, but as I've never used the CROSS APPLY function before I'm not sure where the error is. Please could you take a look into this for me?

Thank you again for the help on this.

8

Syntax is fine for SQL Server (which is the dbms for this forum).

Are you using a different dbms?

9

Hi Scott,

I'm using Salesforce Marketing Cloud which uses SQL Server 2016 when running an SQL Query. I've also ran it through eversql syntax checker and that's showing the below:

You have an error in your SQL syntax; it seems the error is around: 'APPLY ( SELECT a.Account_ID, 'COOLER' AS Equipment_Type WHER' at line 5

Apologies if your answer is correct, it may just not work for me in SFMC for some reason :disappointed: