Link to home
Create AccountLog in
Avatar of Larry Brister
Larry BristerFlag for United States of America

asked on

Choose Next Date

I have a output select with two date columns
The startDate  column will always be in date ASC order
The feedMonth column can be ANY year.
What I NEED it to show is the very next time the feedMonth column  MONTH occurs AFTER startDate

So...in the examples below the first TWO feedMonth dates would be
2013-03-01 00:00:00

The THIRD feedMonth Date would be
2014-03-01 00:00:00

startDate                                feedMonth
2012-11-01 00:00:00             2014-03-01 00:00:00
2012-12-01 00:00:00             2011-03-01 00:00:00
2013-03-01 00:00:00             2019-03-01 00:00:00



Avatar of simonpaul64
simonpaul64

Sorry - I can't work out what you want from the example data...

Can you clarify?
ASKER CERTIFIED SOLUTION
Avatar of arilani
arilani

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
can you post the sql that was used to provide the original output?
Avatar of Larry Brister

ASKER

ok...here you go...
Stored procedure and output attached as text file

the feedMonth is in the final column in the output
ALTER PROCEDURE [dbo].[sp_cfa_CollateralStreamActualGet](@afsTransaction int, @dealID int)

AS
BEGIN
Select	@afsTransaction as ordTranID,  
		'Payment' as EventType,  
		ROW_NUMBER()   
		OVER (Order by afsDate, afsSourceDetail) AS StreamSeqNumber,              
		Null as LoanDate,  
		afsDate as StartDate,  
		afsAmount as PmtAmount,  
		1 as NumPmts,  
		'Annual' as PmtFrequency,  
		Null as EndDate,  
		0.000 as PercentIncrease,  
		0.00 as FixedIncrease,  
		0 as NumberPmtsBeforeChange,  
		Null as AnnualRate,  
		'Annual' as Compounding,  
		Cast(SubString(afsSource,4,4) as Int) as DealID,  
		IsNull(lt.fedW2G,0) + IsNull(lt.stateW2G,0) + IsNull(lt.cityW2G,0) as taxes,  
		l.FiscalMonth,  
		dbo.getLIFileDate(afsDate, l.FiscalMonth) as feedMonth  
Into #tempPaymentStream 
from proc_cfa.dbo.p_AvailableForSale a  
        Left Join proc_lt.dbo.P_Deals lt ON SubString(a.afsSource,4,4) = lt.DealID  
        Left join proc_lt.dbo.P_LegalEntityUsed l on lt.LegalEntityUsedID = l.LEID  
where a.afsTransaction = @afsTransaction and SubString(a.afsSource,4,4) = IsNull(@dealID,SubString(a.afsSource,4,4))  
Order by DealID,StreamSeqNumber



Select	DealID,  
        ordTranID,  
        EventType,  
        StreamSeqNumber,  
        LoanDate,  
        StartDate OriginalStartDate,  
        FiscalMonth,  
		--feedMonth startDate,
		dateadd(m, FiscalMonth+4, dateadd(d,  -datepart(d, StartDate)+1, StartDate)) StartDate, 
        Amounts,  
        NumPmts,  
        PmtFrequency,  
        EndDate,  
        PercentIncrease,  
        FixedIncrease,  
        NumberPmtsBeforeChange,  
        AnnualRate,  
        Compounding,  
        Type as PaymentType ,
		feedMonth 
Into #tempPaymentStreamSplit
FROM   
(  
	Select  DealID,  
			ordTranID,  
			EventType,  
			StreamSeqNumber,  
			LoanDate,  
			StartDate,  
			PmtAmount= Round(Cast(fundedamount - (fundedamount * (pd.taxes*.01)) as money),2,0),  
			TaxAmount= Round(Cast(((fundedamount * (pd.taxes*.01))) as money),2,0),  
			NumPmts,  
			PmtFrequency,  
			EndDate,  
			PercentIncrease,  
			FixedIncrease,  
			NumberPmtsBeforeChange,  
			AnnualRate,  
			Compounding,  
			FiscalMonth,  
			feedMonth  

	 From   
		(  
			Select	DealID,  
					ordTranID,  
					EventType,  
					StreamSeqNumber,  
					LoanDate,  
					StartDate,  
					PmtAmount fundedAmount,  
					taxes,  
					NumPmts,  
					PmtFrequency,  
					EndDate,  
					PercentIncrease,  
					FixedIncrease,  
					NumberPmtsBeforeChange,  
					AnnualRate,  
					Compounding,  
					FiscalMonth,  
					feedMonth  
			From    #tempPaymentStream
			Where   DealID = @DealID  
		)	pd  
	)p  
UNPIVOT  
   (Amounts FOR Type IN (PmtAmount, TaxAmount)  
)AS unpvt;


Select	DealID,
		ordTranID,
		EventType,
		LoanDate,
		Case when PaymentType = 'PmtAmount' then OriginalStartDate else StartDate end as StartDate,
		Amounts as PmtAmount,
		NumPmts,
		PmtFrequency,
		EndDate,
		PercentIncrease,
		FixedIncrease,
		NumberPmtsBeforeChange,
		Annualrate,
		Compounding,
		feedMonth
	Into #tempPaymentsCased
 From #tempPaymentStreamSplit

Select  DealID,
		ordTranID,
		EventType,
		ROW_NUMBER()   
		OVER (Order by StartDate) AS StreamSeqNumber,
		LoanDate,
		StartDate,		
		Sum(PmtAmount) PmtAmount,
		NumPmts,
		PmtFrequency,
		EndDate,
		PercentIncrease,
		FixedIncrease,
		NumberPmtsBeforeChange,
		Annualrate,
		Compounding,
		feedMonth
From	#tempPaymentsCased
Group by
		DealID,
		ordTranID,
		EventType,
		LoanDate,
		StartDate,		
		NumPmts,
		PmtFrequency,
		EndDate,
		PercentIncrease,
		FixedIncrease,
		NumberPmtsBeforeChange,
		Annualrate,
		Compounding,
		feedMonth
END

Open in new window

testData.txt
Did you tryed my solution?
Taking a look now
Your select fit what I asked...not what I needed.

I am submitting another question