How to export only certain records
-
I have a table where the data for the employee and their dependants are in different rows. The only way to identify the dependants for each employee is to match their member numbers and a dependancy code field. I need to export data from this table to csv files - one for employee and one for dependant. These files goes to a vendor who can take only 49 records on each file for testing.
So, the problem is as follows:
I need to export all the dependants and only the dependants that have an associated employee in the employee export.
How do I accomplish this while keeping in mind the limitations of the size of the test file.
Thanks
KR
-
Sample data and expected output would be great!
N 56°04'39.16"
E 12°55'05.25" -
I second the call for sample data and expected output. Generally, if you can select the data with a query, you can export it with SSIS.
Greg
Greg
-
Here is a better explanation of my question:
Sample Data:
Columns: MemberNumber, FirstName, LastName, Address,RelCode
Data:
12345,John,Smith,123 Ave, 18 --- This is the employee record
12345,Sara,Smith,123 Ave, 01 --- This is the spouse record
12345, Paul,Smith, 123 Ave, 19 -- This is the child record
The relcode is interpreted this way: 18 - employee;01-spouse;19-child
The output scenario works like this:
If my output contains John Smith, then the dependant file should contain all his dependants, in this case both sara and Paul. There is a limitation on the test file: Both these files can contain a maximum of 49 records each.
For example sake, let us say I choose to export 15 employee records for the employee file, I need to be able to export all the dependant records for those 15, and only the dependant records for those 15, and I have to make sure that the dependant file does not contain more than 49 records.
Please let me know if this is still not clear
Thanks much
KR
-
you can also use bcp.exe with the queryout option, which would be my preferred method.
---------------------------------------
elsasoft.org -
Yes, now we know of which kind of dependancy they have. BUt how to know to which record John Smith is spouse of Jane Smith?
N 56°04'39.16"
E 12°55'05.25" -
Member number is the only connecting point - If they have the same member number, they are family.
Thanks
KR
-
Family yes, but employee?
You were originally talking about employees. How to tell which employer John Smith has?
N 56°04'39.16"
E 12°55'05.25" -
I am sorry if I was not clear, I am not concerned about the employer.
What I am looking for is to be able to export, say top 15 employees from my table and then keep track of the employees that I exported and then export all the dependant records for those 15 employees. I say 15, because of the limitation of 49 records in each export.
I almost think that I will have to export the top 15 membernumber into a table ( table variable) and using that to export out my employee and dependant files. I was just hoping that there was another way.
thanks
Karthika
-
Well, imagine you stood in a door, letting people and families into two rooms.
Only one in each family holds a ticket.
The ticketholders goes into one room, all other familymembers goes into the other room.
Both rooms can hold a maximum of 49 persons.
Each ticketholder allowed into the first room, must also have *all* of his/hers familymembers into the other room... How would one do?
You need to keep two counts - room 1 and room 2
Set one ticketholder on hold, count for room 1, and all the accompanying family into room 2, count 2.
If room1 < 50 and room 2 < 50, let them in.
Repeat for next
When either room is >= 50 while tallying the next party, they all have to wait and your work is done
..since the numbers are so small, I'd suggest you loop through the 'families' while keeping counts between each 'family'.
/Kenneth
-
Kenneth,
Sounds like a good approach! I will certainly try this logic
Thanks much for your help
KR
-
Another suggestion is two views: one lists only the employees and one lists only the dependents. In the employee view, also have a computed column for the number of dependents associated with the employee. This might make processing your "ticket holders" a little easier.
Tomm Carr
--
Version Normal Form -- http://groups.google.com/group/vrdbms -
Well, with SQL Server 2005, you can do this with ROWNUMBER() function.
N 56°04'39.16"
E 12°55'05.25" -
Play around with this query. It should get you started. It produces a list of at most 49 family members. All you now need to do now is delete the family members of an incomplete family from the list. Of course, if your list is already less than 49 than you need to do nothing. Using views can be useful here to avoid repeating the same code.
Needless to say, if you run into a family with more than 49 members things will get rough.
create table t(
familyid int,
firstname varchar(10),
lastname varchar(10),
address varchar(10),
memberid tinyint)
select top 49 *
from
(
select t.familyid,t.firstname,t.lastname,t.address
from t
join
(
select top 15 *
from allfamilies
where memberid=18
order by familyid
) f on t.familyid=f.familyid
) t
order by familyid
-
Thanks much for the start up query, and all the very helpful suggestions that I have gotten form all of you.
This is part of a bigger process, so when I have been able to get this done, I will be posting as to what I ultimately came up with.
KR
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply