Add pagenumber at the 47th rows in text file using SQL Function.

How to add Pagenumber in 47 rows of the .txt file?

I don't know how to declare @Rows where to assign in 47 rows.

This code was originally written by Mark Wills:
BEGIN
INSERT @textfile SELECT @rundate,@Rows,'------------------------ Page '+CONVERT(VARCHAR(3),@pagecounter)+' ------------------------' 
End

Open in new window


I tried it but I cannot get it to work.

Please see example attached.

Please help. I really appreciate any help.

Thank you.
AddPageNumberIn47thRows.txt
Queennie LAsked:
Who is Participating?
 
Mark WillsConnect With a Mentor Topic AdvisorCommented:
Well, finally...

Had to do a lot of guessing about data (mainly data types) the spreadsheet did not import all that well. So, did a bit of manipulation.

You do have raw duplicates in there for   NAME: BOBROW, B  and they are duplicates - and that is where the control break works - because there is nothing to differentiate one line from another, they dont cause a control break. The downside is the aggregations are quite wrong. I realise this is test data so put it down to test faux-pas.

Removed the initial group by - this type of format (output) you are looking for a row translating to at least one printline (normally multiple print lines in this type of format).

There are a few control breaks - the header only occurs once per report - like a control page
then the Employer information - again likely to be per report (if I am reading the data correctly)

but we borrow from the employer information for page breaks. Might need to do some more work there - for example - carry over column headings.

Now a couple of 'tricks' - using order by and row_numbering to get the sequencing of data correct. Unfortunately, even though the rows have all the data, we need to present the data at the right time. Specifically, the ControlNo is really a footer at the end of each ControlNo group (well, that is my interpretation). And the "totals" happen once - to that extent, I combined rows to form one total line. Then the ForwardedTo at the end. That is controlled by those row-number() elements.

Another 'trick' is with alignment. For numerics you want them right aligned  so use the right() function and pad first. Also, for numbers, check isnull() first, then any aggregation, then cast as varchar. All data elements need a specific length with a fixed format layout. The only exception is the last 'field' on a line if that field is character based. Any literals have implicit length (and dont need to check literals for null).

I removed "Other Claim" and "Another Claim" because they didnt reference any data elements.

Wasnt quite sure about [AllowedAmount] - it seemed to be non-aligned with anything, so assumed it might have been a bit of a diagnostic. If needed, we would follow the same approach as @lastpay, and have the declaratives there.

Any way, I hope you are happy with it - I poured over it and pretty happy with the results. Even though it is quite different from where it started, I think you will find it easier to tweak and tune.

ALTER FUNCTION [dbo].[udf_export_EmployeeCheck](@Check VARCHAR(50))
RETURNS @textfile TABLE 
   (
   linectr INT IDENTITY, 
   rundate DATETIME, 
   [Filename] VARCHAR(50),
   exportrecord VARCHAR(500)
   )
AS
BEGIN
   
   DECLARE @rundate DATETIME = GETDATE()
   DECLARE @pagecounter INT = 1
   DECLARE @linecounter INT = 1
   DECLARE @Header int = 0
   DECLARE @constant5010 INT = 5010
   DECLARE @UniqueID VARCHAR(150)
   DECLARE @Employee_Filename VARCHAR(150)
   DECLARE @Employee_Check VARCHAR(150)
   DECLARE @RPIDNumber VARCHAR(50)
   DECLARE @ControlNo VARCHAR(50)
   DECLARE @Employee VARCHAR(50)
   DECLARE @ICNUmber VARCHAR(50)
   DECLARE @Reason1 varchar(50)
   DECLARE @Reason2 varchar(50)
   DECLARE @Reason3 varchar(50)
   DECLARE @Reason4 varchar(50)
   DECLARE @PayAmount money
   DECLARE @ContractAmount money
   DECLARE @AllowedPayment money
   DECLARE @ForwardedTo varchar(50)
   DECLARE @Bank VARCHAR(50)
   DECLARE @Employer varchar(50) = ''   
   DECLARE @ServiceUnits VARCHAR(50)
   DECLARE @LastRow int = 0
   DECLARE @lastCheck varchar(50) = ''
   DECLARE @lastbank  varchar(50) = ''
   DECLARE @lastEmployer varchar(50) = ''   
   DECLARE @LastEmployee VARCHAR(50) = ''
   DECLARE @LastICNUmber VARCHAR(50) = ''
   DECLARE @LastRPID VARCHAR(50) = ''
   DECLARE @LastControlNo VARCHAR(50) = ''
   DECLARE @LastR1 varchar(50) = ''
   DECLARE @LastR2 varchar(50) = ''
   DECLARE @LastR3 varchar(50) = ''
   DECLARE @LastR4 varchar(50) = ''
   DECLARE @LastForward varchar(50) = ''
   DECLARE @LastPay money = 999999
   DECLARE @LastAllowed money = 999999
   
   
   DECLARE filename_cursor CURSOR FOR SELECT UniqueID,[CheckNumber],[ICNumber],[EmployeeName],[bankname],[employername],ISNULL([RPIDNumber], '')+' '+ISNULL([POSCode],'')+' '+REPLACE(ISNULL([ServiceUnits],''), ' ', '') as [RPIDNumber],[ControlNo],ReasonDescA1,ReasonDescA2,ReasonDescA3,ReasonDescA4,PayAmount,ContractAmount,AllowedAmount,ForwardedTo 
                                            ,row_number() over (partition by [CheckNumber],[bankname],[employername],[EmployeeName],[ICNumber] order by [ControlNo] desc,[RPIDNumber] desc,[POSCode] desc,[ServiceUnits] desc, [uniqueid] desc) as [LastRow]
                                            ,row_number() over (partition by [CheckNumber],[bankname],[employername],[EmployeeName],[ICNumber],[ControlNo] order by [RPIDNumber] desc,[POSCode] desc,[ServiceUnits] desc,[uniqueid] desc) as [LastCont]
                                      FROM tblEmployeeCheck WHERE [CheckNumber] = @Check 
                                      order by [checknumber],[bankname],[employername],[employeename],[icnumber],[ControlNo],[RPIDNumber],[POSCode],[ServiceUnits],[uniqueid]

   OPEN filename_cursor
   FETCH NEXT FROM filename_cursor INTO @UniqueID,@Employee_Check,@ICNUmber,@Employee,@bank,@employer,@RPIDNumber,@ControlNo, @reason1,@reason2,@reason3,@reason4,@PayAmount,@ContractAmount,@AllowedPayment,@ForwardedTo,@LastRow,@LastControlNo
   WHILE @@FETCH_STATUS = 0
   BEGIN
       

Print_Header:

      SET @linecounter = @linecounter + 1

      IF @linecounter > 46
      BEGIN
          INSERT @textfile SELECT @rundate,@Check,'------------------------ Page '+CONVERT(VARCHAR(3),@pagecounter)+' ------------------------' 
          SET @pagecounter = @pagecounter + 1
          INSERT @textfile SELECT @rundate,@Check,'_________________________________________________________________________________________________________________________________________'
          INSERT @textfile SELECT @rundate,@Check,'NPI #:        ' + left(ISNULL([EmployerID], '')+space(50),50) + LEFT(ISNULL([EmployerName], '')+space(60),60) FROM tblEmployeeCheck WHERE UniqueID = @UniqueID AND [CheckNumber] = @Employee_Check
          INSERT @textfile SELECT @rundate,@Check,'Check/EFT #:  ' + left(ISNULL([CheckNumber], '')+space(50),50)+ left('DATE:  ' + ISNULL([BusiImportDate], '') + space(60),60)+'PAGE #: ' + right('   '+CONVERT(VARCHAR(3),@pagecounter),3) FROM tblEmployeeCheck WHERE UniqueID = @UniqueID AND [CheckNumber] = @Employee_Check  
          INSERT @textfile SELECT @rundate,@Check,'_________________________________________________________________________________________________________________________________________'
          set @linecounter = 4
      END   

      if @Check <> @lastcheck
      begin
       /* - - - - - HEADER PAGE INFORMATION - - - - - */
         INSERT @textfile SELECT @rundate,@Check,'  Check/EFT #:     ' + ISNULL([CheckNumber], '') FROM tblEmployeeCheck WHERE UniqueID = @UniqueID AND [CheckNumber] = @Employee_Check
         INSERT @textfile SELECT @rundate,@Check,'  Batch Number:    ' + ISNULL([BatchNumber], '') FROM tblEmployeeCheck WHERE UniqueID = @UniqueID AND [CheckNumber] = @Employee_Check
         INSERT @textfile SELECT @rundate,@Check,'  SetData:         ' + ISNULL([SetData], '')     FROM tblEmployeeCheck WHERE UniqueID = @UniqueID AND [CheckNumber] = @Employee_Check
         INSERT @textfile SELECT @rundate,@Check,'  File:            ' + ISNULL([Filename], '')    FROM tblEmployeeCheck WHERE UniqueID = @UniqueID AND [CheckNumber] = @Employee_Check
         INSERT @textfile SELECT @rundate,@Check,'  Bank Abbr:       ' + ISNULL([BankNameAbbre], '') FROM tblEmployeeCheck WHERE UniqueID = @UniqueID AND [CheckNumber] = @Employee_Check
         INSERT @textfile SELECT @rundate,@Check,'  Pages:           ' + '999999'
         set @header = @@IDENTITY
         INSERT @textfile SELECT @rundate,@Check,'  Check Total:     ' + ISNULL([CheckTotal], '')  FROM tblEmployeeCheck WHERE UniqueID = @UniqueID AND [CheckNumber] = @Employee_Check 
         INSERT @textfile SELECT @rundate,@Check,'  Check Date:      ' + ISNULL([CheckDate], '')   FROM tblEmployeeCheck WHERE UniqueID = @UniqueID AND [CheckNumber] = @Employee_Check 
         INSERT @textfile SELECT @rundate,@Check,'  Ck Type:         ' + ISNULL([PaymentMethodCode], '') FROM tblEmployeeCheck WHERE UniqueID = @UniqueID AND [CheckNumber] = @Employee_Check 
         INSERT @textfile SELECT @rundate,@Check,'                   '         
         INSERT @textfile SELECT @rundate,@Check,'_________________________________________________________________________________________________________________________________________'
         set @lastcheck = @check
         SET @linecounter = @linecounter + 11
      end      

      if @bank <> @lastbank 
      begin      
      /* - - - - - Bank INFORMATION - - - - - */
         INSERT @textfile SELECT @rundate,@Check,ISNULL([BankName], '') FROM tblEmployeeCheck WHERE UniqueID = @UniqueID AND [CheckNumber] = @Employee_Check 
         INSERT @textfile SELECT @rundate,@Check,ISNULL([BankAddress], '') FROM tblEmployeeCheck WHERE UniqueID = @UniqueID AND [CheckNumber] = @Employee_Check 
         INSERT @textfile SELECT @rundate,@Check,ISNULL([BankCityStateZipcode], '') FROM tblEmployeeCheck WHERE UniqueID = @UniqueID AND [CheckNumber] = @Employee_Check 
         INSERT @textfile SELECT @rundate,@Check,'BANK BUSINESS CONTACT INFORMATION:' 
         INSERT @textfile SELECT @rundate,@Check,ISNULL([BankContactName1_NO], '') FROM tblEmployeeCheck WHERE UniqueID = @UniqueID AND [CheckNumber] = @Employee_Check 
         INSERT @textfile SELECT @rundate,@Check,ISNULL([BankContactCommunication1_No], '') FROM tblEmployeeCheck WHERE UniqueID = @UniqueID AND [CheckNumber] = @Employee_Check 
         INSERT @textfile SELECT @rundate,@Check,'                     '
         set @lastbank = @bank
         sET @linecounter = @linecounter + 7
      end      
      
      if @lastemployer <> @employer
      begin                                    
      /* - - - - - Employer INFORMATION - - - - - */                                                                                                    
         INSERT @textfile SELECT @rundate,@Check,LEFT(ISNULL([EmployerName], '')+space(40),40)+space(60)+'NPI #:   ' + ISNULL([EmployerID], '') FROM tblEmployeeCheck WHERE UniqueID = @UniqueID AND [CheckNumber] = @Employee_Check
         INSERT @textfile SELECT @rundate,@Check,LEFT(ISNULL([EmployerAddress], '')+space(40),40)+space(60)+'DATE:    ' + ISNULL([BusiImportDate], '') FROM tblEmployeeCheck WHERE UniqueID = @UniqueID AND [CheckNumber] = @Employee_Check
         INSERT @textfile SELECT @rundate,@Check,LEFT(ISNULL([EmployerCityStateZipcode], '')+space(40),40)+space(60)+'PAGE #:  ' + CONVERT(VARCHAR(3),@pagecounter) FROM tblEmployeeCheck WHERE UniqueID = @UniqueID AND [CheckNumber] = @Employee_Check 
         INSERT @textfile SELECT @rundate,@Check,' '
         INSERT @textfile SELECT @rundate,@Check,'Check/EFT #:  ' + ISNULL([CheckNumber], '') FROM tblEmployeeCheck WHERE UniqueID = @UniqueID AND [CheckNumber] = @Employee_Check
         INSERT @textfile SELECT @rundate,@Check,'REND-PROV  SERV-DATE  POS PD-PROC/MODS        PD-NOS        BILLED      ALLOWED       DEDUCT        COINS      PROV-PD'
         INSERT @textfile SELECT @rundate,@Check,'RARC                                         SUB-NOS      SUB-PROC     GRP/CARC     CARC-AMT      ADJ-QTY'   
         INSERT @textfile SELECT @rundate,@Check,'_________________________________________________________________________________________________________________________________________'
         set @lastEmployer = @employer
         set @LastEmployee = ''
         SET @linecounter = @linecounter + 8
      END
      

     IF @LastEmployee <> @Employee
     OR @LastICNUmber <> @ICNUmber
     begin
      /* - - - - - EMPLOYEE / ICNUMBER INFORMATION - - - - - */
         INSERT @textfile SELECT @rundate,@Check,'NAME: ' +LEFT(ISNULL([EmployeeName], '')+space(25),25)+'CIH:' +LEFT(ISNULL([InsuredID], '')+Space(14),14)+'ACNT:'+LEFT(ISNULL([ClaimInformation], '')+space(17),17)+LEFT(ISNULL([ICNumber], '')+space(35),35)+'ASG:  ' FROM tblEmployeeCheck WHERE UniqueID = @UniqueID AND [CheckNumber] = @Employee_Check and ICNumber = @ICNumber and EmployeeName = @Employee GROUP BY [EmployeeName],[InsuredID],[ClaimInformation],[ICNumber] 
         set @lastemployee = @employee
         set @LastICNUmber = @ICNUmber
         set @LastRPID = ''
         set @LastR1 = ''
         set @LastR2 = ''
         set @LastR3 = ''
         set @LastR4 = ''
         set @LastPay = 999999
         set @LastAllowed = 999999
         set @LastForward = ''
         GOTO Print_Header
     end

      /* - - - - - DETAIL PAGE INFORMATION - - - - - */
      if @LastRPID <> @RPIDNUMBER 
      begin
         INSERT @textfile SELECT @rundate,@Check,LEFT(@RPIDNumber+space(44),44)+right(space(9)+ISNULL([Units], '0'),9)+right(space(13)+ISNULL(CAST([SubmittedAmount] AS VARCHAR(50)), ''),13)+right(space(13)+ISNULL(CAST([AllowedAmount] AS VARCHAR(50)), ''),13)+right(space(13)+ISNULL(CAST([DeductAmount] AS VARCHAR(50)), ''),13)+right(space(13)+ISNULL(CAST([PayAmount] AS VARCHAR(50)), ''),13)+right(space(14)+ISNULL(CAST([PaidAmount] AS VARCHAR(50)), ''),14) FROM tblEmployeeCheck WHERE [uniqueid] = @UniqueID and [CheckNumber] = @Employee_Check and ICNumber = @ICNumber and EmployeeName = @Employee
         set @LastRPID = @RPIDNumber
         GOTO Print_Header
      end

      if @lastR1 <> @Reason1
      begin
         if len(ltrim(rtrim(@reason1))) > 1
         begin
            INSERT @textfile SELECT @rundate,@Check,SPACE(72)+LEFT(ISNULL([ReasonDescA1], '')+space(10),10)+right(space(10)+case when isnumeric([ReasonDescAAmount1]) =1 then cast(cast(ISNULL([ReasonDescAAmount1], '0.00') as money) as varchar(10)) else '0.00' end,10) FROM tblEmployeeCheck WHERE [uniqueid] = @UniqueID and [CheckNumber] = @Employee_Check and ICNumber = @ICNumber and EmployeeName = @Employee 
            set @LastR1 = @Reason1
            GOTO Print_Header
         end
         set @LastR1 = @Reason1
      end

      if @lastR2 <> @Reason2
      begin
         if len(ltrim(rtrim(@reason2))) > 1
         begin
            INSERT @textfile SELECT @rundate,@Check,SPACE(72)+LEFT(ISNULL([ReasonDescA2], '')+space(10),10)+right(space(10)+case when isnumeric([ReasonDescAAmount2]) =1 then cast(cast(ISNULL([ReasonDescAAmount2], '0.00') as money) as varchar(10)) else '0.00' end,10) FROM tblEmployeeCheck WHERE [uniqueid] = @UniqueID and [CheckNumber] = @Employee_Check and ICNumber = @ICNumber and EmployeeName = @Employee 
            set @LastR2 = @Reason2
            GOTO Print_Header
         end
         set @LastR2 = @Reason2
      end

      if @lastR3 <> @Reason3
      begin
         if len(ltrim(rtrim(@reason3))) > 1
         begin
            INSERT @textfile SELECT @rundate,@Check,SPACE(72)+LEFT(ISNULL([ReasonDescA3], '')+space(10),10)+right(space(10)+case when isnumeric([ReasonDescAAmount3]) =1 then cast(cast(ISNULL([ReasonDescAAmount3], '0.00') as money) as varchar(10)) else '0.00' end,10) FROM tblEmployeeCheck WHERE [uniqueid] = @UniqueID and [CheckNumber] = @Employee_Check and ICNumber = @ICNumber and EmployeeName = @Employee 
            set @LastR3 = @Reason3
            GOTO Print_Header
         end
         set @LastR3 = @Reason3
      end

      if @lastR4 <> @Reason4
      begin
         if len(ltrim(rtrim(@reason4))) > 1
         begin
            INSERT @textfile SELECT @rundate,@Check,SPACE(72)+LEFT(ISNULL([ReasonDescA4], '')+space(10),10)+right(space(10)+case when isnumeric([ReasonDescAAmount4]) =1 then cast(cast(ISNULL([ReasonDescAAmount4], '0.00') as money) as varchar(10)) else '0.00' end,10) FROM tblEmployeeCheck WHERE [uniqueid] = @UniqueID and [CheckNumber] = @Employee_Check and ICNumber = @ICNumber and EmployeeName = @Employee 
            set @LastR4 = @Reason4
            GOTO Print_Header
         end
         set @LastR4 = @Reason4
      end

      if @LastControlNo = 1
      and len(@controlno) > 0 
      begin
         INSERT @textfile SELECT @rundate,@Check,'CNTL #: '+ISNULL([ControlNo], '')   FROM tblEmployeeCheck WHERE [uniqueid] = @UniqueID and [CheckNumber] = @Employee_Check and ICNumber = @ICNumber and EmployeeName = @Employee
         if @lastrow > 1 
         begin
            set @LastRPID = ''
            set @LastR1 = ''
            set @LastR2 = ''
            set @LastR3 = ''
            set @LastR4 = ''
         end
         set @ControlNo = ''
