sql - Compare multiple columns: If identical value 1 otherwise 0 -
i have large dataset of firm pairs funding venture capitalist , want check whether 1 partner of pairs funded vc (if yes column ovc=1, otherwise 0), both partners funded @ least 1 vc (if yes column bvb=1) or whether both partners funded same vc (if yes column cvc=1). @ moment table has columns partner11 funding vcs vc11 vc17 , partner22 funding vcs in vc21 vc27. in order able illustrate headers in 1 line, left out vc14-vc17 , vc24-27 in table below.
partner11 | vc11 | vc12 | vc13 | partner22 | vc21 | vc22 | vc23 | ovb | bvb | cvc firma | | | | firmb | | | | 0 | 0 | 0 firmc | moa | | | firmd | | | | 1 | 0 | 0 firme | moa | fred | bob | firmf | | | | 1 | 0 | 0 firmg | | | | firmh | bob | zoe | jet | 1 | 0 | 0 firme | moa | | | firmf | jet | | | 0 | 1 | 0 firmg | moa | fred | bob | firmh | jet | zoe | | 0 | 1 | 0 firmg | moa | fred | bob | firmh | bob | zoe | jet | 0 | 0 | 1 firmg | jet | bob | fred | firmh | bob | zoe | jet | 0 | 0 | 1 if no partner venture-backed, ovb, bvb , cvc take value 0 (see row 1).
to insert 0 or 1 in ovb column, code should check whether 1 partner of pair has @ least 1 value in columns vc11-vc17 partner11 or (excl.) in columns vc21-vc27 partner22 (see row 2 4).
to insert 0 or 1 in bvb column, code should should check whether both partners have value @ least in 1 of vc columns (for partner11 in vc11-vc17, partner22 in vc21-vc27) (see row 5+6).
to insert 0 or 1 in cvc column, code should compare values of partners' vc columns each other, means vc11 should compared vc21, vc22, vc23, vc24, vc25, vc26 , vc27; vc12 vc21, vc22, vc23, vc24, vc25, vc26, vc27 , on (see row 7+8).
i appreciate solve problem.
thank in advance.
well, nastiest query have ever wrote. but, when table un-normalized, end with. let me know if helps in anyway:
run following update ovb , bvb , cvc (sql fiddle):
update mytable set ovb = case( case( (case datalength(vc11) when 0 0 else 1 end) + (case datalength(vc12) when 0 0 else 1 end) + (case datalength(vc13) when 0 0 else 1 end) + (case datalength(vc14) when 0 0 else 1 end) + (case datalength(vc15) when 0 0 else 1 end) + (case datalength(vc16) when 0 0 else 1 end) + (case datalength(vc17) when 0 0 else 1 end) ) when 0 0 else 1 end + case( (case datalength(vc21) when 0 0 else 1 end) + (case datalength(vc22) when 0 0 else 1 end) + (case datalength(vc23) when 0 0 else 1 end) + (case datalength(vc24) when 0 0 else 1 end) + (case datalength(vc25) when 0 0 else 1 end) + (case datalength(vc26) when 0 0 else 1 end) + (case datalength(vc27) when 0 0 else 1 end) ) when 0 0 else 1 end ) when 1 1 else 0 end , bvb = case( case( (case datalength(vc11) when 0 0 else 1 end) + (case datalength(vc12) when 0 0 else 1 end) + (case datalength(vc13) when 0 0 else 1 end) + (case datalength(vc14) when 0 0 else 1 end) + (case datalength(vc15) when 0 0 else 1 end) + (case datalength(vc16) when 0 0 else 1 end) + (case datalength(vc17) when 0 0 else 1 end) ) when 0 0 else 1 end + case( (case datalength(vc21) when 0 0 else 1 end) + (case datalength(vc22) when 0 0 else 1 end) + (case datalength(vc23) when 0 0 else 1 end) + (case datalength(vc24) when 0 0 else 1 end) + (case datalength(vc25) when 0 0 else 1 end) + (case datalength(vc26) when 0 0 else 1 end) + (case datalength(vc27) when 0 0 else 1 end) ) when 0 0 else 1 end ) when 2 1 else 0 end , cvc = case when case when (case datalength(vc11) when 0 0 else (case when vc11 = vc21 1 else 0 end) + (case when vc11 = vc22 1 else 0 end) + (case when vc11 = vc23 1 else 0 end) + (case when vc11 = vc24 1 else 0 end) + (case when vc11 = vc25 1 else 0 end) + (case when vc11 = vc26 1 else 0 end) + (case when vc11 = vc27 1 else 0 end) end) >= 1 1 else 0 end + case when (case datalength(vc12) when 0 0 else (case when vc12 = vc21 1 else 0 end) + (case when vc12 = vc22 1 else 0 end) + (case when vc12 = vc23 1 else 0 end) + (case when vc12 = vc24 1 else 0 end) + (case when vc12 = vc25 1 else 0 end) + (case when vc12 = vc26 1 else 0 end) + (case when vc12 = vc27 1 else 0 end) end) >= 1 1 else 0 end + case when (case datalength(vc13) when 0 0 else (case when vc13 = vc21 1 else 0 end) + (case when vc13 = vc22 1 else 0 end) + (case when vc13 = vc23 1 else 0 end) + (case when vc13 = vc24 1 else 0 end) + (case when vc13 = vc25 1 else 0 end) + (case when vc13 = vc26 1 else 0 end) + (case when vc13 = vc27 1 else 0 end) end) >= 1 1 else 0 end + case when (case datalength(vc14) when 0 0 else (case when vc14 = vc21 1 else 0 end) + (case when vc14 = vc22 1 else 0 end) + (case when vc14 = vc23 1 else 0 end) + (case when vc14 = vc24 1 else 0 end) + (case when vc14 = vc25 1 else 0 end) + (case when vc14 = vc26 1 else 0 end) + (case when vc14 = vc27 1 else 0 end) end) >= 1 1 else 0 end + case when (case datalength(vc15) when 0 0 else (case when vc15 = vc21 1 else 0 end) + (case when vc15 = vc22 1 else 0 end) + (case when vc15 = vc23 1 else 0 end) + (case when vc15 = vc24 1 else 0 end) + (case when vc15 = vc25 1 else 0 end) + (case when vc15 = vc26 1 else 0 end) + (case when vc15 = vc27 1 else 0 end) end) >= 1 1 else 0 end + case when (case datalength(vc16) when 0 0 else (case when vc16 = vc21 1 else 0 end) + (case when vc16 = vc22 1 else 0 end) + (case when vc16 = vc23 1 else 0 end) + (case when vc16 = vc24 1 else 0 end) + (case when vc16 = vc25 1 else 0 end) + (case when vc16 = vc26 1 else 0 end) + (case when vc16 = vc27 1 else 0 end) end) >= 1 1 else 0 end + case when (case datalength(vc17) when 0 0 else (case when vc17 = vc21 1 else 0 end) + (case when vc17 = vc22 1 else 0 end) + (case when vc17 = vc23 1 else 0 end) + (case when vc17 = vc24 1 else 0 end) + (case when vc17 = vc25 1 else 0 end) + (case when vc17 = vc26 1 else 0 end) + (case when vc17 = vc27 1 else 0 end) end) >= 1 1 else 0 end >= 1 1 else 0 end
Comments
Post a Comment