asked on

Nested fetch statements?

I have data that has many "payment streams" in it.

The dates of the "stream" may or may not be "sequential.

I need to build a single text output that explains the stream

My data MAY be something like this

afsSource afsAmount afsDate afsTransaction
SS-104 10000 20110101 9025
SS-104 10000 20110201 9025
---lots of other "non 9025/SS-104 data rows-----------
SS-104 2000 20110301 9025
SS-104 10000 20130101 9025
---Other data----

My "fetch" output would be

2 payments of $1000 beginning on 20110101 and ending on 20110201; 1 payment of $2000 on 2011301; 1 payment of $10000 on 20130101

Select	afsAmount, afsDate
FROM	proc_cfa.dbo.P_AvailableForSale
		where afsTransaction = 9025
		and left(@afsSource,8) = 'SS-104'
		order by afsDate

Open in new window

hyphenpipe

Is the afsAmount in your sample table supposed to read 1000 as opposed to 10000?

Larry Brister

ASKER

hyphenpipe:
Yeah...my bad.
The first two are 1000 and the last one 10000

Larry Brister

ASKER

hyphenpipe
The attached code is messed up on the @numpayments part

ALTER FUNCTION [dbo].[uf_ucc_CFC_FilingLB] (@afsTransaction int, @afsSource nvarchar(8)) 

RETURNS VARCHAR(8000)
AS
BEGIN

	DECLARE @Output VARCHAR(8000)
	Set @Output = ''
	DECLARE @line VARCHAR(8000)
	Declare @numpmts int
	Set @numpmts = (Select count(*) from proc_cfa.dbo.P_AvailableForSale
			where afsTransaction = @afsTransaction
			and
			Substring(afsSource,0,8) = @afsSource
			)

	--declaring my looper
	--purchase deal stream 
		BEGIN
			DECLARE cfCursor CURSOR SCROLL FOR
			--sql
				
					
			SELECT case when @numpmts =1 then cast(@numpmts as varchar) + ' payment in the amount of $' + convert(varchar,afsAmount,1) + ' on or about ' + convert(varchar(25),afsDate,107) +' from and and Including ' + convert(varchar(25),afsDate,107) +'; '
						when @numpmts >1 then cast(@numpmts as varchar) + ' payments in the gross amount of $' + convert(varchar,afsAmount,1) + ' on or about ' + convert(varchar(25),afsDate,107) +' from and Including ' + convert(varchar(25),afsDate,107)+'; '					
				   end as line
			FROM proc_cfa.dbo.P_AvailableForSale
			where afsTransaction = @afsTransaction
			and left(@afsSource,8) = @afsSource
			order by afsDate
		END

	OPEN cfCursor
	FETCH NEXT FROM cfCursor
	Into @line
	
	WHILE @@FETCH_STATUS = 0
	BEGIN
		SET @Output = @Output + @line
		--fill cursor with next set of values
		FETCH NEXT FROM cfCursor
		INTO @line
	END
	CLOSE cfCursor
	DEALLOCATE cfCursor
	RETURN 'All of the Debtor''s right, title and interest in and to that certain account consisting of ' + @Output + ' payable by _varFunder of _varFunderState and any cash and noncash proceeds of the foregoing'
END

Open in new window

hyphenpipe

What would be the desired output if two 1000 payments occurred between a payment of 10000?

SS-104 1000 20110101 9025
SS-104 10000 20110301 9025
SS-104 1000 20110501 9025

Larry Brister

ASKER

hyphenpipe:
Desired result would be 3 distinct statements in succession.

1 payment of 1000 on 20110101; 1 payment of 10000 on 20110301; 1 payment of 10000 on 20110501

Larry Brister

ASKER

Sorry..typo again
1 payment of 1000 on 20110101; 1 payment of 10000 on 20110301; 1 payment of 1000 on 20110501

ralmada

Not sure what is your desired output. Just one string containing a legend like "1 payment of 1000 on ..."

I would suggest you use this query as a base instead of the cursor.

