Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Find client accounts missing a specific service Expand / Collapse
Author
Message
Posted Monday, April 15, 2013 7:51 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 5:15 AM
Points: 9, Visits: 39
I am looking for the right way to determine a missing service from all similar client accounts. We have six different regions with multiple clients. Only One region has this specific service for all of its regional clients. This service is mandatory, so all client accounts must have/show this service. Each client account has a "Portfolio" containing all the offered services.

What I want to do is to run a query to find all portfolios for Region 1 where this specific service is missing for this one region. Since all Portfolios across the six regions are set up the same way a simple WHERE NOT EXIST statement will return thousands of records that are irrelevant since they don't contain this service already.

The Code I am trying to use is as follows:

SELECT dbo.ACCOUNT.PORTFOLIO
FROM dbo.ACCOUNT INNER JOIN
dbo.SERVICE_JOIN ON dbo.ACCOUNT.PORT_ROW_ID =
dbo.SERVICE_JOIN.PORTFOLIO_ID INNER JOIN
dbo.SERVICES ON dbo.SERVICE_JOIN.SERVICE_ID =
dbo.SERVICES.ROW_ID INNER JOIN
dbo.CONTACT_JOIN ON dbo.SERVICE_JOIN.SERVICE_ID =
dbo.CONTACT_JOIN.SERVICE_ID INNER JOIN
dbo.CONTACTS ON dbo.CONTACT_JOIN.CONTACT_ID =
dbo.CONTACTS.ROW_ID FULL OUTER JOIN
dbo.NOTES ON dbo.SERVICES.SERVICE_TYPE =
dbo.NOTES.SERVICE_TYPE AND
dbo.ACCOUNT.ACC_ROW_ID = dbo.NOTES.ACCOUNT_ID
WHERE EXISTS
(SELECT dbo.ACCOUNT.PORTFOLIO
FROM dbo.ACCOUNT WHERE dbo.CLIENT.REGION = 'R1')
WHERE NOT EXISTS
(SELECT dbo.ACCOUNT.PORTFOLIO
FROM dbo.ACCOUNT
WHERE dbo.DM_SERVICES.SERVICE = 'MISSING_SERVICE')


My reasoning is that I am trying to find the portfolios where the service is missing and trying to restrict the search to the one region so we may add them back in. I have included the full code containing the joins for multiple tables where the combined informaton for the accounts are which is correct. My issue seems to begin after the where clause.

My error message is this:

"Msg 156, Level 15, State 1, Line 13
Incorrect syntax near the keyword 'WHERE'."

Line 13 ia actually the WHERE NOT EXIST statement.

Any tips and suggestions would be greatly appreciated.

James
Post #1442305
Posted Monday, April 15, 2013 7:55 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 7:11 PM
Points: 12,876, Visits: 31,789
your outer query has Two WHERe statmeents; the second one should be an AND, i think:
SELECT
dbo.ACCOUNT.PORTFOLIO
FROM dbo.ACCOUNT
INNER JOIN dbo.SERVICE_JOIN
ON dbo.ACCOUNT.PORT_ROW_ID = dbo.SERVICE_JOIN.PORTFOLIO_ID
INNER JOIN dbo.SERVICES
ON dbo.SERVICE_JOIN.SERVICE_ID = dbo.SERVICES.ROW_ID
INNER JOIN dbo.CONTACT_JOIN
ON dbo.SERVICE_JOIN.SERVICE_ID = dbo.CONTACT_JOIN.SERVICE_ID
INNER JOIN dbo.CONTACTS
ON dbo.CONTACT_JOIN.CONTACT_ID = dbo.CONTACTS.ROW_ID
FULL OUTER JOIN dbo.NOTES
ON dbo.SERVICES.SERVICE_TYPE = dbo.NOTES.SERVICE_TYPE
AND dbo.ACCOUNT.ACC_ROW_ID = dbo.NOTES.ACCOUNT_ID
WHERE EXISTS (SELECT
dbo.ACCOUNT.PORTFOLIO
FROM dbo.ACCOUNT
WHERE dbo.CLIENT.REGION = 'R1')
AND NOT EXISTS (SELECT
dbo.ACCOUNT.PORTFOLIO
FROM dbo.ACCOUNT
WHERE dbo.DM_SERVICES.SERVICE = 'MISSING_SERVICE')


also, you may have modified an existing query, but as it stands, if this is returning just the one column dbo.ACCOUNT.PORTFOLIO
FROM dbo.ACCOUNT there is no reason to have lall those other joins...they just slow down the query, and you only need the WHERe...AND


Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1442310
Posted Monday, April 15, 2013 10:15 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 5:15 AM
Points: 9, Visits: 39
Thanks Lowell for the quick reply. But when I altered the code as you presented it the query returned nothing. I would be satisfied if that were the case but I know that there are account portfolios missing this service.

Best Regards,
Post #1442394
Posted Monday, April 15, 2013 10:33 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 7:35 PM
Points: 22,992, Visits: 31,471
It is difficult to provide correct answers to questions when we can't see what you see.

Please read the first article I reference below in my signature block. It will walk you through the things you need to post and how to post them to get the best answers quickly and get tested code in return.

