April 9, 2019 at 3:59 pm
Hi All,
I have a table named AccountProfile which has a column named AccountNumber along with other columns.
I have another two tables named AbsPlan and RitPlan.
These two tables has the same column named AccountNumber along with other columns like AccountEffectivedate , AccountStatus (Open/Closed) etc.
AccountNumber is unique in AccountProfile table whereas it has multiple rows in the other two tables based on various AccountEffectiveDate (1990,1991 ...etc)...
I need to pull a report from the AccountProfile table based on below conditions.
1. If the AccountStatus is closed for all the AccountEffectivedate for a particular account then those accounts should not be included in the report.
2. If atleast one is having an AccountStatus of Open in either of the two tables (AbsPlan and RitPlan) then those account need to be included in the report.
I tried below query to get the account numbers first from the two tables, later I thought of using In condition to get the report from AccountProfile table but im not getting the report as expected. Can you please guide me .
select distinct a.AccountNumber from AbsPlan a
join RitPlan r
on r.AccountNumber=a.AccountNumber
and r.AccountEffectivedate =a.AccountEffectivedate
where (a.AccountStatus not in (1)or r.AccountStatus not in(1)); ---not in 1 means not in open status.
April 9, 2019 at 6:10 pm
Do you want to list inactive data in the report?
If not, you can just join to the other tables including "AccountStatus IN (1)" in the JOIN ON clause, something like below:
SELECT ...
FROM AccountProfile ap
LEFT OUTER JOIN AbsPlan a ON a.AccountNumber = ap.AccountNumber AND a.AccountStatus IN (1)
LEFT OUTER JOIN RitPlan r ON r.AccountNumber = ap.AccountNumber AND r.AccountStatus IN (1)
Since you didn't give any details about what you want to list in the report, and how to list it, it's hard to give any specific advice.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
April 9, 2019 at 7:14 pm
It's always venturous to write a query only from a verbal description, as some details may be get lost. Thus, I recommend that you always include CREATE TABLE statements for your table definitions and INSERT statements with sample data, enough to illustrate all angles of the problem. And of course the desired result given the sample.
As read your question, it seems to be that AccountEffectiveDate can be ignored. Here is a query:
; WITH PlanUnion AS (
SELECT AccountNumber, AccountStatus
FROM RitPplan
UNION ALL
SELECT AccountNumber, AccountStatus
FROM AbsPlan
)
SELECT ap.AccountNumber, ap.AccountName, etc
FROM AccountProfile ap
WHERE EXISTS (SELECT *
FROM PlanUnion PU
WHERE PU.AccountNumber = ap.AccountNumber
AND PU.AccountStatus = 'Open')
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
April 9, 2019 at 7:40 pm
It sounds like you want something like this:
SELECT …
FROM AccountProfile ap
WHERE EXISTS
(
SELECT AccountNumber
FROM AbsPlan a
WHERE a.AccountNumber = ap.AccountNumber
AND a.AccountStatus = 'Open'
UNION ALL
SELECT AccountNumber
FROM RitPlan r
WHERE r.AccountNumber = ap.AccountNumber
AND r.AccountStatus = 'Open'
)
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply