Subquery Question

Question

Subquery Question

SQLPain

Hall of Fame

Points: 3522
More actions
July 12, 2016 at 2:54 pm

#315016

I am using the following query to see how many apps were approved, funded, etc from the following facilitators:
SELECT
C.Facilitator
,ISNULL((SELECT COUNT(C.AppID) WHERE C.Status = 'Funded'), 0) AS [Funded Apps]
,ISNULL((SELECT COUNT(C.AppID) WHERE C.Status = 'Approved'), 0) AS [Approved Apps]
,ISNULL((SELECT COUNT(C.AppID) WHERE C.Status = 'Declined'), 0) AS [Declined Apps]
,ISNULL((SELECT COUNT(C.AppID) WHERE C.Status = 'Counter'), 0) AS [Counter Apps]
FROM
(
SELECT
B.AppID
,B.Status
, CASE WHEN B.Facilitator LIKE 'First%App%' THEN 'First Approve'
WHEN B.Facilitator LIKE '%May%' OR B.Facilitator LIKE '' OR B.Facilitator LIKE '%Bil%'
OR B.Facilitator LIKE '%Laf%' OR B.Facilitator LIKE '%Hoa%'
OR B.Facilitator LIKE 'Ke%' OR B.Facilitator LIKE 'Tom%'
THEN 'No Fac.'
ELSE B.Facilitator END AS [Facilitator]
FROM
(
SELECT
A.Facilitator
,A.AppID
,A.Status
FROM
(
SELECT DISTINCT
CA.application_id AS [AppID]
,CASE WHEN LEFT(SO.name, 1) <> LEFT(SO.user_data5, 1) THEN SO.user_data5 ELSE '' END [Facilitator]
,CASE WHEN CA.dec_status = 'A' AND CA.when_funded IS NULL THEN 'Approved'
WHEN CA.dec_status = 'A' AND CA.when_funded IS NOT NULL THEN 'Funded'
WHEN CA.dec_status = 'D' THEN 'Declined'
WHEN CA.dec_status = 'C' THEN 'Counter'
ELSE 'N/A' END AS [Status]
FROM credit AS CA
LEFT JOIN sooffice AS SO ON CA.src_office_id = SO.src_office_id
WHERE SO.is_disabled = 0
AND (CA.dec_status = 'A' OR CA.dec_status = 'D' OR CA.dec_status = 'C')
) AS A
LEFT JOIN credit AS CA ON CA.application_id = A.[AppID]
Where (A.Status = 'Funded' OR A.Status = 'Approved' OR A.Status = 'Declined' OR A.Status = 'Counter')
) AS B
LEFT JOIN credit AS CA ON CA.application_id = B.AppID
) AS C
LEFT JOIN credit AS CA ON CA.application_id = C.AppID
GROUP BY C.Facilitator, C.Status
The result set I am getting is:
Facilitator.........Funded Apps......Approved Apps......Declined Apps..........Counter Apps
Blue ...............0.......................0.................................0................1
Blue................0..............................0.....................................27..................0
Blue................0..............................11.......................................0............0
Blue..............1..............................0....................................0..............0
What I really need is :
Facilitator.........Funded Apps......Approved Apps......Declined Apps..........Counter Apps
Blue ...............1.......................11.................................27................1
Can somebody suggest what is wrong with it.

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

