Apex Code Development You need to sign in to do that
Don't have an account?
Saania EXIST statement equivalent in SOQL
HI,
The statement I want to execute is ... 'Clients that did not have a sale last FY, but did buy a product in last 3 years'.
That is, if a client bought a product in FY 2005, but not in FY 2007 (which was the previous FY), I want the client to show up in the list.
In my SQL Server table, we were using the DISTINCT and EXIST statements to retrieve such clients. The clients were matched on the products they bought and the fice(a unique id for the client). But the allsls (All Sales) table can have multiple entries for the same fice and product, for a given client.
In SOQL since these statements are not available, how to accomplish this?
The SQL statement is as:
I would definately appreciate any help to solve this query.
Thanks,
The statement I want to execute is ... 'Clients that did not have a sale last FY, but did buy a product in last 3 years'.
That is, if a client bought a product in FY 2005, but not in FY 2007 (which was the previous FY), I want the client to show up in the list.
In my SQL Server table, we were using the DISTINCT and EXIST statements to retrieve such clients. The clients were matched on the products they bought and the fice(a unique id for the client). But the allsls (All Sales) table can have multiple entries for the same fice and product, for a given client.
In SOQL since these statements are not available, how to accomplish this?
The SQL statement is as:
Code:
SELECT distinct sc.fice, sc.state, sc.product
FROM dbo.allsls AS sc (NOLOCK)
WHERE sc.trxdate BETWEEN '07/01/2004' AND '06/30/2006' -- Colleges bought 3 to 4 FY ago (2005-2006) (i.e. not last FY year, but last 3)
AND NOT EXISTS (SELECT t1.product FROM dbo.allsls as t1 (NOLOCK)
WHERE t1.product = sc.product AND t1.fice = sc.fice
AND t1.trxdate BETWEEN '07/01/2006' AND '06/30/2008' -- Didn't buy current FY (2008) or prev FY (2007)
)I would definately appreciate any help to solve this query.
Thanks,
werewolfI think you'll need at least 2 queries to get this data in Salesforce. You'll have to query clients that did not have a sale last FY, and then query clients that did have a sale in the prior 3 FYs, and then compare the 2 results.
SaaniaThanks, I was thinking of doing something similar ... was just hoping for a better solution. Thanks once again.