RE: Identifying unique sets of combinations

[Dennis <iseries@xxxxxxxxxxxx>]

Hi, Richard:

Finally got back to this - other priorities and all that.

Everything looked to be wonderful until I got to the catcheck CTE, which includes columns that are not in - and, as far as I can see, cannot be in - the catcomp CTE on which it is based.  This is that point of complexity I'd mentioned before that makes my head swim.

I do appreciate your efforts on my behalf!  But I think I'll just opt for the familiar array-type process.  A shame since I think the SQL solution would have been a nice trophy.  :)
--  
Sent from my Galaxy tablet phone with with K-9 Mail. Please excuse my brevity.

"Richard Casey" <casey_r@xxxxxxxxxxxxxxxx> wrote:

> Dennis,
>
> 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),
> 	catcheck as (
> 		select cat1, cat2, a.code code1, b.code code2 
> 		from catcomp 
> 		join codefile a on a.category=cat1
> 		left outer join codefile b on b.category=cat2 and
> b.code=a.code)                
> select distinct cat1,cat2 
> from catcheck a 
> where not exists ( 
> 	select * 
> 	from catcheck b 
> 	where b.cat1=a.cat1 and b.cat2=a.cat2 and b.code2 is null) 
>
> Each CTE builds on the previous one. "catcount" is a list of categories
> with
> the count of the number of codes assigned to them. "catcomp" then pulls
> the
> category pairs that have the same count. That lets us ignore the
> category
> pairs that can't be duplicates. "catcheck" generates records for each
> code
> in the first category of the category pair and includes the matching
> code
> from the second category of the category pair. The left outer join
> means
> that the matching code will be null if the second category doesn't have
> that
> code. The actual query pulls from catcheck for any category that
> doesn't
> have any nulls for that matching code.
>
> This query will report each category pair twice; since if A=B, then
> B=A.
>
> Have fun!
>
> Richard
>
>
>
> -----Original Message-----
> From: midrange-l-bounces@xxxxxxxxxxxx
> [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Dennis
> Sent: Friday, June 03, 2011 12:40 PM
> To: midrange-l@xxxxxxxxxxxx
> Subject: Identifying unique sets of combinations
>
> 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.
>
> Thanks!
> --
> Sent from my Galaxy tablet phone with with K-9 Mail. Please excuse my
> brevity.
>
> -- 
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
> list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.