When asking for sample data, we know that you can't post actual company data but we do expect you to be able to put together sample data that mirros the type of data in your production systems.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1442402
Posted Monday, April 15, 2013 10:49 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 7:11 PM
Points: 12,876, Visits: 31,789
jbulldog (4/15/2013)
Thanks Lowell for the quick reply. But when I altered the code as you presented it the query returned nothing. I would be satisfied if that were the case but I know that there are account portfolios missing this service.

Best Regards,


yeah the pseudo code you posted makes it hard to really see any issues.
use the IF code tags, and paste the real query you are testing with , at a minimum.
The suggestion about DDL and sample data would allow any of the volunteers here to give you a working, tested solution, instead of guesses.

you also might consider using CTE's to organize the data , but it really depends on your query.



Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1442412
Posted Tuesday, April 16, 2013 6:34 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 5:15 AM
Points: 9, Visits: 39
Hey Lowell this the actual code I ran(below), the only changes were to the table and column names. What was returned was the PORTFOLIO column and 0 rows. As I said before I know there are some portfolios with this missing service. Or to rephrase it there are some portfolios that should have this service but it is now missing. Reducing the number of joins to just three tables necessary seemed to increase the length of the query time immensely.

SELECT
dbo.ACCOUNT.PORTFOLIO
FROM dbo.ACCOUNT
INNER JOIN dbo.SERVICE_JOIN
ON dbo.ACCOUNT.PORT_ROW_ID = dbo.SERVICE_JOIN.PORTFOLIO_ID
INNER JOIN dbo.SERVICES
ON dbo.SERVICE_JOIN.SERVICE_ID = dbo.SERVICES.ROW_ID
INNER JOIN dbo.CONTACT_JOIN
ON dbo.SERVICE_JOIN.SERVICE_ID = dbo.CONTACT_JOIN.SERVICE_ID
INNER JOIN dbo.CONTACTS
ON dbo.CONTACT_JOIN.CONTACT_ID = dbo.CONTACTS.ROW_ID
FULL OUTER JOIN dbo.NOTES
ON dbo.SERVICES.SERVICE_TYPE = dbo.NOTES.SERVICE_TYPE
AND dbo.ACCOUNT.ACC_ROW_ID = dbo.NOTES.ACCOUNT_ID
WHERE EXISTS (SELECT
dbo.ACCOUNT.PORTFOLIO
FROM dbo.ACCOUNT
WHERE dbo.CLIENT.REGION = 'R1')
AND NOT EXISTS (SELECT
dbo.ACCOUNT.PORTFOLIO
FROM dbo.ACCOUNT
WHERE dbo.DM_SERVICES.SERVICE = 'MISSING_SERVICE')

Now removing the NOT from the AND NOT EXISTS statement actually returns the same results. 0 rows.

Is it possible this is the wrong path to a solution?

How do you query to find a missing record that should be present with a criteria to filter the selection then?
Post #1442707
Posted Tuesday, April 16, 2013 7:38 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 7:11 PM
Points: 12,876, Visits: 31,789
really?
to me, the WHERE just does not look right:

I see two huge issues:
first, each EXISTS seem to reference THREE tables, and there's no joins...i don't even think it should work:
WHERE  EXISTS (SELECT
dbo.ACCOUNT.PORTFOLIO
FROM dbo.ACCOUNT
WHERE dbo.CLIENT.REGION = 'R1')

that's dbo.ACCOUNT from the inner query (maybe?) and dbo.CLIENT.REGION from the outer query, with no criteria to join the two within the EXISTs.,

the same thing with the second EXISTS :
SELECT
dbo.ACCOUNT.PORTFOLIO
FROM dbo.ACCOUNT
WHERE dbo.DM_SERVICES.SERVICE = 'MISSING_SERVICE')

where is dbo.DM_SERVICES joined to dbo.ACCOUNT?

both of those end up being a cross join, whihch i assume makes it slow, and probably never filters much data.

The other thing is, for an exists, i always select a constant, and not a column name for clarity.

i would select a constant in my exists, instead of the column from the outer query;
something like this looks a little better to me.

SELECT
acct.PORTFOLIO
FROM dbo.ACCOUNT acct
INNER JOIN dbo.SERVICE_JOIN
ON acct.PORT_ROW_ID = dbo.SERVICE_JOIN.PORTFOLIO_ID
INNER JOIN dbo.SERVICES
ON dbo.SERVICE_JOIN.SERVICE_ID = dbo.SERVICES.ROW_ID
INNER JOIN dbo.CONTACT_JOIN
ON dbo.SERVICE_JOIN.SERVICE_ID = dbo.CONTACT_JOIN.SERVICE_ID
INNER JOIN dbo.CONTACTS
ON dbo.CONTACT_JOIN.CONTACT_ID = dbo.CONTACTS.ROW_ID
FULL OUTER JOIN dbo.NOTES
ON dbo.SERVICES.SERVICE_TYPE = dbo.NOTES.SERVICE_TYPE
AND acct.ACC_ROW_ID = dbo.NOTES.ACCOUNT_ID
--testing if related data exists or not
WHERE EXISTS (SELECT
1
FROM dbo.CLIENT cl
WHERE cl.REGION = 'R1'
AND cl.?PORTFOLIO? = acct.PORTFOLIO)
--testing if related data exists or not
AND NOT EXISTS (SELECT
1
FROM dbo.DM_SERVICES sv
WHERE sv.SERVICE = 'MISSING_SERVICE'
AND sv.?PORTFOLIO? = acct.PORTFOLIO)




Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1442738
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse