Export to text files based on a value and if employee has multiple reasons show all reasons to that particular employee.

I have a SQL Functions that export from SQL table to text files based on a value that works perfectly. BIG THANKS to MARK WILLIS for his expertise. I really appreciate his intelligence and help.

My apology I was not clear when I first asked this question.

My problem is when an employee has multiple reasons, I only want to show all reasons only to that particular employee. I don't want it to go to the next page if reasons belong to the same employee. Only a different employee goes to the next page in a text file.

This is the SQL Functions:
ALTER FUNCTION [dbo].[udf_export_employee](@filename 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 @Employee_UniqueID varchar(50)
	DECLARE @Employee_Filename varchar(50)
	DECLARE filename_cursor CURSOR FOR SELECT UniqueID,[Filename] from Employee where [Filename] = @filename

	OPEN filename_cursor
	FETCH NEXT FROM filename_cursor INTO @Employee_UniqueID,@Employee_Filename
	WHILE @@FETCH_STATUS = 0
	BEGIN

		SET @pagecounter = @pagecounter + 1

		IF @pagecounter = 1
		BEGIN
			INSERT @textfile SELECT @rundate,@filename,'------------------------ '+ConstantVariable+' ------------------------' 
                              FROM ConstantVariable1 where UniqueID = @pagecounter
   			INSERT @textfile SELECT @rundate,@filename,'                                                       '
   			INSERT @textfile SELECT @rundate,@filename,'                                                       '
   			INSERT @textfile SELECT @rundate,@filename,'                                                       '
		END
		ELSE
		BEGIN
			INSERT @textfile SELECT @rundate,@filename,'------------------------ Page '+convert(varchar(3),@pagecounter)+' ------------------------' 
   			INSERT @textfile SELECT @rundate,@filename,'                                                       '
   			INSERT @textfile SELECT @rundate,@filename,'                                                       '
   			INSERT @textfile SELECT @rundate,@filename,'                                                       '
		END

		INSERT @textfile SELECT @rundate,@filename,'EMPLOYEE NAME: ' + EmployeeName from Employee where UniqueID = @Employee_UniqueID and [Filename] = @Employee_Filename
		INSERT @textfile SELECT @rundate,@filename,'EMPLOYEE ADDRESS: ' + EmployeeAddress from Employee where UniqueID = @Employee_UniqueID and [Filename] = @Employee_Filename
		INSERT @textfile SELECT @rundate,@filename,'EMPLOYEE CITY: ' + EmployeeCity from Employee where UniqueID = @Employee_UniqueID and [Filename] = @Employee_Filename
		INSERT @textfile SELECT @rundate,@filename,'EMPLOYEE ZIP: ' + EmployeeZip from Employee where UniqueID = @Employee_UniqueID and [Filename] = @Employee_Filename
    	INSERT @textfile SELECT @rundate,@filename,'                                                       '
		INSERT @textfile SELECT @rundate,@filename,'EMPLOYEE EMPLOYMENT DATE: ' + EmploymentDate from Employee where UniqueID = @Employee_UniqueID and [Filename] = @Employee_Filename
    	INSERT @textfile SELECT @rundate,@filename,'                                                       '
		INSERT @textfile SELECT @rundate,@filename,'EMPLOYER NAME: ' + EmployerName from Employee where UniqueID = @Employee_UniqueID and [Filename] = @Employee_Filename
		INSERT @textfile SELECT @rundate,@filename,'EMPLOYER ADDRESS: ' + EmployerAddress from Employee where UniqueID = @Employee_UniqueID and [Filename] = @Employee_Filename
		INSERT @textfile SELECT @rundate,@filename,'EMPLOYER CITY: ' + EmployerCity from Employee where UniqueID = @Employee_UniqueID and [Filename] = @Employee_Filename
		INSERT @textfile SELECT @rundate,@filename,'EMPLOYER ZIP: ' + EmployerZip from Employee where UniqueID = @Employee_UniqueID and [Filename] = @Employee_Filename
    	INSERT @textfile SELECT @rundate,@filename,'                                                       '
		INSERT @textfile SELECT @rundate,@filename,'REASONS ' + 'DESCRIPTIONS ' + 'AMOUNT ' from Employee where UniqueID = @Employee_UniqueID and [Filename] = @Employee_Filename
		--THIS IS WHERE THE REASONS, DESCRIPTION, AND AMOUNT GOES PER EMPLOYEE ---INSERT @textfile SELECT @rundate,@filename,Reasons from Employee where UniqueID = @Employee_UniqueID and [Filename] = @Employee_Filename

		FETCH NEXT FROM filename_cursor INTO @Employee_UniqueID,@Employee_Filename

	END

	CLOSE filename_cursor
	DEALLOCATE filename_cursor

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

	RETURN
END

Open in new window


Is this possible? I tried to play around with the SQL query but I cannot get it to work. See attached.

Please help.

Thank you again for all your help.
SQLTables--3-.xlsx
File1_12272017.txt
File2_12272017.txt
File3_12272017.txt
Queennie LAsked:
Who is Participating?
 
Mark WillsTopic AdvisorCommented:
OK, then I think (this time), it will work the way you need it...

The underlying assumption is all details will be the same for a given [Filename],[EmployeeName],[EmployerName] combination
ALTER FUNCTION [dbo].[udf_export_employee](@filename 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 @Employee_UniqueID varchar(50)
	DECLARE @Employee_Filename varchar(50)
	DECLARE @Employee varchar(50)
	DECLARE @Employer varchar(50)
	DECLARE filename_cursor CURSOR FOR SELECT MAX(UniqueID) as UniqueID,[Filename],[EmployeeName],[EmployerName]
									   FROM Employee where [Filename] = @filename group by [Filename],[EmployeeName],[EmployerName]

	OPEN filename_cursor
	FETCH NEXT FROM filename_cursor INTO @Employee_UniqueID,@Employee_Filename,@Employee,@Employer
	WHILE @@FETCH_STATUS = 0
	BEGIN

		SET @pagecounter = @pagecounter + 1

		IF @pagecounter = 1
		BEGIN
			INSERT @textfile SELECT @rundate,@filename,'------------------------ '+ConstantVariable+' ------------------------' 
							 FROM ConstantVariable where UniqueID = @pagecounter
   			INSERT @textfile SELECT @rundate,@filename,'                                                       '
   			INSERT @textfile SELECT @rundate,@filename,'                                                       '
   			INSERT @textfile SELECT @rundate,@filename,'                                                       '
		END
		ELSE
		BEGIN
			INSERT @textfile SELECT @rundate,@filename,'------------------------ Page '+convert(varchar(3),@pagecounter)+' ------------------------' 
   			INSERT @textfile SELECT @rundate,@filename,'                                                       '
   			INSERT @textfile SELECT @rundate,@filename,'                                                       '
   			INSERT @textfile SELECT @rundate,@filename,'                                                       '
		END


		INSERT @textfile SELECT @rundate,@filename,EmployeeName from Employee where UniqueID = @Employee_UniqueID and [Filename] = @Employee_Filename
		INSERT @textfile SELECT @rundate,@filename,EmployeeAddress from Employee where UniqueID = @Employee_UniqueID and [Filename] = @Employee_Filename
		INSERT @textfile SELECT @rundate,@filename,EmployeeCity from Employee where UniqueID = @Employee_UniqueID and [Filename] = @Employee_Filename
		INSERT @textfile SELECT @rundate,@filename,EmployeeZip from Employee where UniqueID = @Employee_UniqueID and [Filename] = @Employee_Filename
		INSERT @textfile SELECT @rundate,@filename,'                                                       '
		INSERT @textfile SELECT @rundate,@filename,EmploymentDate from Employee where UniqueID = @Employee_UniqueID and [Filename] = @Employee_Filename
		INSERT @textfile SELECT @rundate,@filename,'                                                       '
		INSERT @textfile SELECT @rundate,@filename,EmployerName from Employee where UniqueID = @Employee_UniqueID and [Filename] = @Employee_Filename
		INSERT @textfile SELECT @rundate,@filename,EmployerAddress from Employee where UniqueID = @Employee_UniqueID and [Filename] = @Employee_Filename
		INSERT @textfile SELECT @rundate,@filename,EmployerCity from Employee where UniqueID = @Employee_UniqueID and [Filename] = @Employee_Filename
		INSERT @textfile SELECT @rundate,@filename,EmployerZip from Employee where UniqueID = @Employee_UniqueID and [Filename] = @Employee_Filename
		INSERT @textfile SELECT @rundate,@filename,'                                                       '
		INSERT @textfile SELECT @rundate,@filename,'Reasons         Description     Amount'
		INSERT @textfile SELECT @rundate,@filename,cast([Reasons] as char(16))+cast([Description] as char(16))+cast([Amount] as char(7)) 
						 FROM Employee where [Filename] = @Employee_Filename and EmployeeName = @Employee and EmployerName = @Employer 
		INSERT @textfile SELECT @rundate,@filename,'                                                       '

		FETCH NEXT FROM filename_cursor INTO @Employee_UniqueID,@Employee_Filename,@Employee,@Employer

	END

	CLOSE filename_cursor
	DEALLOCATE filename_cursor

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

	RETURN
END
GO

Open in new window

1
 
Mark WillsTopic AdvisorCommented:
Yes, of course it is possible :)

UniqueID is no longer usable as it was, so we need to rely on filename.

I'll have a closer look and get back to you.
0
 
Mark WillsTopic AdvisorCommented:
OK, see how this works for you
ALTER FUNCTION [dbo].[udf_export_employee](@filename 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 @Employee_UniqueID varchar(50)
	DECLARE @Employee_Filename varchar(50)
	DECLARE filename_cursor CURSOR FOR SELECT MAX(UniqueID) as UniqueID,[Filename] from Employee where [Filename] = @filename group by [Filename]

	OPEN filename_cursor
	FETCH NEXT FROM filename_cursor INTO @Employee_UniqueID,@Employee_Filename
	WHILE @@FETCH_STATUS = 0
	BEGIN

		SET @pagecounter = @pagecounter + 1

		IF @pagecounter = 1
		BEGIN
			INSERT @textfile SELECT @rundate,@filename,'------------------------ '+ConstantVariable+' ------------------------' 
                              FROM ConstantVariable where UniqueID = @pagecounter
			INSERT @textfile SELECT @rundate,@filename,'                                                       '
			INSERT @textfile SELECT @rundate,@filename,'                                                       '
			INSERT @textfile SELECT @rundate,@filename,'                                                       '
		END
		ELSE
		BEGIN
			INSERT @textfile SELECT @rundate,@filename,'------------------------ Page '+convert(varchar(3),@pagecounter)+' ------------------------' 
			INSERT @textfile SELECT @rundate,@filename,'                                                       '
			INSERT @textfile SELECT @rundate,@filename,'                                                       '
			INSERT @textfile SELECT @rundate,@filename,'                                                       '
		END


		INSERT @textfile SELECT @rundate,@filename,EmployeeName from Employee where UniqueID = @Employee_UniqueID and [Filename] = @Employee_Filename
		INSERT @textfile SELECT @rundate,@filename,EmployeeAddress from Employee where UniqueID = @Employee_UniqueID and [Filename] = @Employee_Filename
		INSERT @textfile SELECT @rundate,@filename,EmployeeCity from Employee where UniqueID = @Employee_UniqueID and [Filename] = @Employee_Filename
		INSERT @textfile SELECT @rundate,@filename,EmployeeZip from Employee where UniqueID = @Employee_UniqueID and [Filename] = @Employee_Filename
		INSERT @textfile SELECT @rundate,@filename,'                                                       '
		INSERT @textfile SELECT @rundate,@filename,EmploymentDate from Employee where UniqueID = @Employee_UniqueID and [Filename] = @Employee_Filename
		INSERT @textfile SELECT @rundate,@filename,'                                                       '
		INSERT @textfile SELECT @rundate,@filename,EmployerName from Employee where UniqueID = @Employee_UniqueID and [Filename] = @Employee_Filename
		INSERT @textfile SELECT @rundate,@filename,EmployerAddress from Employee where UniqueID = @Employee_UniqueID and [Filename] = @Employee_Filename
		INSERT @textfile SELECT @rundate,@filename,EmployerCity from Employee where UniqueID = @Employee_UniqueID and [Filename] = @Employee_Filename
		INSERT @textfile SELECT @rundate,@filename,EmployerZip from Employee where UniqueID = @Employee_UniqueID and [Filename] = @Employee_Filename
		INSERT @textfile SELECT @rundate,@filename,'                                                       '
		INSERT @textfile SELECT @rundate,@filename,'Reasons         Description     Amount'
		INSERT @textfile SELECT @rundate,@filename,cast([Reasons] as char(16))+cast([Description] as char(16))+cast([Amount] as char(7)) from Employee where [Filename] = @Employee_Filename

		FETCH NEXT FROM filename_cursor INTO @Employee_UniqueID,@Employee_Filename

	END

	CLOSE filename_cursor
	DEALLOCATE filename_cursor

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

	RETURN
END
GO

Open in new window

0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
Queennie LAuthor Commented:
@Mark:


I tested it but the File3 has 2 different employees and all reasons goes to EMPLOYEE NAME3_B. See attached.

Thank you again for all your help.
File1_02042018.txt
File2_02042018.txt
File3_02042018.txt
0
 
Mark WillsTopic AdvisorCommented:
*laughing* (sheepishly), I didnt even look at File3 - that's embarrassing.....

Just need to add EMPLOYEENAME to the control break logic.

Probably should also include EMPLOYERNAME as well in that control break logic.

How does that sound ?
0
 
Queennie LAuthor Commented:
@Mark:

I think that sounds good.

Thank you again for your help.
0
 
Queennie LAuthor Commented:
Thank you again for all your help Mark.

I really appreciate it.

Have a good day!
0
 
Queennie LAuthor Commented:
@Mark:

Thank you again for your intelligence and help. You are superb. I am a newbie to these things. I have been working for this sql function for 3 weeks.

Thank you and thank you again.

I really appreciate it from the bottom of my heart.
0
 
Mark WillsTopic AdvisorCommented:
An absolute pleasure to be able to help you.

And in being a 'newbie', if there is ever anything that I am doing, or any other Expert, then do not hesitate to ask for explanations.

We have all been newbies at some stage, so we do understand. However, we do sometimes assume too much , or gloss over detail (such as not looking at File3). That's when you need to speak up.

Your questions are always well written and you interact. Those two key attributes help us so very much.

So, a big Thank You to you too.

We are here to help :)

Cheers,
Mark Wills
1
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.