This content has been marked as final. Show 4 replies
I have 2 tables: 1st with companies (CID, manager, type) of 2 types distributed between managers and 2nd table with insurance contracts (InsID, CID, BegDate, EndDate) of 2 types. A type 1 company must have a valid type 1 contract. A type 2 company must have both type 1 and 2 valid contracts. I need to calculate the number of companies for each manager, that violate the rule on a given date (i.e IDs of companies that don't have active type 1 contract and IDs of type 2 companies that don't have active type 2 contract). But I needn't count a company twice if it is of type 2 and have neither type 1 contract nor type 2. In any other db I would make 2 separate queries, UNION them and then COUNT DISTINCT the result GROUP BY manager. But here... I've already made a separate expression for each query, but I need to join them. Can I make a union in expression? Maybe I can unite 2 set analysis operations in a single expression? Thanks in advance!