[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.sqlserver.programming

Need help with SELECT

Terri

3/30/2007 6:13:00 PM

How can I identify policies that do not have at least one associated account
that is active?

Desired results:
C-Only associated account is inactive
D-No associated accounts
F-Only associated account is inactive

CREATE TABLE #Accounts
(
AccountID int,
Active char (1)
)

CREATE TABLE #Policies
(
PolicyID char
)

CREATE TABLE #AccountPolicies
(
AccountID int,
PolicyID char
)

INSERT #Accounts (AccountID, Active) VALUES (1,'Y')
INSERT #Accounts (AccountID, Active) VALUES (2,'N')
INSERT #Accounts (AccountID, Active) VALUES (3,'Y')
INSERT #Accounts (AccountID, Active) VALUES (4,'N')

INSERT #Policies (PolicyID) VALUES ('A')
INSERT #Policies (PolicyID) VALUES ('B')
INSERT #Policies (PolicyID) VALUES ('C')
INSERT #Policies (PolicyID) VALUES ('D')
INSERT #Policies (PolicyID) VALUES ('E')

INSERT #AccountPolicies (AccountID,PolicyID) VALUES (1,'A')
INSERT #AccountPolicies (AccountID,PolicyID) VALUES (1,'B')
INSERT #AccountPolicies (AccountID,PolicyID) VALUES (2,'C')
INSERT #AccountPolicies (AccountID,PolicyID) VALUES (3,'A')
INSERT #AccountPolicies (AccountID,PolicyID) VALUES (3,'E')
INSERT #AccountPolicies (AccountID,PolicyID) VALUES (4,'A')

SELECT #Accounts.AccountID, #AccountPolicies.PolicyID FROM #Accounts INNER
JOIN #AccountPolicies ON #Accounts.AccountID = #AccountPolicies.AccountID

DROP TABLE #Accounts
DROP TABLE #Policies
DROP TABLE #AccountPolicies
2 Answers

Tom Moreau

3/30/2007 6:39:00 PM

0

Try:

select
p.*
from
#Policies p
where not exists
(
select
*
from
#AccountPolicies ap
join #Accounts a on ap.AccountID = a.AccountID
where
p.PolicyID = ap.PolicyID
and a.Active = 'Y'
)
go

BTW, Policy F does not exist.

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
.
"Terri" <terri@cybernets.com> wrote in message
news:eujjvk$eqv$1@reader2.nmix.net...
How can I identify policies that do not have at least one associated account
that is active?

Desired results:
C-Only associated account is inactive
D-No associated accounts
F-Only associated account is inactive

CREATE TABLE #Accounts
(
AccountID int,
Active char (1)
)

CREATE TABLE #Policies
(
PolicyID char
)

CREATE TABLE #AccountPolicies
(
AccountID int,
PolicyID char
)

INSERT #Accounts (AccountID, Active) VALUES (1,'Y')
INSERT #Accounts (AccountID, Active) VALUES (2,'N')
INSERT #Accounts (AccountID, Active) VALUES (3,'Y')
INSERT #Accounts (AccountID, Active) VALUES (4,'N')

INSERT #Policies (PolicyID) VALUES ('A')
INSERT #Policies (PolicyID) VALUES ('B')
INSERT #Policies (PolicyID) VALUES ('C')
INSERT #Policies (PolicyID) VALUES ('D')
INSERT #Policies (PolicyID) VALUES ('E')

INSERT #AccountPolicies (AccountID,PolicyID) VALUES (1,'A')
INSERT #AccountPolicies (AccountID,PolicyID) VALUES (1,'B')
INSERT #AccountPolicies (AccountID,PolicyID) VALUES (2,'C')
INSERT #AccountPolicies (AccountID,PolicyID) VALUES (3,'A')
INSERT #AccountPolicies (AccountID,PolicyID) VALUES (3,'E')
INSERT #AccountPolicies (AccountID,PolicyID) VALUES (4,'A')

SELECT #Accounts.AccountID, #AccountPolicies.PolicyID FROM #Accounts INNER
JOIN #AccountPolicies ON #Accounts.AccountID = #AccountPolicies.AccountID

DROP TABLE #Accounts
DROP TABLE #Policies
DROP TABLE #AccountPolicies

--CELKO--

3/31/2007 12:17:00 AM

0

>> How can I identify policies that do not have at least one associated account that is active?
Desired results:

C-Only associated account is inactive
D-No associated accounts
F-Only associated account is inactive <<

That sounds like it is a one-to-one, but there is nothing in your DDL
to ensure that. Here is a re-write with keys and constraints:

CREATE TABLE Accounts
(account_id INTEGER NOT NULL PRIMARY KEY,
active_flag CHAR(1) DEFAULT 'A' NOT NULL
CHECK(active_flag IN ('A', 'F'));

Should you use the dates of the policies (start, expire) and compute
if there is an active policy in effect? I would think that an account
has many policies, and not the other way around, but ...

CREATE TABLE Policies
(policy_id CHAR(10) NOT NULL PRIMARY KEY);

We need a key and constraints for the relationship table. Play with
the UNIQUE and PRIMARY KEY stuff to get 1:m, m:1 and 1:1 relationships
as needed.

CREATE TABLE AccountPolicies
(account_id INTEGER NOT NULL UNIQUE
REFERENCES Accounts,
policy_id CHAR(10) NOT NULL UNIQUE
REFERENCES Policies,
PRIMARY KEY (account_id, policy_id));

This will work even after you pick your uniqueness constraints:

SELECT P.account_id,
CASE WHEN MAX(A.active_flag) = 'A' THEN 'A'
WHEN MIN(A.active_flag) = 'F' THEN 'F'
ELSE 'D' END AS acct_status
FROM Policies AS P
LEFT OUTER JOIN
AccountPolicies AS AP
ON P.account_id = AP.account_id
GROUP BY P.account_id;