--         GOTO Print_Header  -- no need we want this to drop through and get the FETCH NEXT - this is the end of the control records and needs a new row
      end

      if @lastRow = 1
      begin

         if @LastPay <> @PayAmount
         begin
            INSERT @textfile SELECT @rundate,@Check,'PT RESP'+right(space(12)+CAST(sum(isnull([PayAmount],0.00)) AS VARCHAR(20)),12)+'   CARC'+right(space(12)+CAST(sum(isnull([ContractAmount],0.00)) AS VARCHAR(20)),12)
                             + '  CLAIM TOTALS:'+right(space(13)+CAST(sum(isnull([SubmittedAmount],0.00)) AS VARCHAR(20)),13)+right(space(13)+CAST(sum(isnull([AllowedAmount],0.00)) AS VARCHAR(20)),13)+right(space(13)+CAST(sum(isnull([DeductAmount],0.00)) AS VARCHAR(20)),13)+right(space(13)+CAST(sum(isnull([PayAmount],0.00)) AS VARCHAR(20)),13)+right(space(14)+CAST(sum(isnull([PaidAmount],0.00)) AS VARCHAR(20)),14)  
            FROM tblEmployeeCheck where [CheckNumber] = @Employee_Check and bankname = @bank and employername = @employer and ICNumber = @ICNumber and EmployeeName = @Employee  
            GROUP BY  [CheckNumber],[bankname],[employername],[EmployeeName],[ICNumber]
            set @lastPay = @payamount
            GOTO Print_Header
         end


--         INSERT @textfile SELECT @rundate,@Check,right(space(12)+CAST(max(isnull([AllowedAmount],0.00)) AS VARCHAR(50)),12) FROM tblEmployeeCheck where [CheckNumber] = @Employee_Check and bankname = @bank and employername = @employer and ICNumber = @ICNumber and EmployeeName = @Employee  
--         GROUP BY  [CheckNumber],[bankname],[employername],[EmployeeName],[ICNumber]
--         set @linecounter = @linecounter + 1
   
         if @LastForward <> @ForwardedTo
         begin	     
            if len(rtrim(ltrim(@forwardedTo))) > 1
            begin
               INSERT @textfile SELECT @rundate,@Check,'INFORMATION FORWARDED TO:             '+ISNULL([ForwardedTO], '') FROM tblEmployeeCheck where [CheckNumber] = @Employee_Check and bankname = @bank and employername = @employer and bankname = @bank and employername = @employer and ICNumber = @ICNumber and EmployeeName = @Employee  GROUP BY [ForwardedTO]
               INSERT @textfile SELECT @rundate,@Check,'_________________________________________________________________________________________________________________________________________'
               set @linecounter = @linecounter + 1
               set @LastForward = @ForwardedTo
--               GOTO Print_Header   -- not needed because end of loop anyway
            end  
         end
      end
	       
      FETCH NEXT FROM filename_cursor INTO @UniqueID,@Employee_Check,@ICNumber,@Employee,@bank,@employer,@RPIDNumber,@ControlNo,@reason1,@reason2,@reason3,@reason4,@PayAmount,@ContractAmount,@AllowedPayment,@ForwardedTo,@lastRow,@LastControlNo
      
    END

      CLOSE filename_cursor
      DEALLOCATE filename_cursor

      if @header > 0
      begin
         update @textfile set exportrecord = replace(exportrecord,'999999',format(@pagecounter,'######')) where linectr = @Header      
         INSERT @textfile SELECT @rundate,@Check,'------------------------ Page '+CONVERT(VARCHAR(3),@pagecounter)+' ------------------------' 
      end
      else
         INSERT @textfile SELECT @rundate,@Check,'No Check Details Exist For '+@Check

   RETURN
END

Open in new window

Running for the test samples you had before check '1020133309' and check 'F723583752'
select exportrecord from dbo.udf_export_EmployeeCheck('1020133309')

select exportrecord from dbo.udf_export_EmployeeCheck('F723583752')

Open in new window

I have attached the results
exportrecord.txt
1
 
pcelbaCommented:
The code itself is a snippet and it cannot work. You should post the whole script.
0
 
PatHartmanCommented:
Text files are not updateable in this manner.  Text file records are basically end to end.  To insert something after 47 records would require rewriting ALL the records that follow in order to make room for the inserted data, even if it is a single byte.  The way to update text files is to read the original file and write an updated file.  At the end, if you want the names to be kept, you would rename the original to some "keep" name and rename the new version to the original name.  Databases work much differently and they allow all actions on random records.  Behind the scenes, the code moves stuff around and uses pointers.  So for example, if you updated a record and the result made it longer, the engine could not put it back where it was without rewriting potentially hundreds of records.  So, what it does, is to insert in place of the record a pointer to the actual disk address where it wrote the updated record.
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
ZberteocCommented:
Try this:
declare
	@row int=1,
	@rows_per_page int=47,
	@pagecounter int=0
while @row<=1000
begin
	if @row%@rows_per_page=0
	begin
		set @pagecounter=@pagecounter+1
	end
	print cast(@row as varchar(5))+case when @row%@rows_per_page=0 then '------------------------ Page '+CONVERT(VARCHAR(3),@pagecounter)+' ------------------------' else '' end
	set @row=@row+1
end

Open in new window

0
 
Queennie LAuthor Commented:
I test this code below and it is working and output is attached. The only thing I cannot figure out is the Page number at the 47 rows.

This is the code:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[udf_export_EmployeeCheck](@Check VARCHAR(50))
RETURNS @textfile TABLE 
	(
	linectr INT IDENTITY, 
	rundate DATETIME, 
	[Filename] VARCHAR(50),
	exportrecord VARCHAR(500)
	)
AS
BEGIN
	
	DECLARE @rundate DATETIME = GETDATE()
	DECLARE @pagecounter INT = 0
	DECLARE @constant5010 INT = 5010
	DECLARE @UniqueID VARCHAR(150)
	DECLARE @Employee_Filename VARCHAR(150)
	DECLARE @Employee_Check VARCHAR(150)
	DECLARE @Employee varchar(50)
	DECLARE @EmpID varchar(50)
	DECLARE @Employer varchar(50)
	
	
	DECLARE filename_cursor CURSOR FOR SELECT MAX(UniqueID) AS UniqueID,[CheckNumber],[EmpID],[EmployeeName] FROM [tblEmployeeCheck] WHERE [CheckNumber] = @Check GROUP BY [CheckNumber],[EmpID],[EmployeeName]
	

	OPEN filename_cursor
	FETCH NEXT FROM filename_cursor INTO @UniqueID,@Employee_Check,@EmpID,@Employee
	WHILE @@FETCH_STATUS = 0
	BEGIN
	
		SET @pagecounter = @pagecounter + 1
		

		IF @pagecounter = 1
		BEGIN
			INSERT @textfile SELECT @rundate,@Check,'------------------------ '+ConstantVariable+' ------------------------' 
                              FROM [dbo].[Employee_ConstantVariable] WHERE UniqueID = @pagecounter
			INSERT @textfile SELECT @rundate,@Check,'                                                       '
		
		/* - - - - - HEADER PAGE INFORMATION - - - - - */
		INSERT @textfile SELECT @rundate,@Check,ISNULL(('  Check/EFT #:     '), '') + ISNULL([CheckNumber], '') FROM tblEmployeeCheck WHERE UniqueID = UniqueID AND [CheckNumber] = @Employee_Check
		INSERT @textfile SELECT @rundate,@Check,ISNULL(('  Pages:           '), '') + ISNULL([TotalPages], '') FROM tblEmployeeCheck WHERE UniqueID = UniqueID AND [CheckNumber] = @Employee_Check GROUP BY [CheckNumber],[TotalPages]
		INSERT @textfile SELECT @rundate,@Check,ISNULL(('  Check Tot:  '), '') + ISNULL([CheckTot], '') FROM tblEmployeeCheck WHERE UniqueID = UniqueID AND [CheckNumber] = @Employee_Check 
		INSERT @textfile SELECT @rundate,@Check,ISNULL(('  Check Date:      '), '') + ISNULL([CheckDate], '') FROM tblEmployeeCheck WHERE UniqueID = UniqueID AND [CheckNumber] = @Employee_Check 
		INSERT @textfile SELECT @rundate,@Check,ISNULL(('  Ck Type:         '), '') + ISNULL([PaymentMethodCode], '') FROM tblEmployeeCheck WHERE UniqueID = UniqueID AND [CheckNumber] = @Employee_Check 
		INSERT @textfile SELECT @rundate,@Check,'                                                       '         
		
		INSERT @textfile SELECT @rundate,@Check,'___________________________________________________________________________________________________________________________________________'

		
		/* - - - - - Employer INFORMATION - - - - - */
		INSERT @textfile SELECT @rundate,@Check,ISNULL([EmployerName], '') FROM tblEmployeeCheck WHERE UniqueID = UniqueID AND [CheckNumber] = @Employee_Check 
		INSERT @textfile SELECT @rundate,@Check,ISNULL([EmployerAddress], '') FROM tblEmployeeCheck WHERE UniqueID = UniqueID AND [CheckNumber] = @Employee_Check 
		INSERT @textfile SELECT @rundate,@Check,ISNULL([EmployerCityStateZipcode], '') FROM tblEmployeeCheck WHERE UniqueID = UniqueID AND [CheckNumber] = @Employee_Check 
		INSERT @textfile SELECT @rundate,@Check,ISNULL(('Employer BUSINESS CONTACT INFORMATION:         '), '') 
		INSERT @textfile SELECT @rundate,@Check,ISNULL([EmployerContactName1_NO], '') FROM tblEmployeeCheck WHERE UniqueID = UniqueID AND [CheckNumber] = @Employee_Check 
		INSERT @textfile SELECT @rundate,@Check,ISNULL([EmployerContactCommunication1_No], '') FROM tblEmployeeCheck WHERE UniqueID = UniqueID AND [CheckNumber] = @Employee_Check 
		INSERT @textfile SELECT @rundate,@Check,'                                                       '
		                                    
		/* - - - - - Employee INFORMATION - - - - - */                                                                                                    
		INSERT @textfile SELECT @rundate,@Check,LEFT(ISNULL([BankInformation], '')+'                                        ',40)+ISNULL(('                                                        Bank ID: '), '') + ISNULL([EmployeeID], '') FROM tblEmployeeCheck WHERE UniqueID = UniqueID AND [CheckNumber] = @Employee_Check
		INSERT @textfile SELECT @rundate,@Check,LEFT(ISNULL([BankAddress], '')+'                                        ',40)+ISNULL(('                                                        DATE:  '), '') + ISNULL([Date], '') FROM tblEmployeeCheck WHERE UniqueID = UniqueID AND [CheckNumber] = @Employee_Check
		INSERT @textfile SELECT @rundate,@Check,LEFT(ISNULL([BankCityStateZipcode], '')+'                                        ',40) 
		INSERT @textfile SELECT @rundate,@Check,'                                                       '

		INSERT @textfile SELECT @rundate,@Check,ISNULL(('Check/EFT #:     '), '') + ISNULL([CheckNumber], '') FROM tblEmployeeCheck WHERE UniqueID = UniqueID AND [CheckNumber] = @Employee_Check
		INSERT @textfile SELECT @rundate,@Check,'Employee Name  ' + 'Account Number  ' 
		INSERT @textfile SELECT @rundate,@Check,'___________________________________________________________________________________________________________________________________________'
        
		
		END
		ELSE
	
		
		/* - - - - - DETAIL PAGE INFORMATION - - - - - */
		INSERT @textfile SELECT @rundate,@Check,'___________________________________________________________________________________________________________________________________________'
        INSERT @textfile SELECT @rundate,@Check,LEFT(ISNULL(('EMPLOYEE NAME: '), '')+'      ',6) +LEFT(ISNULL([EmployeeName], '')+'                         ',25)+LEFT(ISNULL(('ACNT:'), '')+'     ',5)+LEFT(ISNULL([AccountNo], '')  FROM tblEmployeeCheck WHERE [CheckNumber] = @Employee_Check and EmpID = @EmpID and EmployeeName = @Employee GROUP BY [EmployeeName],[EmpID] 
		
		INSERT @textfile SELECT @rundate,@Check,+ ISNULL(('                             TOTALS:                '), '')+LEFT(ISNULL((CAST(SUM([SubmittedAmount]) AS VARCHAR)),'0.00')+'             ',13)+LEFT(ISNULL((CAST(SUM([PaidAmount]) AS VARCHAR)),'0.00')+'             ',13)  FROM tblEmployeeCheck where [CheckNumber] = @Employee_Check and EmpID = @EmpID and EmployeeName = @Employee  GROUP BY  EmpID, EmployeeName
	    
		FETCH NEXT FROM filename_cursor INTO UniqueID,@Employee_Check,@EmpID,@Employee

		
	    END

		BEGIN
		INSERT @textfile SELECT @rundate,@Check,'------------------------ Page '+CONVERT(VARCHAR(3),@pagecounter)+' ------------------------' 
   	    End


	    CLOSE filename_cursor
	    DEALLOCATE filename_cursor

		

	    IF @pagecounter = 0
    	INSERT @textfile SELECT @rundate,@Check,'No Check Details Exist For '+@Check
	   
	  
		

	RETURN
END

Open in new window


Thank you for all your help.
00003795_04112018.txt
0
 
Queennie LAuthor Commented:
@ZBerteoc:

I will test your code logic and I will let you know.

Thank you.
0
 
Queennie LAuthor Commented:
@ZBerteoc:

I added your code but it gives an error "Invalid use of a side-effecting operator 'PRINT' within a function."

Thank you for your help.
0
 
Kyle AbrahamsSenior .Net DeveloperCommented:
-- at the top before your loop:
declare @pageNumber int = 1 


	/* - - - - - DETAIL PAGE INFORMATION - - - - - */
		INSERT @textfile SELECT @rundate,@Check,'___________________________________________________________________________________________________________________________________________'
        INSERT @textfile SELECT @rundate,@Check,LEFT(ISNULL(('EMPLOYEE NAME: '), '')+'      ',6) +LEFT(ISNULL([EmployeeName], '')+'                         ',25)+LEFT(ISNULL(('ACNT:'), '')+'     ',5)+LEFT(ISNULL([AccountNo], '')  FROM tblEmployeeCheck WHERE [CheckNumber] = @Employee_Check and EmpID = @EmpID and EmployeeName = @Employee GROUP BY [EmployeeName],[EmpID] 

/**** NEW LOGIC *****/
  if @pagecounter % 47 = 0 
  begin
      INSERT @textfile SELECT  @rundate,@Check, 'PAGE NUMBER: ' + convert(varchar, @pageNumber)
      set @pageNumber = @pageNumber + 1
 end

Open in new window

0
 
Queennie LAuthor Commented:
@Kyle Abrahams:

I insert that code and tested it but for some reason the page number is not showing.

Thank you for your help.
0
 
ZberteocCommented:
I used PRINT just for illustrate the logic but you will have to adapt your code. I guess it will be used in the INSERT line.
0
 
ZberteocCommented:
I guess this should do it, only I recommend to use a variable to store the line per page number(47) as I hard coded it:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[udf_export_EmployeeCheck](@Check VARCHAR(50))
RETURNS @textfile TABLE 
	(
	linectr INT IDENTITY, 
	rundate DATETIME, 
	[Filename] VARCHAR(50),
	exportrecord VARCHAR(500)
	)
AS
BEGIN
	
	DECLARE @rundate DATETIME = GETDATE()
	DECLARE @pagecounter INT = 0
	DECLARE @constant5010 INT = 5010
	DECLARE @UniqueID VARCHAR(150)
	DECLARE @Employee_Filename VARCHAR(150)
	DECLARE @Employee_Check VARCHAR(150)
	DECLARE @Employee varchar(50)
	DECLARE @EmpID varchar(50)
	DECLARE @Employer varchar(50)
	
	
	DECLARE filename_cursor CURSOR FOR SELECT MAX(UniqueID) AS UniqueID,[CheckNumber],[EmpID],[EmployeeName] FROM [tblEmployeeCheck] WHERE [CheckNumber] = @Check GROUP BY [CheckNumber],[EmpID],[EmployeeName]
	

	OPEN filename_cursor
	FETCH NEXT FROM filename_cursor INTO @UniqueID,@Employee_Check,@EmpID,@Employee
	WHILE @@FETCH_STATUS = 0
	BEGIN
	
		SET @pagecounter = @pagecounter + 1
		

		IF @pagecounter = 1
		BEGIN
			INSERT @textfile SELECT @rundate,@Check,'------------------------ '+ConstantVariable+' ------------------------' 
                              FROM [dbo].[Employee_ConstantVariable] WHERE UniqueID = @pagecounter
			INSERT @textfile SELECT @rundate,@Check,'                                                       '
		
		/* - - - - - HEADER PAGE INFORMATION - - - - - */
		INSERT @textfile SELECT @rundate,@Check,ISNULL(('  Check/EFT #:     '), '') + ISNULL([CheckNumber], '') FROM tblEmployeeCheck WHERE UniqueID = UniqueID AND [CheckNumber] = @Employee_Check
		INSERT @textfile SELECT @rundate,@Check,ISNULL(('  Pages:           '), '') + ISNULL([TotalPages], '') FROM tblEmployeeCheck WHERE UniqueID = UniqueID AND [CheckNumber] = @Employee_Check GROUP BY [CheckNumber],[TotalPages]
		INSERT @textfile SELECT @rundate,@Check,ISNULL(('  Check Tot:  '), '') + ISNULL([CheckTot], '') FROM tblEmployeeCheck WHERE UniqueID = UniqueID AND [CheckNumber] = @Employee_Check 
		INSERT @textfile SELECT @rundate,@Check,ISNULL(('  Check Date:      '), '') + ISNULL([CheckDate], '') FROM tblEmployeeCheck WHERE UniqueID = UniqueID AND [CheckNumber] = @Employee_Check 
		INSERT @textfile SELECT @rundate,@Check,ISNULL(('  Ck Type:         '), '') + ISNULL([PaymentMethodCode], '') FROM tblEmployeeCheck WHERE UniqueID = UniqueID AND [CheckNumber] = @Employee_Check 
		INSERT @textfile SELECT @rundate,@Check,'                                                       '         
		
		INSERT @textfile SELECT @rundate,@Check,'___________________________________________________________________________________________________________________________________________'

		
		/* - - - - - Employer INFORMATION - - - - - */
		INSERT @textfile SELECT @rundate,@Check,ISNULL([EmployerName], '') FROM tblEmployeeCheck WHERE UniqueID = UniqueID AND [CheckNumber] = @Employee_Check 
		INSERT @textfile SELECT @rundate,@Check,ISNULL([EmployerAddress], '') FROM tblEmployeeCheck WHERE UniqueID = UniqueID AND [CheckNumber] = @Employee_Check 
		INSERT @textfile SELECT @rundate,@Check,ISNULL([EmployerCityStateZipcode], '') FROM tblEmployeeCheck WHERE UniqueID = UniqueID AND [CheckNumber] = @Employee_Check 
		INSERT @textfile SELECT @rundate,@Check,ISNULL(('Employer BUSINESS CONTACT INFORMATION:         '), '') 
		INSERT @textfile SELECT @rundate,@Check,ISNULL([EmployerContactName1_NO], '') FROM tblEmployeeCheck WHERE UniqueID = UniqueID AND [CheckNumber] = @Employee_Check 
		INSERT @textfile SELECT @rundate,@Check,ISNULL([EmployerContactCommunication1_No], '') FROM tblEmployeeCheck WHERE UniqueID = UniqueID AND [CheckNumber] = @Employee_Check 
		INSERT @textfile SELECT @rundate,@Check,'                                                       '
		                                    
		/* - - - - - Employee INFORMATION - - - - - */                                                                                                    
		INSERT @textfile SELECT @rundate,@Check,LEFT(ISNULL([BankInformation], '')+'                                        ',40)+ISNULL(('                                                        Bank ID: '), '') + ISNULL([EmployeeID], '') FROM tblEmployeeCheck WHERE UniqueID = UniqueID AND [CheckNumber] = @Employee_Check
		INSERT @textfile SELECT @rundate,@Check,LEFT(ISNULL([BankAddress], '')+'                                        ',40)+ISNULL(('                                                        DATE:  '), '') + ISNULL([Date], '') FROM tblEmployeeCheck WHERE UniqueID = UniqueID AND [CheckNumber] = @Employee_Check
		INSERT @textfile SELECT @rundate,@Check,LEFT(ISNULL([BankCityStateZipcode], '')+'                                        ',40) 
		INSERT @textfile SELECT @rundate,@Check,'                                                       '

		INSERT @textfile SELECT @rundate,@Check,ISNULL(('Check/EFT #:     '), '') + ISNULL([CheckNumber], '') FROM tblEmployeeCheck WHERE UniqueID = UniqueID AND [CheckNumber] = @Employee_Check
		INSERT @textfile SELECT @rundate,@Check,'Employee Name  ' + 'Account Number  ' 
		INSERT @textfile SELECT @rundate,@Check,'___________________________________________________________________________________________________________________________________________'
        
		
		END
		ELSE
	
		
		/* - - - - - DETAIL PAGE INFORMATION - - - - - */
		INSERT @textfile SELECT @rundate,@Check,'___________________________________________________________________________________________________________________________________________'
        INSERT @textfile SELECT @rundate,@Check,LEFT(ISNULL(('EMPLOYEE NAME: '), '')+'      ',6) +LEFT(ISNULL([EmployeeName], '')+'                         ',25)+LEFT(ISNULL(('ACNT:'), '')+'     ',5)+LEFT(ISNULL([AccountNo], '')  FROM tblEmployeeCheck WHERE [CheckNumber] = @Employee_Check and EmpID = @EmpID and EmployeeName = @Employee GROUP BY [EmployeeName],[EmpID] 
		
		INSERT @textfile SELECT @rundate,@Check,+ ISNULL(('                             TOTALS:                '), '')+LEFT(ISNULL((CAST(SUM([SubmittedAmount]) AS VARCHAR)),'0.00')+'             ',13)+LEFT(ISNULL((CAST(SUM([PaidAmount]) AS VARCHAR)),'0.00')+'             ',13)  FROM tblEmployeeCheck where [CheckNumber] = @Employee_Check and EmpID = @EmpID and EmployeeName = @Employee  GROUP BY  EmpID, EmployeeName
	    
		FETCH NEXT FROM filename_cursor INTO UniqueID,@Employee_Check,@EmpID,@Employee

		
	    END

		IF @pagecounter%47=0
		BEGIN
		INSERT @textfile SELECT @rundate,@Check,'------------------------ Page '+CONVERT(VARCHAR(3),@pagecounter)+' ------------------------' 
   	    End


	    CLOSE filename_cursor
	    DEALLOCATE filename_cursor

		

	    IF @pagecounter = 0
    	INSERT @textfile SELECT @rundate,@Check,'No Check Details Exist For '+@Check
		

	RETURN
END

Open in new window

0
 
Mark WillsTopic AdvisorCommented:
47th Row ?

Should be easy enough :)

Just add in a linecounter and check for that as well then increment page only when the linecounter > 46

Hvent tested and will do in a little while.

Sorry about that, been in a meeting for the last 3 hours without hitting SUBMIT - duh.
ALTER FUNCTION [dbo].[udf_export_EmployeeCheck](@Check VARCHAR(50))
RETURNS @textfile TABLE 
	(
	linectr INT IDENTITY, 
	rundate DATETIME, 
	[Filename] VARCHAR(50),
	exportrecord VARCHAR(500)
	)
AS
BEGIN
	
	DECLARE @rundate DATETIME = GETDATE()
	DECLARE @pagecounter INT = 1
	DECLARE @linecounter INT = 0
	DECLARE @constant5010 INT = 5010
	DECLARE @UniqueID VARCHAR(150)
	DECLARE @Employee_Filename VARCHAR(150)
	DECLARE @Employee_Check VARCHAR(150)
	DECLARE @Employee varchar(50)
	DECLARE @EmpID varchar(50)
	DECLARE @Employer varchar(50)
	
	
	DECLARE filename_cursor CURSOR FOR SELECT MAX(UniqueID) AS UniqueID,[CheckNumber],[EmpID],[EmployeeName] FROM [tblEmployeeCheck] WHERE [CheckNumber] = @Check GROUP BY [CheckNumber],[EmpID],[EmployeeName]
	

	OPEN filename_cursor
	FETCH NEXT FROM filename_cursor INTO @UniqueID,@Employee_Check,@EmpID,@Employee
	WHILE @@FETCH_STATUS = 0
	BEGIN
	
		SET @linecounter = @Linecounter + 1
		

		IF @pagecounter = 1 or @linecounter > 46
		BEGIN
			INSERT @textfile SELECT @rundate,@Check,'------------------------ '+ConstantVariable+' ------------------------' 
                              FROM [dbo].[Employee_ConstantVariable] WHERE UniqueID = @pagecounter
			INSERT @textfile SELECT @rundate,@Check,'                                                       '
		
		/* - - - - - HEADER PAGE INFORMATION - - - - - */
		INSERT @textfile SELECT @rundate,@Check,ISNULL(('  Check/EFT #:     '), '') + ISNULL([CheckNumber], '') FROM tblEmployeeCheck WHERE UniqueID = UniqueID AND [CheckNumber] = @Employee_Check
		INSERT @textfile SELECT @rundate,@Check,ISNULL(('  Pages:           '), '') + ISNULL([TotalPages], '') FROM tblEmployeeCheck WHERE UniqueID = UniqueID AND [CheckNumber] = @Employee_Check GROUP BY [CheckNumber],[TotalPages]
		INSERT @textfile SELECT @rundate,@Check,ISNULL(('  Check Tot:  '), '') + ISNULL([CheckTot], '') FROM tblEmployeeCheck WHERE UniqueID = UniqueID AND [CheckNumber] = @Employee_Check 
		INSERT @textfile SELECT @rundate,@Check,ISNULL(('  Check Date:      '), '') + ISNULL([CheckDate], '') FROM tblEmployeeCheck WHERE UniqueID = UniqueID AND [CheckNumber] = @Employee_Check 
		INSERT @textfile SELECT @rundate,@Check,ISNULL(('  Ck Type:         '), '') + ISNULL([PaymentMethodCode], '') FROM tblEmployeeCheck WHERE UniqueID = UniqueID AND [CheckNumber] = @Employee_Check 
		INSERT @textfile SELECT @rundate,@Check,'                                                       '         
		
		INSERT @textfile SELECT @rundate,@Check,'___________________________________________________________________________________________________________________________________________'

		
		/* - - - - - Employer INFORMATION - - - - - */
		INSERT @textfile SELECT @rundate,@Check,ISNULL([EmployerName], '') FROM tblEmployeeCheck WHERE UniqueID = UniqueID AND [CheckNumber] = @Employee_Check 
		INSERT @textfile SELECT @rundate,@Check,ISNULL([EmployerAddress], '') FROM tblEmployeeCheck WHERE UniqueID = UniqueID AND [CheckNumber] = @Employee_Check 
		INSERT @textfile SELECT @rundate,@Check,ISNULL([EmployerCityStateZipcode], '') FROM tblEmployeeCheck WHERE UniqueID = UniqueID AND [CheckNumber] = @Employee_Check 
		INSERT @textfile SELECT @rundate,@Check,ISNULL(('Employer BUSINESS CONTACT INFORMATION:         '), '') 
		INSERT @textfile SELECT @rundate,@Check,ISNULL([EmployerContactName1_NO], '') FROM tblEmployeeCheck WHERE UniqueID = UniqueID AND [CheckNumber] = @Employee_Check 
		INSERT @textfile SELECT @rundate,@Check,ISNULL([EmployerContactCommunication1_No], '') FROM tblEmployeeCheck WHERE UniqueID = UniqueID AND [CheckNumber] = @Employee_Check 
		INSERT @textfile SELECT @rundate,@Check,'                                                       '
		                                    
		/* - - - - - Employee INFORMATION - - - - - */                                                                                                    
		INSERT @textfile SELECT @rundate,@Check,LEFT(ISNULL([BankInformation], '')+'                                        ',40)+ISNULL(('                                                        Bank ID: '), '') + ISNULL([EmployeeID], '') FROM tblEmployeeCheck WHERE UniqueID = UniqueID AND [CheckNumber] = @Employee_Check
		INSERT @textfile SELECT @rundate,@Check,LEFT(ISNULL([BankAddress], '')+'                                        ',40)+ISNULL(('                                                        DATE:  '), '') + ISNULL([Date], '') FROM tblEmployeeCheck WHERE UniqueID = UniqueID AND [CheckNumber] = @Employee_Check
		INSERT @textfile SELECT @rundate,@Check,LEFT(ISNULL([BankCityStateZipcode], '')+'                                        ',40) 
		INSERT @textfile SELECT @rundate,@Check,'                                                       '

		INSERT @textfile SELECT @rundate,@Check,ISNULL(('Check/EFT #:     '), '') + ISNULL([CheckNumber], '') FROM tblEmployeeCheck WHERE UniqueID = UniqueID AND [CheckNumber] = @Employee_Check
		INSERT @textfile SELECT @rundate,@Check,'Employee Name  ' + 'Account Number  ' 
		INSERT @textfile SELECT @rundate,@Check,'___________________________________________________________________________________________________________________________________________'
 
        set @linecounter = 23        
		SET @pagecounter = @pagecounter + 1
		
		END
		ELSE
	
		
		/* - - - - - DETAIL PAGE INFORMATION - - - - - */
		INSERT @textfile SELECT @rundate,@Check,'___________________________________________________________________________________________________________________________________________'
        INSERT @textfile SELECT @rundate,@Check,LEFT(ISNULL(('EMPLOYEE NAME: '), '')+'      ',6) +LEFT(ISNULL([EmployeeName], '')+'                         ',25)+LEFT(ISNULL(('ACNT:'), '')+'     ',5)+LEFT(ISNULL([AccountNo], '')  FROM tblEmployeeCheck WHERE [CheckNumber] = @Employee_Check and EmpID = @EmpID and EmployeeName = @Employee GROUP BY [EmployeeName],[EmpID] 
		
		INSERT @textfile SELECT @rundate,@Check,+ ISNULL(('                             TOTALS:                '), '')+LEFT(ISNULL((CAST(SUM([SubmittedAmount]) AS VARCHAR)),'0.00')+'             ',13)+LEFT(ISNULL((CAST(SUM([PaidAmount]) AS VARCHAR)),'0.00')+'             ',13)  FROM tblEmployeeCheck where [CheckNumber] = @Employee_Check and EmpID = @EmpID and EmployeeName = @Employee  GROUP BY  EmpID, EmployeeName

        set @linecounter = @linecounter + 3        

	    
		FETCH NEXT FROM filename_cursor INTO UniqueID,@Employee_Check,@EmpID,@Employee

		
	    END

		BEGIN
		INSERT @textfile SELECT @rundate,@Check,'------------------------ Page '+CONVERT(VARCHAR(3),@pagecounter)+' ------------------------' 
   	    End


	    CLOSE filename_cursor
	    DEALLOCATE filename_cursor

		

	    IF @pagecounter = 0
    	INSERT @textfile SELECT @rundate,@Check,'No Check Details Exist For '+@Check
	   
	  
		

	RETURN
END

Open in new window

0
 
Queennie LAuthor Commented:
@Mark Wills:

I will test this and will let you know.

Thanks again.
0
 
Kyle AbrahamsSenior .Net DeveloperCommented:
is each insert consider it's own line?  Or each run thru the cursor 1 line?
0
 
Queennie LAuthor Commented:
@Kyle Abrahams:

Each insert has it's own line.

thanks again.
0
 
Kyle AbrahamsSenior .Net DeveloperCommented:
And so when you're doing the detail line do you want them grouped together or just always insert the page number at the 47th line?
0
 
Mark WillsTopic AdvisorCommented:
Each insert represents a line on the page.
0
 
ZberteocCommented:
Have you tried my solution?
0
 
Kyle AbrahamsSenior .Net DeveloperCommented:
try this:

ALTER FUNCTION [dbo].[udf_export_EmployeeCheck](@Check VARCHAR(50))
RETURNS @textfile TABLE 
	(
	linectr INT IDENTITY, 
	rundate DATETIME, 
	[Filename] VARCHAR(50),
	exportrecord VARCHAR(500)
	)
AS
BEGIN
	
	DECLARE @rundate DATETIME = GETDATE()
	DECLARE @pagecounter INT = 0
	DECLARE @linecounter INT = 0
	DECLARE @constant5010 INT = 5010
	DECLARE @UniqueID VARCHAR(150)
	DECLARE @Employee_Filename VARCHAR(150)
	DECLARE @Employee_Check VARCHAR(150)
	DECLARE @Employee varchar(50)
	DECLARE @EmpID varchar(50)
	DECLARE @Employer varchar(50)
	
	
	DECLARE filename_cursor CURSOR FOR SELECT MAX(UniqueID) AS UniqueID,[CheckNumber],[EmpID],[EmployeeName] FROM [tblEmployeeCheck] WHERE [CheckNumber] = @Check GROUP BY [CheckNumber],[EmpID],[EmployeeName]
	
	-- number of header lines in the file
	declare @HeaderLinesCount int = 23
	-- numebr of detail lines in the file
	declare @DetailsLinesCount int = 3

	-- last line of the page to print the page number
	declare @Pagebreak int = 47

	-- used for determining if the header should be printed again.
	declare @newPage int = 1

	OPEN filename_cursor
	FETCH NEXT FROM filename_cursor INTO @UniqueID,@Employee_Check,@EmpID,@Employee
	WHILE @@FETCH_STATUS = 0
	BEGIN
	
	
		

		IF @newPage = 1 
		BEGIN
			INSERT @textfile SELECT @rundate,@Check,'------------------------ '+ConstantVariable+' ------------------------' 
                              FROM [dbo].[Employee_ConstantVariable] WHERE UniqueID = @pagecounter
			INSERT @textfile SELECT @rundate,@Check,'                                                       '
		
		/* - - - - - HEADER PAGE INFORMATION - - - - - */
		INSERT @textfile SELECT @rundate,@Check,ISNULL(('  Check/EFT #:     '), '') + ISNULL([CheckNumber], '') FROM tblEmployeeCheck WHERE UniqueID = UniqueID AND [CheckNumber] = @Employee_Check
		INSERT @textfile SELECT @rundate,@Check,ISNULL(('  Pages:           '), '') + ISNULL([TotalPages], '') FROM tblEmployeeCheck WHERE UniqueID = UniqueID AND [CheckNumber] = @Employee_Check GROUP BY [CheckNumber],[TotalPages]
		INSERT @textfile SELECT @rundate,@Check,ISNULL(('  Check Tot:  '), '') + ISNULL([CheckTot], '') FROM tblEmployeeCheck WHERE UniqueID = UniqueID AND [CheckNumber] = @Employee_Check 
		INSERT @textfile SELECT @rundate,@Check,ISNULL(('  Check Date:      '), '') + ISNULL([CheckDate], '') FROM tblEmployeeCheck WHERE UniqueID = UniqueID AND [CheckNumber] = @Employee_Check 
		INSERT @textfile SELECT @rundate,@Check,ISNULL(('  Ck Type:         '), '') + ISNULL([PaymentMethodCode], '') FROM tblEmployeeCheck WHERE UniqueID = UniqueID AND [CheckNumber] = @Employee_Check 
		INSERT @textfile SELECT @rundate,@Check,'                                                       '         
		
		INSERT @textfile SELECT @rundate,@Check,'___________________________________________________________________________________________________________________________________________'

		
		/* - - - - - Employer INFORMATION - - - - - */
		INSERT @textfile SELECT @rundate,@Check,ISNULL([EmployerName], '') FROM tblEmployeeCheck WHERE UniqueID = UniqueID AND [CheckNumber] = @Employee_Check 
		INSERT @textfile SELECT @rundate,@Check,ISNULL([EmployerAddress], '') FROM tblEmployeeCheck WHERE UniqueID = UniqueID AND [CheckNumber] = @Employee_Check 
		INSERT @textfile SELECT @rundate,@Check,ISNULL([EmployerCityStateZipcode], '') FROM tblEmployeeCheck WHERE UniqueID = UniqueID AND [CheckNumber] = @Employee_Check 
		INSERT @textfile SELECT @rundate,@Check,ISNULL(('Employer BUSINESS CONTACT INFORMATION:         '), '') 
		INSERT @textfile SELECT @rundate,@Check,ISNULL([EmployerContactName1_NO], '') FROM tblEmployeeCheck WHERE UniqueID = UniqueID AND [CheckNumber] = @Employee_Check 
		INSERT @textfile SELECT @rundate,@Check,ISNULL([EmployerContactCommunication1_No], '') FROM tblEmployeeCheck WHERE UniqueID = UniqueID AND [CheckNumber] = @Employee_Check 
		INSERT @textfile SELECT @rundate,@Check,'                                                       '
		                                    
		/* - - - - - Employee INFORMATION - - - - - */                                                                                                    
		INSERT @textfile SELECT @rundate,@Check,LEFT(ISNULL([BankInformation], '')+'                                        ',40)+ISNULL(('                                                        Bank ID: '), '') + ISNULL([EmployeeID], '') FROM tblEmployeeCheck WHERE UniqueID = UniqueID AND [CheckNumber] = @Employee_Check
		INSERT @textfile SELECT @rundate,@Check,LEFT(ISNULL([BankAddress], '')+'                                        ',40)+ISNULL(('                                                        DATE:  '), '') + ISNULL([Date], '') FROM tblEmployeeCheck WHERE UniqueID = UniqueID AND [CheckNumber] = @Employee_Check
		INSERT @textfile SELECT @rundate,@Check,LEFT(ISNULL([BankCityStateZipcode], '')+'                                        ',40) 
		INSERT @textfile SELECT @rundate,@Check,'                                                       '

		INSERT @textfile SELECT @rundate,@Check,ISNULL(('Check/EFT #:     '), '') + ISNULL([CheckNumber], '') FROM tblEmployeeCheck WHERE UniqueID = UniqueID AND [CheckNumber] = @Employee_Check
		INSERT @textfile SELECT @rundate,@Check,'Employee Name  ' + 'Account Number  ' 
		INSERT @textfile SELECT @rundate,@Check,'___________________________________________________________________________________________________________________________________________'
 
        set @linecounter = @HeaderLinesCount
		SET @pagecounter = @pagecounter + 1
		set @newPage = 0
		
		END
		ELSE
	
		
		/* - - - - - DETAIL PAGE INFORMATION - - - - - */
		INSERT @textfile SELECT @rundate,@Check,'___________________________________________________________________________________________________________________________________________'
        INSERT @textfile SELECT @rundate,@Check,LEFT(ISNULL(('EMPLOYEE NAME: '), '')+'      ',6) +LEFT(ISNULL([EmployeeName], '')+'                         ',25)+LEFT(ISNULL(('ACNT:'), '')+'     ',5)+LEFT(ISNULL([AccountNo], '')  FROM tblEmployeeCheck WHERE [CheckNumber] = @Employee_Check and EmpID = @EmpID and EmployeeName = @Employee GROUP BY [EmployeeName],[EmpID] 
		
		INSERT @textfile SELECT @rundate,@Check,+ ISNULL(('                             TOTALS:                '), '')+LEFT(ISNULL((CAST(SUM([SubmittedAmount]) AS VARCHAR)),'0.00')+'             ',13)+LEFT(ISNULL((CAST(SUM([PaidAmount]) AS VARCHAR)),'0.00')+'             ',13)  FROM tblEmployeeCheck where [CheckNumber] = @Employee_Check and EmpID = @EmpID and EmployeeName = @Employee  GROUP BY  EmpID, EmployeeName

        set @linecounter = @linecounter + @DetailsLinesCount
		
		if @lineCounter % @Pagebreak = 0 
		begin
		   -- print page header again
		   set @newPage =  1
                   
                   -- print the page number
		   INSERT @textfile SELECT @rundate,@Check,'------------------------ Page '+CONVERT(VARCHAR(3),@pagecounter)+' ------------------------' 
   	    End



	    
		FETCH NEXT FROM filename_cursor INTO UniqueID,@Employee_Check,@EmpID,@Employee

		
	    END




	    CLOSE filename_cursor
	    DEALLOCATE filename_cursor

		

	    IF @pagecounter = 0
    	INSERT @textfile SELECT @rundate,@Check,'No Check Details Exist For '+@Check
	   
	  
		

	RETURN
END

Open in new window

0
 
Queennie LAuthor Commented:
@Kyle:

I will test this and I will let you know.

thank you again for your help.
0
 
ZberteocCommented:
@Queennie L

I also posted a solution earlier at ID: 42529894 and I asked if tested.
0
 
Kyle AbrahamsSenior .Net DeveloperCommented:
zbert yours won't work.  You only insert one page number post the cursor loop.
0
 
Queennie LAuthor Commented:
So sorry I am running an errands. I will test it tomorrow.

Thank you for all the help. I really need this. I will let you know tomorrow.
0
 
Mark WillsTopic AdvisorCommented:
Hi Queennie,

Started to go through your function.

I think there are a LOT of errors in there. Have found and fixed most, but wont post just yet. Can you supply a table definition for employeechack and a sample layout of the page, then I can check / test properly.

The function you copied for this version was based on one employee / file per page this requirement is different and not simply adding in a line counter. Looks a bit more like multi employees per employer. So, header / page relationships will be different.
0
 
Queennie LAuthor Commented:
@Mark Wills:

Yes. I had death in my family. I will get back to you as soon as I get back to work.

I did not have a chance to test it yet.

Thank you for all your help.
0
 
Queennie LAuthor Commented:
Sorry Guys:

It took me awhile to post the tests. My apology and thank you again for all your help.

I tested all the SQL function above but seems like nothing is working.

Here is the SQL Function I revised and was originally written by Mark Willis:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[udf_export_EmployeeCheck](@Check VARCHAR(50))
RETURNS @textfile TABLE 
	(
	linectr INT IDENTITY, 
	rundate DATETIME, 
	[Filename] VARCHAR(50),
	exportrecord VARCHAR(500)
	)
AS
BEGIN
	
	DECLARE @rundate DATETIME = GETDATE()
	DECLARE @pagecounter INT = 0
	DECLARE @linecounter INT = 0
	DECLARE @constant5010 INT = 5010
	DECLARE @UniqueID VARCHAR(150)
	DECLARE @Employee_Filename VARCHAR(150)
	DECLARE @Employee_Check VARCHAR(150)
	DECLARE @Employee VARCHAR(50)
	DECLARE @ICNUmber VARCHAR(50)
	DECLARE @Bank VARCHAR(50)
	DECLARE @ServiceUnits VARCHAR(50)
	
	
	
	
	   
	
	DECLARE filename_cursor CURSOR FOR SELECT MAX(UniqueID) AS UniqueID,[CheckNumber],[ICNumber],[EmployeeName] FROM tblEmployeeCheck WHERE [CheckNumber] = @Check GROUP BY [CheckNumber],[ICNumber],[EmployeeName]
	

	-- number of header lines in the file
	declare @HeaderLinesCount int = 23
	-- numebr of detail lines in the file
	declare @DetailsLinesCount int = 3

	-- last line of the page to print the page number
	declare @Pagebreak int = 47

	-- used for determining if the header should be printed again.
	declare @newPage int = 1

	OPEN filename_cursor
	FETCH NEXT FROM filename_cursor INTO @UniqueID,@Employee_Check,@ICNUmber,@Employee
	WHILE @@FETCH_STATUS = 0
	BEGIN
	    

		--SET @pagecounter = @pagecounter + 1
		

		--IF @pagecounter = 1
		--**
		--SET @linecounter = @linecounter + 1
		

		--IF @pagecounter = 1 OR @linecounter > 46
		--**
		IF @newPage = 1

		BEGIN
			--INSERT @textfile SELECT @rundate,@Check,'------------------------ '+ConstantVariable+' ------------------------' 
   --                           FROM [dbo].[Employee_ConstantVariable] WHERE UniqueID = @pagecounter
			--INSERT @textfile SELECT @rundate,@Check,'------------------------ Page '+CONVERT(VARCHAR(3),@pagecounter)+' ------------------------' 
   	    
			INSERT @textfile SELECT @rundate,@Check,'                                                       '
		
		/* - - - - - HEADER PAGE INFORMATION - - - - - */
		INSERT @textfile SELECT @rundate,@Check,ISNULL(('  Check/EFT #:     '), '') + ISNULL([CheckNumber], '') FROM tblEmployeeCheck WHERE UniqueID = @UniqueID AND [CheckNumber] = @Employee_Check
		INSERT @textfile SELECT @rundate,@Check,ISNULL(('  Batch Number:    '), '') + ISNULL([BatchNumber], '') FROM tblEmployeeCheck WHERE UniqueID = @UniqueID AND [CheckNumber] = @Employee_Check
		INSERT @textfile SELECT @rundate,@Check,ISNULL(('  SetData:         '), '') + ISNULL([SetData], '') FROM tblEmployeeCheck WHERE UniqueID = @UniqueID AND [CheckNumber] = @Employee_Check
		INSERT @textfile SELECT @rundate,@Check,ISNULL(('  File:            '), '') + ISNULL([Filename], '') FROM tblEmployeeCheck WHERE UniqueID = @UniqueID AND [CheckNumber] = @Employee_Check
		INSERT @textfile SELECT @rundate,@Check,ISNULL(('  Bank Abbr:  '), '') + ISNULL([BankNameAbbre], '') FROM tblEmployeeCheck WHERE UniqueID = @UniqueID AND [CheckNumber] = @Employee_Check
		INSERT @textfile SELECT @rundate,@Check,ISNULL(('  Pages:           '), '') FROM tblEmployeeCheck WHERE UniqueID = @UniqueID AND [CheckNumber] = @Employee_Check GROUP BY [CheckNumber]
		INSERT @textfile SELECT @rundate,@Check,ISNULL(('  Check Total:  '), '') + ISNULL([CheckTotal], '') FROM tblEmployeeCheck WHERE UniqueID = @UniqueID AND [CheckNumber] = @Employee_Check 
		INSERT @textfile SELECT @rundate,@Check,ISNULL(('  Check Date:      '), '') + ISNULL([CheckDate], '') FROM tblEmployeeCheck WHERE UniqueID = @UniqueID AND [CheckNumber] = @Employee_Check 
		INSERT @textfile SELECT @rundate,@Check,ISNULL(('  Ck Type:         '), '') + ISNULL([PaymentMethodCode], '') FROM tblEmployeeCheck WHERE UniqueID = @UniqueID AND [CheckNumber] = @Employee_Check 
		INSERT @textfile SELECT @rundate,@Check,'                                                       '         
		
		INSERT @textfile SELECT @rundate,@Check,'___________________________________________________________________________________________________________________________________________'

		
		/* - - - - - Bank INFORMATION - - - - - */
		INSERT @textfile SELECT @rundate,@Check,ISNULL([BankName], '') FROM tblEmployeeCheck WHERE UniqueID = @UniqueID AND [CheckNumber] = @Employee_Check 
		INSERT @textfile SELECT @rundate,@Check,ISNULL([BankAddress], '') FROM tblEmployeeCheck WHERE UniqueID = @UniqueID AND [CheckNumber] = @Employee_Check 
		INSERT @textfile SELECT @rundate,@Check,ISNULL([BankCityStateZipcode], '') FROM tblEmployeeCheck WHERE UniqueID = @UniqueID AND [CheckNumber] = @Employee_Check 
		INSERT @textfile SELECT @rundate,@Check,ISNULL(('BANK BUSINESS CONTACT INFORMATION:         '), '') 
		INSERT @textfile SELECT @rundate,@Check,ISNULL([BankContactName1_NO], '') FROM tblEmployeeCheck WHERE UniqueID = @UniqueID AND [CheckNumber] = @Employee_Check 
		INSERT @textfile SELECT @rundate,@Check,ISNULL([BankContactCommunication1_No], '') FROM tblEmployeeCheck WHERE UniqueID = @UniqueID AND [CheckNumber] = @Employee_Check 
		INSERT @textfile SELECT @rundate,@Check,'                                                       '
		                                    
		/* - - - - - Employer INFORMATION - - - - - */                                                                                                    
		INSERT @textfile SELECT @rundate,@Check,LEFT(ISNULL([EmployerName], '')+'                                        ',40)+ISNULL(('                                                        NPI #: '), '') + ISNULL([EmployerID], '') FROM tblEmployeeCheck WHERE UniqueID = @UniqueID AND [CheckNumber] = @Employee_Check
		INSERT @textfile SELECT @rundate,@Check,LEFT(ISNULL([EmployerAddress], '')+'                                        ',40)+ISNULL(('                                                        DATE:  '), '') + ISNULL([BusiImportDate], '') FROM tblEmployeeCheck WHERE UniqueID = @UniqueID AND [CheckNumber] = @Employee_Check
		INSERT @textfile SELECT @rundate,@Check,LEFT(ISNULL([EmployerCityStateZipcode], '')+'                                        ',40)+ISNULL(('                                                        PAGE #:         '), '') FROM tblEmployeeCheck WHERE UniqueID = @UniqueID AND [CheckNumber] = @Employee_Check 
		INSERT @textfile SELECT @rundate,@Check,'                                                       '

		INSERT @textfile SELECT @rundate,@Check,ISNULL(('Check/EFT #:     '), '') + ISNULL([CheckNumber], '') FROM tblEmployeeCheck WHERE UniqueID = @UniqueID AND [CheckNumber] = @Employee_Check
		INSERT @textfile SELECT @rundate,@Check,'REND-PROV  ' + 'SERV-DATE  ' + 'POS PD-PROC/MODS        ' + 'PD-NOS        ' +  'BILLED      ' +  'ALLOWED       ' +  'DEDUCT        ' +  'COINS'
		INSERT @textfile SELECT @rundate,@Check,'PD'     
		INSERT @textfile SELECT @rundate,@Check,'LADC                                         ' + 'SUB-NOS      ' + 'SUB-PROC     ' + 'GRP/LADC     '+  'LADC-AMT      ' +  'ADJ-QTY'   
		INSERT @textfile SELECT @rundate,@Check,'___________________________________________________________________________________________________________________________________________'
        
		--SET @linecounter = 23        
		--SET @pagecounter = @pagecounter + 1
		
		--**
		set @linecounter = @HeaderLinesCount
		SET @pagecounter = @pagecounter + 1
		set @newPage = 0
		

		END
		

		ELSE
		--BEGIN
		--INSERT @textfile SELECT @rundate,@Check,'------------------------ Page '+CONVERT(VARCHAR(3),@pagecounter)+' ------------------------' 
   	    --INSERT @textfile SELECT @rundate,@Check,'___________________________________________________________________________________________________________________________________________'
        
	
        --INSERT @textfile SELECT @rundate,@Check,'                                                       ' 
		--END


		
		
		/* - - - - - DETAIL PAGE INFORMATION - - - - - */
		INSERT @textfile SELECT @rundate,@Check,'___________________________________________________________________________________________________________________________________________'
        INSERT @textfile SELECT @rundate,@Check,LEFT(ISNULL(('NAME: '), '')+'      ',6) +LEFT(ISNULL([EmployeeName], '')+'                         ',25)+LEFT(ISNULL(('CIH:'), '')+'    ',4) +LEFT(ISNULL([InsuredID], '')+'              ',14)+LEFT(ISNULL(('ACNT:'), '')+'     ',5)+LEFT(ISNULL([ClaimInformation], '')+'                 ',17)+LEFT(ISNULL([ICNumber], '')+'               ',15)+LEFT(ISNULL(('ASG:'), '')+'      ',6)  FROM tblEmployeeCheck WHERE [CheckNumber] = @Employee_Check and ICNumber = @ICNumber and EmployeeName = @Employee GROUP BY [EmployeeName],[InsuredID],[ClaimInformation],[ICNumber] 
		
		

		INSERT @textfile SELECT @rundate,@Check,LEFT(ISNULL([RPIDNumber], '')+'            ',12)+LEFT(ISNULL(([POSCode]+''+REPLACE([ServiceUnits], ' ', '')), '')+'                       ',23)+LEFT(ISNULL([Units], '')+'         ',9)+LEFT(ISNULL(CAST([SubmittedAmount] AS VARCHAR(50)), '')+'             ',13)+LEFT(ISNULL(CAST([AllowedAmount] AS VARCHAR(50)), '')+'             ',13)+LEFT(ISNULL(CAST([DeductAmount] AS VARCHAR(50)), '')+'              ',14)+LEFT(ISNULL(CAST([PayAmount] AS VARCHAR(50)), '')+'                    ',20)+ISNULL(CAST([PaidAmount] AS VARCHAR(50)), '')+
		+CHAR(13) + CHAR(10)+
		+SPACE(49)+LEFT([ReasonDescA1] + REPLICATE(' ',9), 9)+ISNULL([ReasonDescAAmount1], '')+
		+CHAR(13) + CHAR(10)+
		+REPLICATE(' ',49)+LEFT(ISNULL([ReasonDescA2], '')+'         ',9)+ISNULL([ReasonDescAAmount2], '')+
		+CHAR(13) + CHAR(10)+
		+REPLICATE(' ',49)+LEFT(ISNULL([ReasonDescA3], '')+'         ',9)+ISNULL([ReasonDescAAmount3], '')+
		+CHAR(13) + CHAR(10)+
		+REPLICATE(' ',49)+LEFT(ISNULL([ReasonDescA4], '')+'         ',9)+ISNULL([ReasonDescAAmount4], '')+
		+CHAR(13) + CHAR(10)+
		+'                        '+
		+CHAR(13) + CHAR(10)+
		+LEFT(ISNULL(('CNTL #:'), '')+'        ',8)+ISNULL([ControlNo], '')+
		+CHAR(13) + CHAR(10)+
		+LEFT(ISNULL(('PT RESP'), '')+'               ',15)+LEFT(ISNULL(CAST([PayAmount] AS VARCHAR(50)), '')+'              ',14)+LEFT(ISNULL(('CARC'), '')+'        ',8)+ISNULL(CAST([ContractAmount] AS VARCHAR(50)), '')
		FROM tblEmployeeCheck WHERE [CheckNumber] = @Employee_Check and ICNumber = @ICNumber and EmployeeName = @Employee GROUP BY [RPIDNumber],[POSCode],[ServiceUnits],[Units],[SubmittedAmount] ,[AllowedAmount],[DeductAmount],[PayAmount],[PaidAmount] ,[ReasonDescA1],[ReasonDescAAmount1],[ReasonDescA2],[ReasonDescAAmount2],[ReasonDescA3],[ReasonDescAAmount3],[ReasonDescA4],[ReasonDescAAmount4],[ControlNo],[ContractAmount] ORDER BY [ControlNo] ASC
		INSERT @textfile SELECT @rundate,@Check,+ ISNULL(('                             CLAIM TOTALS:                '), '')+LEFT(ISNULL((CAST(SUM([SubmittedAmount]) AS VARCHAR)),'0.00')+'             ',13)+LEFT(ISNULL((CAST(SUM([AllowedAmount]) AS VARCHAR)),'0.00')+'             ',13)+LEFT(ISNULL((CAST(SUM([DeductAmount]) AS VARCHAR)),'0.00')+'              ',14)+LEFT(ISNULL((CAST(SUM([PayAmount]) AS VARCHAR)),'0.00')+'                     ',21)+ISNULL((CAST(SUM([PaidAmount]) AS VARCHAR)),'0.00')  FROM tblEmployeeCheck where [CheckNumber] = @Employee_Check and ICNumber = @ICNumber and EmployeeName = @Employee  GROUP BY  ICNumber, EmployeeName
	    INSERT @textfile SELECT @rundate,@Check,+ISNULL((CAST(SUM([AllowedAmount]) AS VARCHAR)),'0.00') FROM tblEmployeeCheck where [CheckNumber] = @Employee_Check and ICNumber = @ICNumber and EmployeeName = @Employee  GROUP BY  ICNumber, EmployeeName 
	
	
		INSERT @textfile SELECT @rundate,@Check,'                                                       '
		INSERT @textfile SELECT @rundate,@Check,LEFT(ISNULL(('OTHER CLAIM:'), '')+'                                          ',36)
		+CHAR(13) + CHAR(10)
		+SPACE(37)+ISNULL(('ANOTHER CLAIM:'),'') FROM tblEmployeeCheck where [CheckNumber] = @Employee_Check  and ICNumber = @ICNumber and EmployeeName = @Employee  
				
		INSERT @textfile SELECT @rundate,@Check,LEFT(ISNULL(('INFORMATION FORWARDED TO:'), '')+'                                          ',36)+' '+ISNULL([ForwardedTO], '') FROM tblEmployeeCheck where [CheckNumber] = @Employee_Check  and ICNumber = @ICNumber and EmployeeName = @Employee  GROUP BY [ForwardedTO]

		
		--SET @linecounter = @linecounter + 3     
		
		--**
		set @linecounter = @linecounter + @DetailsLinesCount
		
		if @lineCounter % @Pagebreak = 0 
		begin
		   -- print page header again
		   set @newPage =  1
                   
           --**print the page number
		   INSERT @textfile SELECT @rundate,@Check,'------------------------ Page '+CONVERT(VARCHAR(3),@pagecounter)+' ------------------------' 
   	    End
  

		FETCH NEXT FROM filename_cursor INTO @UniqueID,@Employee_Check,@ICNumber,@Employee


		
	    END

		


		--BEGIN
		--INSERT @textfile SELECT @rundate,@Check,'------------------------ Page '+CONVERT(VARCHAR(3),@pagecounter)+' ------------------------' 
  -- 	    END

		IF @pagecounter%47=1
		BEGIN
		INSERT @textfile SELECT @rundate,@Check,'------------------------ Page '+CONVERT(VARCHAR(3),@pagecounter)+' ------------------------' 
   	    End



		CLOSE filename_cursor
		DEALLOCATE filename_cursor

	    IF @pagecounter = 0
    	INSERT @textfile SELECT @rundate,@Check,'No Check Details Exist For '+@Check
	   
	   



	 
	RETURN
END

Open in new window


Attached also is the SQL Table data.

Thank you again and again for all your help.
SQLTableData.xlsx
1020133309_04132018.txt
F723583752_04132018.txt
0
 
Kyle AbrahamsSenior .Net DeveloperCommented:
I'm seeing ------------------------ Page 2 ------------------------ in the 1020133309_04132018.txt

What's not working?
0
 
Queennie LAuthor Commented:
@Kyle Abrahams:

Page 1 is on Row 47
Page 2 is on Row 94
Page 3 is on Row 141
Page 4 is on Row 141 plus 47

I don't know if this is possible.

Thank you again. I really appreciate with your help.
0
 
Kyle AbrahamsSenior .Net DeveloperCommented:
use this instead:
if @lineCounter % @Pagebreak = 0 
		begin
		   -- print page header again
		   set @newPage =  1
                   
           --**print the page number
		   INSERT @textfile SELECT @rundate,@Check,'------------------------ Page '+CONVERT(VARCHAR(3),@pagecounter)+' ------------------------' 
   	    End
  

Open in new window

 

Essentially you're checking if the line number is divisible by 47.

Note that the following is also off:
IF @pagecounter%47=1  -- should be 0, but should also be using line number?
		BEGIN
		INSERT @textfile SELECT @rundate,@Check,'------------------------ Page '+CONVERT(VARCHAR(3),@pagecounter)+' ------------------------' 
   	    End

Open in new window

0
 
Queennie LAuthor Commented:
@Kyle:

I will test it and will let you know.

Thank you again.
0
 
Mark WillsTopic AdvisorCommented:
@Queennie L

This requirement is quite different from the previous function. Previously we were basically going from horizontal row information into vertiical page layout. Bit like the page header section and the reason why @uniqueid is so important.

This time, you have control break logic to cater for, and the linecounter will be useless while the number of inserted rows does not match the selection criteria (ie you insert more rows than is being counted).

So, have to take a slightly different approach. Need to insert a line at a time for this type of format / layout and need to use the params gained from the cursor to select the rows. You have some detail rows being grouped by different criteria and that will throw out the detail lines / line counter.

And you are missing some information like number of pages, pagenumber (in header), and the alignment could do with some tidy up.

It does need a rewrite in my opinion.

What information must appear at the top of each page ?

Do you want me to rewrite it for you ?
0
 
Queennie LAuthor Commented:
@Mark Wills:

Please do. I really need help.

Thank you again.
0
 
Queennie LAuthor Commented:
The first page header will be all the headers information and the second page header and the last pages will be just the bank information. I also want the count of total pages in first page. Thank you very much.
0
 
Mark WillsTopic AdvisorCommented:
OK, will see what I can do....
0
 
Queennie LAuthor Commented:
@Mark Wills:
Thank you again.
0
 
Queennie LAuthor Commented:
Thank you Mark for your hard work and your solution is the best to what I need. I appreciate you from the bottom of my heart.

Also, thank you for all that participated to make this project a success.

I appreciate all of you.

Thank you again.
0
 
Mark WillsTopic AdvisorCommented:
It is my pleasure, so very happy that it appears to be working for you.

Your words are too kind and likewise greatly appreciated :)

Cheers,
Mark Wills
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.