SQLPain (7/12/2016)
I am using the following query to see how many apps were approved, funded, etc from the following facilitators:
SELECT DISTINCT
C.Facilitator
,ISNULL((SELECT COUNT(C.AppID) WHERE C.Status = 'Funded'), 0) AS [Funded Apps]
,ISNULL((SELECT COUNT(C.AppID) WHERE C.Status = 'Approved'), 0) AS [Approved Apps]
,ISNULL((SELECT COUNT(C.AppID) WHERE C.Status = 'Declined'), 0) AS [Declined Apps]
,ISNULL((SELECT COUNT(C.AppID) WHERE C.Status = 'Counter'), 0) AS [Counter Apps]
FROM
(
SELECT
B.AppID
,B.Status
, CASE WHEN B.Facilitator LIKE 'First%App%' THEN 'First Approve'
WHEN B.Facilitator LIKE '%May%' OR B.Facilitator LIKE '' OR B.Facilitator LIKE '%Bil%'
OR B.Facilitator LIKE '%Laf%' OR B.Facilitator LIKE '%Hoa%'
OR B.Facilitator LIKE 'Ke%' OR B.Facilitator LIKE 'Tom%'
THEN 'No Fac.'
ELSE B.Facilitator END AS [Facilitator]
FROM
(
SELECT
A.Facilitator
,A.AppID
,A.Status
FROM
(
SELECT DISTINCT
CA.application_id AS [AppID]
,CASE WHEN LEFT(SO.name, 1) <> LEFT(SO.user_data5, 1) THEN SO.user_data5 ELSE '' END [Facilitator]
,CASE WHEN CA.dec_status = 'A' AND CA.when_funded IS NULL THEN 'Approved'
WHEN CA.dec_status = 'A' AND CA.when_funded IS NOT NULL THEN 'Funded'
WHEN CA.dec_status = 'D' THEN 'Declined'
WHEN CA.dec_status = 'C' THEN 'Counter'
ELSE 'N/A' END AS [Status]
FROM credit AS CA
LEFT JOIN sooffice AS SO ON CA.src_office_id = SO.src_office_id
WHERE SO.is_disabled = 0
AND (CA.dec_status = 'A' OR CA.dec_status = 'D' OR CA.dec_status = 'C')
) AS A
LEFT JOIN credit AS CA ON CA.application_id = A.[AppID]
Where (A.Status = 'Funded' OR A.Status = 'Approved' OR A.Status = 'Declined' OR A.Status = 'Counter')
) AS B
LEFT JOIN credit AS CA ON CA.application_id = B.AppID
) AS C
LEFT JOIN credit AS CA ON CA.application_id = C.AppID
GROUP BY C.Facilitator, C.Status
The result set I am getting is:
Facilitator.........Funded Apps......Approved Apps......Declined Apps..........Counter Apps
Blue ...............0.......................0.................................0................1
Blue................0..............................0.....................................27..................0
Blue................0..............................11.......................................0............0
Blue..............1..............................0....................................0..............0
What I really need is :
Facilitator.........Funded Apps......Approved Apps......Declined Apps..........Counter Apps
Blue ...............1.......................11.................................27................1
Can somebody suggest what is wrong with it.

Wow pretty vague here. I am guessing that maybe you want to use MAX around those columns.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

SQLPain Hall of Fame Points: 3522 More actions · Answer 2

Facilitator is text field, that's why subquery not a JOIN

Sean Lange SSC Guru Points: 286590 More actions · Answer 3

SQLPain (7/12/2016)
Facilitator is text field, that's why subquery not a JOIN

Clearly is isn't a text field or your query wouldn't work. It must be a varchar.

Regardless I don't see what that has to do with the problem at hand. Have you tried MAX? Without any real details that is the best guess I can come up with.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

SQLPain Hall of Fame Points: 3522 More actions · Answer 4

Thanks Sean, what would be the correct statement, Im using this.

(SELECT MAX(COUNT(C.AppID) WHERE C.Status = 'Funded')

Can we do MAX on aggregates?

Sean Lange SSC Guru Points: 286590 More actions · Answer 5

SQLPain (7/12/2016)
Thanks Sean, what would be the correct statement, Im using this.
(SELECT MAX(COUNT(C.AppID) WHERE C.Status = 'Funded')
Can we do MAX on aggregates?

No you can't nest aggregates. There seems to be some other challenges with your query though. I notice you have both distinct and group by. That is redundant.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

SQLPain Hall of Fame Points: 3522 More actions · Answer 6

I took out the distinct.

If somebody else could also look into it that would be great

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 7

SQLPain (7/12/2016)
I took out the distinct.
If somebody else could also look into it that would be great

Sure. There's a ton of redundant joins and stuff in it but something like this should get you part way. Why did you have all those left joins to the credit table?

SELECT

C.Facilitator,

[Funded Apps] = ISNULL((SELECT COUNT(C.AppID) WHERE C.Status = 'Funded'), 0),

[Approved Apps] = ISNULL((SELECT COUNT(C.AppID) WHERE C.Status = 'Approved'), 0),

[Declined Apps] = ISNULL((SELECT COUNT(C.AppID) WHERE C.Status = 'Declined'), 0),

[Counter Apps] = ISNULL((SELECT COUNT(C.AppID) WHERE C.Status = 'Counter'), 0)

FROM ( -- c

-- SELECT

--B.AppID,

-- B.Status,

-- [Facilitator]

--FROM ( -- b

-- SELECT

--A.Facilitator,

--A.AppID,

--A.Status

--FROM ( -- a

SELECT --DISTINCT

[AppID] = CA.application_id,

[Facilitator] = CASE

WHEN B.Facilitator LIKE 'First%App%'

THEN 'First Approve'

WHEN B.Facilitator LIKE '%May%' OR B.Facilitator LIKE '' OR B.Facilitator LIKE '%Bil%' OR B.Facilitator LIKE '%Laf%'

OR B.Facilitator LIKE '%Hoa%' OR B.Facilitator LIKE 'Ke%' OR B.Facilitator LIKE 'Tom%'

THEN 'No Fac.'

ELSE B.Facilitator END,

y.[Status]

FROM credit AS CA

INNER JOIN sooffice AS SO

ON CA.src_office_id = SO.src_office_id

CROSS APPLY (

SELECT [Facilitator] = CASE

WHEN LEFT(SO.name, 1) <> LEFT(SO.user_data5, 1) THEN SO.user_data5

ELSE '' END

) b

CROSS APPLY (

SELECT [Status] = CASE

WHEN CA.dec_status = 'A' AND CA.when_funded IS NULL THEN 'Approved'

WHEN CA.dec_status = 'A' AND CA.when_funded IS NOT NULL THEN 'Funded'

WHEN CA.dec_status = 'D' THEN 'Declined'

WHEN CA.dec_status = 'C' THEN 'Counter'

ELSE 'N/A' END

) y

WHERE SO.is_disabled = 0 -- this filter changes LEFT JOIN sooffice to INNER JOIN sooffice

AND CA.dec_status IN ('A','D','C')

AND y.[Status] IN ('Funded','Approved','Declined','Counter')

--) a

--LEFT JOIN credit AS CA redundant

--ON CA.application_id = A.[AppID]

--WHERE A.[Status] IN ('Funded','Approved','Declined','Counter')

--) b

--WHERE

--LEFT JOIN credit AS CA

--ON CA.application_id = B.AppID

) c

--LEFT JOIN credit AS CA

--ON CA.application_id = C.AppID

GROUP BY C.Facilitator, C.Status

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 8

SQLPain (7/12/2016)
I took out the distinct.
If somebody else could also look into it that would be great

This should be close:

SELECT --DISTINCT

x.[Facilitator],

[Funded Apps] = SUM(CASE WHEN y.[Status] = 'Funded' THEN 1 ELSE 0 END),

[Approved Apps] = SUM(CASE WHEN y.[Status] = 'Approved' THEN 1 ELSE 0 END),

[Declined Apps] = SUM(CASE WHEN y.[Status] = 'Declined' THEN 1 ELSE 0 END),

[Counter Apps] = SUM(CASE WHEN y.[Status] = 'Counter' THEN 1 ELSE 0 END)

FROM credit ca

INNER JOIN sooffice so

ON CA.src_office_id = SO.src_office_id

CROSS APPLY (

SELECT [Facilitator] = CASE

WHEN LEFT(SO.name, 1) = LEFT(SO.user_data5, 1) THEN '' --SO.user_data5

WHEN SO.user_data5 LIKE 'First%App%'

THEN 'First Approve'

WHEN SO.user_data5 LIKE '%May%' OR SO.user_data5 LIKE '' OR SO.user_data5 LIKE '%Bil%' OR SO.user_data5 LIKE '%Laf%'

OR SO.user_data5 LIKE '%Hoa%' OR SO.user_data5 LIKE 'Ke%' OR SO.user_data5 LIKE 'Tom%'

THEN 'No Fac.'

ELSE SO.user_data5 END

) x

CROSS APPLY (

SELECT [Status] = CASE

WHEN CA.dec_status = 'A' AND CA.when_funded IS NULL THEN 'Approved'

WHEN CA.dec_status = 'A' AND CA.when_funded IS NOT NULL THEN 'Funded'

WHEN CA.dec_status = 'D' THEN 'Declined'

WHEN CA.dec_status = 'C' THEN 'Counter'

ELSE 'N/A' END

) y

WHERE SO.is_disabled = 0

AND CA.dec_status IN ('A','D','C')

AND y.[Status] IN ('Funded','Approved','Declined','Counter')

GROUP BY x.[Facilitator]

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

SQLPain Hall of Fame Points: 3522 More actions · Answer 9

Thanks Guyz

I connected it one more time with itself and used the MAX function, which kind of solved my problem.

I know this is not an ideal thing to do

SELECT

Main.Facilitator

,MAX(main.[Funded Apps]) As [Funded]

,MAX(main.[Approved Apps]) AS [Approved]

,MAX(main.[Declined Apps]) As [Declined]

,MAX(main.[Counter Apps]) AS [Counter]

FROM

(

SELECT

C.Facilitator

,ISNULL((SELECT COUNT(C.AppID) WHERE C.Status = 'Funded' ), 0) AS [Funded Apps]

---------xxxxxxxxx

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 10

SQLPain (7/13/2016)
Thanks Guyz
I connected it one more time with itself and used the MAX function, which kind of solved my problem.
I know this is not an ideal thing to do
SELECT
Main.Facilitator
,MAX(main.[Funded Apps]) As [Funded]
,MAX(main.[Approved Apps]) AS [Approved]
,MAX(main.[Declined Apps]) As [Declined]
,MAX(main.[Counter Apps]) AS [Counter]
FROM
(
SELECT
C.Facilitator
,ISNULL((SELECT COUNT(C.AppID) WHERE C.Status = 'Funded' ), 0) AS [Funded Apps]
---------xxxxxxxxx

Then why do it? You've just doubled the size of a pile of junk. Where's your professionalism?

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]