;with CTE as (
select
	afsSource,
	afsAmount,
	afsDate,
	afsTransaction,
	row_number() over (partition by afsSource, afsDate, afsAmount order by afsSource, afsDate) rn
from yourtable 
)
select afsSource, afsAmount, afsDate, afsTransaction, max(rn) cnt
from CTE
group by afsSource, afsAmount, afsDate, afsTransaction

Open in new window

Larry Brister

ASKER

ralmada,
I took your select and modified it (attached)

The output looks like this...

I STILL need a single text output that would read...
3 payments of 2295.66 Beginning 20300618 and ending 20300818; 2 payments of 2170.02 beginning 20300918 and ending 20301018; 7 payments of 2295.66 Beginning 20301118 and ending 20310518;
ans so on.

This text is inserted into a legal docuyment and MUST be in this format.

Declare @afsTransaction int
Declare @afsSource nvarchar(8)
Set @afsTransaction = 9025
Set @afsSource = 'SS-1790'
;with CTE as (  
select  
        afsSource,  
        afsAmount,  
        afsDate,  
        afsTransaction,  
        row_number() over (partition by afsSource, afsDate, afsAmount order by afsSource, afsDate) rn  
from proc_cfa.dbo.P_AvailableForSale   
)  
select afsAmount, afsDate, max(rn) cnt  
from CTE  
where SubString(afsSource,0,8) = @afsSource
and afsTransAction = @afsTransAction
group by afsSource, afsAmount, afsDate, afsTransaction 
order by afsDate

Open in new window

ralmada

don't have a test environment here, but can you try this

....
row_number() over (partition by afsSource, afsAmount order by afsSource, afsDate) rn
...

instead?

Larry Brister

ASKER

ralmada:
In the straight select...same results
Am I supposed to insert this into my fetch above?

ralmada

sorry,

so far this is what I have come up with. It's not the best but it will add a column which will start over when the amount changes. Still need further analysis to get you the summary though...

declare @table1 table (
afsSource varchar(20),
afsAmount      int,
afsDate          datetime,
afsTransaction int
)

declare @table2 table (
id_num int identity,
afsSource varchar(20),
afsAmount      int,
afsDate          datetime,
afsTransaction int,
rn int
)


insert @table1 values('SS-104',              10000,              '20110101',         9025)
insert @table1 values('SS-104',               10000,              '20110201',        9025)
insert @table1 values('SS-104',               10000,              '20110301',        9025)
insert @table1 values('SS-104',              2000,                '20110301',        9025)
insert @table1 values('SS-104',              10000,               '20130101',        9025)
insert @table1 values('SS-104',               10000,              '20130201',        9025)


declare @afsSource varchar(20)
declare @afsAmount      int
declare @afsDate          datetime
declare @afsTransaction int
declare @afsSource1 varchar(20)
declare @afsAmount1      int
declare @afsDate1          datetime
declare @afsTransaction1 int


declare @output varchar(max)
declare @i int

declare cfCursor cursor for
select afsSource, afsTransaction, afsDate, afsAmount from @table1

open cfCursor

set @i = 0
fetch next from cfCursor
into @afsSource, @afsTransaction, @afsDate, @afsAmount

--select @afsSource1 = @AfsSource, @afsTransaction1 = @afsTransaction, @afsDate1 = @afsDate, @afsAmount1 = @afsAmount

while @@FETCH_STATUS = 0
begin
	
 if isnull(@afsSource1,0) = @afsSource and isnull(@afsTransaction1, '') = @afsTransaction and isnull(@afsAmount1, 0) <> @afsAmount
		set @i = 1
	else 
		set @i = @i + 1

	select @afsSource1 = @AfsSource, @afsTransaction1 = @afsTransaction, @afsDate1 = @afsDate, @afsAmount1 = @afsAmount		

	insert into @table2
	select @afsSource, @afsAmount, @afsDate, @afsTransaction, @i


	fetch next from cfCursor
	into @afsSource, @afsTransaction, @afsDate, @afsAmount

end

close cfCursor
deallocate cfCursor

select * from @table2

Open in new window

ASKER CERTIFIED SOLUTION

ralmada

membership

This solution is only available to members.

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

Start Free Trial

Larry Brister

ASKER

Perfect....thanks