Re: Identifying unique sets of combinations -- MIDRANGE-L

I had started with almost the identical approach as Richard, although intending also to identify generally when a "category lacks" just one element to have a complete match; included in the potential matches are those with count [number of elements in a set] one less than another set. Although I had started with CREATE VIEW, I changed each here in the code example below to be a CTE. The snippets may or may not be of any value or interest to you:

<code>

create table qtemp/md (ctg char(5), cod char(1))
;
insert into qtemp/md values('CCVAC','V'),('ALLAB','G')
,('ALLAB','V'),('ANYCD','V'),('SRVAC','G'),('SRVAC','V')
,('XXXXX','X'),('XXXXX','G'),('XXXXX','V')
;
drop function CtgCodes
;
create function CtgCodes
(InCtg varchar( 5)
) returns varchar(120)
language sql
set option dbgview=*SOURCE
begin
declare NoRows condition for sqlstate '02000' ;
declare err0100 char(1) default '0' ;
declare CodVal varchar( 5) ;
declare CodList varchar(120) default '' ;
declare GetCodes cursor for
select cod from qtemp/md where ctg = InCtg order by cod ;
declare continue handler for NoRows
begin
set err0100 = '1' ;
end ;

open GetCodes ;
while err0100<>'1' do
fetch GetCodes into CodVal ;
if err0100<>'1' then
set CodList = CodList concat ':' concat CodVal ;
end if ;
end while ;
close GetCodes ;
return CodList ;
end
;
with
mdcnt as /* category list plus counting elements in each */
(select ctg, count(*) as cnt
from qtemp/md group by ctg )
, mdpot as /* potential matching sets or superset by one elem */
(select a.ctg supctg, a.cnt supcnt, b.ctg subctg, b.cnt subcnt
from mdcnt a inner join mdcnt b
on ( a.cnt = b.cnt or a.cnt = b.cnt+1 )
and a.ctg <> b.ctg )
/* optional; change final select to use mdpot if omitted: */
, mdexc as /* exclude pair reversals for the matching sets */
(select dup.*
from mdpot dup exception join
( select max(supctg, subctg) supctg, subctg
from mdpot where supcnt=subcnt ) exc
on dup.supctg=exc.supctg and dup.subctg=exc.subctg )
/* category superset and subset [match] pairs and elem counts */
select /* supctg, subctg, supcnt, subcnt */
supctg concat case when supcnt=subcnt then ' matches '
else ' eclipses ' end concat
subctg concat case when supcnt=subcnt then ''
else ' by ' concat varchar(dec(supcnt-subcnt,3))
concat ' element(s)' end /* known:by 1 element */
, char(ctgcodes(supctg),10) concat ' vs ' concat
char(ctgcodes(subctg),10)
from mdexc /* or mdpot which keeps the duplicate pairs */ xxx
where subcnt =
( select count(sub.cod)
from qtemp/md sup /* superset */
left outer join
qtemp/md sub /* subset */
on sup.ctg <> sub.ctg and sup.cod = sub.cod
and sub.ctg = xxx.subctg
where sup.ctg = xxx.supctg )
order by /* supcnt-subcnt, */ supctg
;
/* report: */
String Expression String Expression
ALLAB eclipses CCVAC by 1 element(s) :G:V vs :V
ALLAB eclipses ANYCD by 1 element(s) :G:V vs :V
ALLAB matches SRVAC :G:V vs :G:V
ANYCD matches CCVAC :V vs :V
SRVAC eclipses CCVAC by 1 element(s) :G:V vs :V
SRVAC eclipses ANYCD by 1 element(s) :G:V vs :V
XXXXX eclipses SRVAC by 1 element(s) :G:V:X vs :G:V
XXXXX eclipses ALLAB by 1 element(s) :G:V:X vs :G:V
******** End of data ********
;
drop table qtemp/md
;

</code>

Regards, Chuck

On 03-Jun-2011 11:36 , Dennis wrote:

The category table is:
CREATE TABLE TAPCTGR (grer CHAR(3), GRCTG CHAR(5) GRATCD CHAR(5)
GRDSC CHAR(30) GRWGHT NUMERIC(2,1))

That is, Company, Category, Absence Code, Description, Weight.
(Weight will be 1 for a full day; .5 for a half day.)

Sample:

SELECT GRCTG, GRATCD, GRDSC, GRWGHT FROM TAPCTGR
WHERE GRATCD in ('G','V') ORDER BY GRCTG, GRATCD

ALLAB,G,CARRY-OVER VACATION,1.0
ALLAB,V,VACATION,1.0
ANYCD,V,VACATION,1.0
CCVAC,V,VACATION,1.0
SRVAC,G,CARRY-OVER VACATION,1.0
SRVAC,V,VACATION,1.0

(etc.)

Typos unintentional; copy/paste not possible here.

On 03-Jun-2011 11:08 , Richard Casey wrote:

How is your category/code info stored?

Just for grins (sanity optional!), I created a table (codefile)
with two fields: category and code.

The following query should identify category "pairs" that contain
the same set of codes.

with catcount as (
select category,count(*) count
from codefile
group by category),
catcomp as (
select a.category cat1, b.category cat2
from catcount a
join catcount b on a.count=b.count
where a.category<>b.category),
<<SNIP>>

Dennis on Friday, June 03, 2011 12:40 PM wrote:

We have a table as part of our HR system that is used to
categorize days off. For example, "S" (for sick) is in the
SCKAC, USEDS, VACCK and 14 other categories (I know, I know).
V(acation) is in 7 categories, and so on. These things are
categorized in such a way because they intersect so much. For
example, a Carry-over vacation day and a Vacation day each have
the same effect in terms of Vacation Balance, but very different
effects when it comes to calculating next year's new balance.

In all, there are 110 absence codes that are grouped into 55
different categories. In an effort to simplify all of this, I've
been asked to identify categories that share common members, with
no exceptions. (In other words, if categories AAA, ABB and ACC
all contain V and C, and none of them contain anything else, they
are exact duplicates.)

By the same token, If V and G are in 5 categories, but a sixth
category lacks G, the powers that be would like this noted also.

I'm sure there's a way to get to this answer and still maintain
what's left of my sanity (many of you realize that there's little
left), but I have not yet landed on it. I thought SQL might get
me to the answer most readily, but try as I might I haven't found
a workable solution yet; my CTEs quickly get too complex for me
to follow. (See prior note on sanity.) Pivot table doesn't seem
quite right (though it may help); counts are not definitive
enough...

So I thought I'd come to this group; see if you've had a similar
situation, and might be willing to share how you arrived at a
solution.