• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 333
  • Last Modified:

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

0
lrbrister
Asked:
lrbrister
1 Solution
 
Erick37Commented:
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
 
lrbristerAuthor Commented:
Perfect...thanks.
0
 
deightonCommented:
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
 
lrbristerAuthor Commented:
deighton:
You're absolutely right. Thanks
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now