This is a follow on request to yesterday's post:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:319832.
To review I have two tables something like this.
TableA
year use_no county rec_type
2009 1 1 X
2009 3 1 X
2009 4 1 Y
2009 5 2 X
2009 6 2 X
2009 7 2 Y
2009 10 2 Y
TableB
year use_no dup_set
2009 1 A
2009 2 A
2009 7 B
2009 8 B
2009 9 B
The year and use_no fields combine to be the unique key for both tables
and records that share the same key in each table are related to each
other. TableB records sets of records that are considered duplicates
and all the records of a set are related by sharing the same dup_set key
value.
The goal of my task is to create report that counts the number of
records in TableA grouped by county and rec_type and counts the number
of records if all the duplicate records recorded in TableB are taken
into account. So using the above simplified data, the report would
produce results something like this.
county rec_type records records_with_dups
1 X 2 3
1 Y 1 1
2 X 2 2
2 Y 2 4
With some tinkering I have gotten part of the way there. The following
set of table joins will produce a record set of records that are
duplicated and how many duplicates their are.
SELECT
p.year,
p.use_no,
count(*) AS duplicates
FROM
tableA p INNER JOIN
tableB e ON (p.use_no = e.use_no AND p.year = e.year and
e.duplicate_set is not null) INNER JOIN
tableB d ON (e.duplicate_set = d.duplicate_set AND e.year = d.year)
GROUP BY
p.year,
p.use_no
This returns results something like:
year use_no duplicates
2009 1 2
2009 7 3
Now if I can just combine this back into the rest of the tableA data so
I can get something that looks like this, I would be good to go.
year use_no duplicates
2009 1 2
2009 3 1
2009 4 1
2009 5 1
2009 6 1
2009 7 3
2009 10 1
I tried a UNION but I got this result. Note that records from the
duplicate set are duplicated.
year use_no duplicates
2009 1 2
2009 1 1
2009 3 1
2009 4 1
2009 5 1
2009 6 1
2009 7 3
2009 7 1
2009 10 1
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to
date
Get the Free Trial
http://ad.doubleclick.net/clk;207172674;29440083;f
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:319879
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
