Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
Thanks, I seem to figured out, how to apply it to my solution. Previously I had errors with setting brackets...
Count({($<Type={2}>-<BegDate={"<=$(TargetDate)"},EndDate={">=$(TargetDate)"},Itype={2}>)+($-<BegDate={"<=$(TargetDate)"},EndDate={">=$(TargetDate)"},Itype={1}>)}Distinct CID)
Perhaps if you post your expressions we can see what you're trying to do and help find an answer. Posting an example document that demonstrates the problem would be even better.
You can check Set Operators Union. Two set of selections can be union operated.
Sure. That's the example of what I have for now...
Thanks, I seem to figured out, how to apply it to my solution. Previously I had errors with setting brackets...
Count({($<Type={2}>-<BegDate={"<=$(TargetDate)"},EndDate={">=$(TargetDate)"},Itype={2}>)+($-<BegDate={"<=$(TargetDate)"},EndDate={">=$(TargetDate)"},Itype={1}>)}Distinct CID)