asked on

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

ASKER CERTIFIED SOLUTION

Erick37

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

luceysupport

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

Larry Brister

ASKER

Perfect...thanks.

deighton

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

Larry Brister

ASKER

deighton:
You're absolutely right. Thanks