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

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

0
lrbrister
Asked:
lrbrister
  • 7
  • 4
  • 2
1 Solution
 
hyphenpipeCommented:
Is the afsAmount in your sample table supposed to read 1000 as opposed to 10000?

0
 
lrbristerAuthor Commented:
hyphenpipe:
Yeah...my bad.
The first two are 1000 and the last one 10000
0
 
lrbristerAuthor Commented:
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

0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
hyphenpipeCommented:
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
0
 
lrbristerAuthor Commented:
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
0
 
lrbristerAuthor Commented:
Sorry..typo again
1 payment of 1000 on 20110101; 1 payment of 10000 on 20110301; 1 payment of 1000 on 20110501
0
 
ralmadaCommented:
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

0
 
lrbristerAuthor Commented:
ralmada,
I took your select and modified it (attached)

The output looks like this...
 screenprint
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

0
 
ralmadaCommented:
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?
0
 
lrbristerAuthor Commented:
ralmada:
In the straight select...same results
Am I supposed to insert this into my fetch above?
0
 
ralmadaCommented:
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

0
 
ralmadaCommented:
ok, the below will give you a resultset with the periods and the summary you want, from there you need to concatenate it into the string you need
declare @table1 table (
afsSource varchar(20),
afsAmount      int,
afsDate          datetime,
afsTransaction int
)

declare @table2 table (
afsSource varchar(20),
afsAmount      int,
afsDate          datetime,
afsTransaction int,
rn int,
id_num 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, @x int

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

open cfCursor

set @i = 0
set @x = 1

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

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

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

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


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

end

close cfCursor
deallocate cfCursor

select afsSource, afsTransaction, id_num, afsAmount, min(afsDate), max(afsDate), max(rn) from @table2
group by afsSource, afsTransaction, id_num, afsAmount

Open in new window

0
 
lrbristerAuthor Commented:
Perfect....thanks
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

  • 7
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now