March 8, 2017 at 2:07 pm
I have a situation where I am thinking of using EXISTS or NOT EXISTS function.
I have a scenario where some of the records in my table has NAME TYPE of 0, 1, 2, 3, etc.
If an account in my NAME table has 0 and 2 record types, I only want to retrieve record type 2.
If an account in my NAME table has 0, 1, 3 record types, I only want to retrieve record type 0.
Basically, if record type 2 exists, give me record type with 2. If record type 2 does not exist, then give me 0.
Every record should always have record type of 0.
Example of data:
PARENTACCOUNT TYPE Street City State Zipcode
0000441985 0 2099 KIMBERWICKE CIR OVIEDO FL 32765-7578
0000015190 0 10201 GROSVENOR PL APT 1104 NORTH BETHESDA MD 20852-4618
0000015190 2 10201 GROSVENOR PL APT1104 ROCKVILLE MD 20852-4618
As you can see, account 0000441985 has only TYPE 0, So I want this record. Account 0000015190 has TYPE 0 and 2, so I want TYPE 2 record.
Here is sample of my query:
SELECT
N.PARENTACCOUNT
,n.TYPE
,n.Street
,n.City
,n.State
,n.Zipcode
FROM DBO.NAME n
WHERE n.PARENTACCOUNT = '0000015190'
AND n.ProcessDate = '20170306'
AND n.TYPE IN (0,2)
AND EXISTS (SELECT 1 FROM DBO.NAME mailing WHERE mailing.ProcessDate = '20170306' AND mailing.TYPE = 2 AND mailing.PARENTACCOUNT = n.PARENTACCOUNT)
It does not work the way I want it.
Any help is appreciate it.
Thank you
March 8, 2017 at 4:44 pm
I would use a NOT EXISTS to exclude the records with a type 2, and then UNION that to a query that only picks up type 2, like this:--get all the type 0 where there is no type 2
SELECT
N.PARENTACCOUNT
,n.TYPE
,n.Street
,n.City
,n.State
,n.Zipcode
FROM
DBO.NAME n
WHERE
n.ProcessDate = '20170306'
AND n.TYPE = 0
AND NOT EXISTS (SELECT 1 FROM DBO.NAME mailing WHERE mailing.ProcessDate = '20170306' AND mailing.TYPE = 2 AND mailing.PARENTACCOUNT = n.PARENTACCOUNT)
UNION ALL
--and get all the ones that have a type 2
SELECT
N.PARENTACCOUNT
,n.TYPE
,n.Street
,n.City
,n.State
,n.Zipcode
FROM
DBO.NAME n
WHERE
n.ProcessDate = '20170306'
AND n.TYPE = 2
Other folks might know more efficient ways of getting the same result.
March 9, 2017 at 5:50 am
Thank you for your response.
March 9, 2017 at 9:17 am
Garin T - Wednesday, March 8, 2017 2:07 PMI have a situation where I am thinking of using EXISTS or NOT EXISTS function.
I have a scenario where some of the records in my table has NAME TYPE of 0, 1, 2, 3, etc.
If an account in my NAME table has 0 and 2 record types, I only want to retrieve record type 2.
If an account in my NAME table has 0, 1, 3 record types, I only want to retrieve record type 0.
Basically, if record type 2 exists, give me record type with 2. If record type 2 does not exist, then give me 0.
Every record should always have record type of 0.
Do you have some sort of code or lookup table that these types are foreign keyed to? It seems what you really want is some sort of priority based on type, that you could sort on for each parent account, grouping by the parent account.
March 17, 2017 at 10:05 am
The standard way to return one row from a group based on a priority is to use ROW_NUMBER.
;
WITH CTE AS
(
SELECT
N.PARENTACCOUNT,
n.TYPE,
n.Street,
n.City,
n.State,
n.Zipcode,
ROW_NUMBER() OVER(PARTITION BY PARENTACCOUNT, ProcessDate ORDER BY n.Type DESC) AS rn
FROM DBO.NAME n
WHERE n.PARENTACCOUNT = '0000015190'
AND n.ProcessDate = '20170306'
AND n.TYPE IN (0,2)
)
SELECT *
FROM CTE
WHERE rn = 1
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply