SQL Select Statement

My attached select statement will produce (example) the following output
You'll notice that there can be multiple compounding methods for a deal.
The loan date will always be the same for a dealID

What I need is ONE record per deal.  

This is my current output
DealID     cp           ld
8001       Annual   01/01/2011
8002       Annual   01/01/2012
8002       Monthly  01/01/2012
8003       Monthly  01/01/2013

This is what I need.
Notice...that anything with more than one compounding method outputs "Split"

DealID     cp           ld
8001       Annual   01/01/2011
8002       Split        01/01/2012
8003       Monthly  01/01/2013
Select		DealID, compoundingMethod cp, loanDate  ld
	from	P_PaymentStreams
Group by	DealID, compoundingMethod, loanDate

Open in new window

Larry Bristersr. DeveloperAsked:
Who is Participating?
 
Erick37Connect With a Mentor Commented:
Try this:
Select		
	DealID
	,CASE WHEN COUNT(compoundingMethod) > 1 THEN 'Split' ELSE MIN(compoundingMethod) END cp
	,MAX(loanDate)  ld
from	P_PaymentStreams
Group by	
DealID

Open in new window

0
 
luceysupportCommented:
start by creating a table variable to hold your data, add an extra column for count

first insert into this table variable the dealid and id and the count, grouping by both.

Then update the cp column to be split where count > 1

Update the cp column to be the actual value of cp where count =1
0
 
Larry Bristersr. DeveloperAuthor Commented:
Perfect...thanks.
0
 
deightonprogCommented:
Erick37 is on to someting, but you might want to do

Select            
      DealID
      ,CASE WHEN COUNT(distinct compoundingMethod) > 1 THEN 'Split' ELSE MIN(compoundingMethod) END cp
      ,MAX(loanDate)  ld
from      P_PaymentStreams
Group by      
DealID




so that it does not split if they are all the same
0
 
Larry Bristersr. DeveloperAuthor Commented:
deighton:
You're absolutely right. Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.