August 5, 2011 at 6:43 am
Hi guys, This is an SQL2000 question so no RANK() function or INTERSECT solutions please.
I am suffering Friday Brain fade.
I have two tables.
Table 1 contains three fields: Staff_seq_number, Staff_ID, Staff_Team
Table 2 contains two fields: Staff_ID, Staff_team.
Staff_ID in table 2 will always exist in table 1.
There may be more than one record with the same staff_ID in table 1
There may be 0 or 1 records in table 1 that match staff_ID and Staff_team in table 2.
Currently the join criteria is simply on Staff_ID. The dataset gets aggregated (count) on Staff_Seq_number so in some cases I am double counding the Table 2 records because I find more than one match in Table 1.
The join criteria needs to be :
If you find a match on both Staff_ID and Staff_Team then use that record
If not then take the first record in table 1 that matches on Staff_ID.
It would be easy in SQL2005 because I would simply rank the records on a join and only take the top ranked result for each table2 record but I can't work it out in SQL 2000.
I have tried setting the criteria as a derived table and a co-related sub query and can't get either to return consistent results. I am sure it's possible, but my brains is fried.
August 5, 2011 at 7:01 am
It's probably easier to change your aggregate to a distinct count.
Count(DISTINCT Staff_Seq_Num)
You may also need to use another field if that doesn't give you the correct counts.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
August 5, 2011 at 7:10 am
Sorry, wasn't clear enough in the original post/
The aggregate is actually on another field (Table2.contact_ID) but the grouping is by Staff_seq_num
so I would still be getting duplicate records.
August 5, 2011 at 7:13 am
It's very eay in sql2000 and I will be more than happy to help you write a query to do this if you will provide a ddl and sample data insert script for setting up the case.
Please refer to link in my signature to find out how to do so.
Also, if you will specify exactly what you want to see in resultset that will help too.
August 5, 2011 at 8:41 am
Thanks for the offer Eugene,
I will try to do that on Monday (almost day end here now...)
I will need to strip the current query down to the offending tables and some sample data.
for the time being, I am creating a derived table which is the min(Staff_seq_num) and Staff_ID and then using that to link back the full table. It's not right because activity get allocated to the wrong team, but at least it is not being duplicated.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply