SQLTeam.com | Weblogs | Forums

Query help


#1

My data is like this.

Account# person# personname
100 1 John
100 2 Jill

101 1 Josh

102 1 Jack
102 2 Jarred
102 3 Johana

each account can have applicant 1, 2, 3 people on the account (there is always 1 person, both 2, 3 are optional).

Question:
In the out put I need to list only applicant 1, but indicate a flag if there is applicant 1 or 2 present on this account.
( in the above example account 100 has 2 applicants, 101 has just 1, 102 has 3)

account number # applicant1 name #, applicant2 present#(Y/N) applicant3 present# (Y/N)

100 John Y N
101 Josh N N
102 Jack Y Y


#2
 DECLARE @tAccount TABLE
 (    Account INT,
    Person TINYINT,
    PersonName VARCHAR(30))
INSERT INTO @tAccount(Account,Person,PersonName)
VALUES (100,1,'John'),(100, 2, 'Jill'),
    (101, 1, 'Josh'),
    (102, 1, 'Jack'),(102, 2, 'Jarred'),(102, 3, 'Johana')


SELECT
    Account
    ,MAX(CASE WHEN Person = 1 THEN PersonName ELSE '' END) AS PersonName
    ,MAX([applicant2 present]) AS [applicant2 present]
    ,MAX([applicant3 present]) AS [applicant3 present]
FROM
(
SELECT
    Account, PersonName , Person
    ,CASE WHEN Person = 2 THEN 'Y' ELSE 'N' END AS [applicant2 present]
    ,CASE WHEN Person = 3 THEN 'Y' ELSE 'N' END AS [applicant3 present]
FROM
    @tAccount    
)A
GROUP BY Account


SELECT
    Account,PersonName
    ,[applicant2 present]
    ,[applicant3 present]
FROM 
    @tAccount AS A1
    OUTER APPLY
    (    SELECT
            MAX(CASE WHEN Person = 2 THEN 'Y' ELSE 'N' END) AS [applicant2 present]
            ,MAX(CASE WHEN Person = 3 THEN 'Y' ELSE 'N' END) AS [applicant3 present]
        FROM 
            @tAccount AS A2
        WHERE
            A2.Account = A1.Account    
    )A3    
WHERE
    Person = 1

Account    PersonName    applicant2 present    applicant3 present
100    John    Y    N
101    Josh    N    N
102    Jack    Y    Y

#3

The same result but different approach

SELECT
    Account
    ,PersonName AS [applicant1 name]
    ,CASE WHEN NoApplicants >= 2 THEN 'Y' ELSE 'N' END AS [applicant2 present]
    ,CASE WHEN NoApplicants >= 3 THEN 'Y' ELSE 'N' END AS [applicant3 present]
FROM
(
    SELECT
        Account,PersonName,Person
        ,COUNT(Account) OVER(PARTITION BY Account) AS NoApplicants
    FROM
        @tAccount
)A
WHERE
    A.Person = 1