此查询中的所有内容除第二个LEFT JOIN（BEGIN_DATE和END_DATE所在的位置）之外均有效。 因为我必须按照附加列进行分组，所以它们可以用于“加入”，我得到了错误的数字。 有没有办法做到这一点，而不必分组。 我希望这是有道理的。 基本上，因为我必须将BEGIN_DATE和END_DATE包括在组中，所有内容都会丢失。
SELECT to_char(T1.CALL_TIMESTAMP,'YYYY-IW') AS OMONTH ,COUNT(T1.HOUSE) AS NODECALLS ,T3.NODE_CODE ,T5.NODECUSTCOUNT ,T1.CALL_CATEGORY_LVL_3 ,sum((CASE WHEN T1.TC_WIP_TRANSACTION_ID IS NOT NULL THEN 1 ELSE 0 END )) AS TC ,sum((CASE WHEN T1.TC_WIP_TRANSACTION_ID IS NOT NULL THEN 1 ELSE 0 END ))/nullif(COUNT(T1.HOUSE), 0) AS SVRATEPERCALL ,COUNT(T1.HOUSE)/ nullif(T5.NODECUSTCOUNT, 0) AS CALLRATE FROM CVKOMNZP.NZKOMUSER.NFOV_INBD_REMEDY_CALL_DETAILS T1 LEFT JOIN ( SELECT T2.NODE_CODE,T2.BEGIN_DATE,T2.END_DATE,T2.HOUSE,T2.CORP FROM CVKOMNZP.NZKOMUSER.D_HOUSEHOLD_CH_HIST T2 ) T3 ON T1.CORP = T3.CORP AND T1.HOUSE = T3.HOUSE AND (T1.CALL_TIMESTAMP BETWEEN T3.BEGIN_DATE AND T3.END_DATE) LEFT JOIN ( SELECT count(ADM_HOUSEHOLD_ID) AS NODECUSTCOUNT,NODE_CODE,BEGIN_DATE, END_DATE FROM CVKOMNZP.NZKOMUSER.D_HOUSEHOLD_CH_HIST WHERE HOUSE_STATUS_CODE = 2 AND END_DATE = '2999-12-31 00:00:00' AND T1.CALL_TIMESTAMP BETWEEN BEGIN_DATE AND END_DATE GROUP BY NODE_CODE,BEGIN_DATE,END_DATE ) T5 ON T5.NODE_CODE = T3.NODE_CODE AND T1.CALL_TIMESTAMP BETWEEN T5.BEGIN_DATE AND T5.END_DATE WHERE T1.EXCLUSION_FLAG = 'N' AND T1.CALL_TIMESTAMP >= To_Date ('07-29-2017', 'MM-DD-YYYY' ) AND T1.CALL_TIMESTAMP <= To_Date ('07-31-2017', 'MM-DD-YYYY' ) GROUP BY to_char(T1.CALL_TIMESTAMP,'YYYY-IW') ,T3.NODE_CODE ,T5.NODECUSTCOUNT ,T1.CALL_CATEGORY_LVL_3Everything in this query works except for the second LEFT JOIN, where BEGIN_DATE and END_DATE are. Because I have to group by the additional columns, so they can be used in the "on join", I am getting false numbers. Is there any way to do this without having to group by. I hope this makes sense. Basically because I have to include BEGIN_DATE AND END_DATE in the group by, everything gets lost.
SELECT to_char(T1.CALL_TIMESTAMP,'YYYY-IW') AS OMONTH ,COUNT(T1.HOUSE) AS NODECALLS ,T3.NODE_CODE ,T5.NODECUSTCOUNT ,T1.CALL_CATEGORY_LVL_3 ,sum((CASE WHEN T1.TC_WIP_TRANSACTION_ID IS NOT NULL THEN 1 ELSE 0 END )) AS TC ,sum((CASE WHEN T1.TC_WIP_TRANSACTION_ID IS NOT NULL THEN 1 ELSE 0 END ))/nullif(COUNT(T1.HOUSE), 0) AS SVRATEPERCALL ,COUNT(T1.HOUSE)/ nullif(T5.NODECUSTCOUNT, 0) AS CALLRATE FROM CVKOMNZP.NZKOMUSER.NFOV_INBD_REMEDY_CALL_DETAILS T1 LEFT JOIN ( SELECT T2.NODE_CODE,T2.BEGIN_DATE,T2.END_DATE,T2.HOUSE,T2.CORP FROM CVKOMNZP.NZKOMUSER.D_HOUSEHOLD_CH_HIST T2 ) T3 ON T1.CORP = T3.CORP AND T1.HOUSE = T3.HOUSE AND (T1.CALL_TIMESTAMP BETWEEN T3.BEGIN_DATE AND T3.END_DATE) LEFT JOIN ( SELECT count(ADM_HOUSEHOLD_ID) AS NODECUSTCOUNT,NODE_CODE,BEGIN_DATE, END_DATE FROM CVKOMNZP.NZKOMUSER.D_HOUSEHOLD_CH_HIST WHERE HOUSE_STATUS_CODE = 2 AND END_DATE = '2999-12-31 00:00:00' AND T1.CALL_TIMESTAMP BETWEEN BEGIN_DATE AND END_DATE GROUP BY NODE_CODE,BEGIN_DATE,END_DATE ) T5 ON T5.NODE_CODE = T3.NODE_CODE AND T1.CALL_TIMESTAMP BETWEEN T5.BEGIN_DATE AND T5.END_DATE WHERE T1.EXCLUSION_FLAG = 'N' AND T1.CALL_TIMESTAMP >= To_Date ('07-29-2017', 'MM-DD-YYYY' ) AND T1.CALL_TIMESTAMP <= To_Date ('07-31-2017', 'MM-DD-YYYY' ) GROUP BY to_char(T1.CALL_TIMESTAMP,'YYYY-IW') ,T3.NODE_CODE ,T5.NODECUSTCOUNT ,T1.CALL_CATEGORY_LVL_3最满意答案
如果我理解此权限，则您希望在未通过BEGIN和END DATE分组的情况下获得COUNT。 然而，因为你的子查询（第二个左连接）需要包含BEGIN和NEED，所以你不知道如何在没有它的情况下进行分组。
如果是这种情况，你需要一个子查询来计算你的计数，并将它加回到同一个表中。
仅供参考：您的T1.CALL_TIMESTAMP在此子查询中没有意义，因为您没有称为T1的表。 我将它重命名为“a”。 随意将其改为您想要的。
看看这是否有道理
LEFT JOIN ( SELECT a.BEGIN_DATE, a.END_DATE, node.NODECUSTCOUNT, a.node_code FROM CVKOMNZP.NZKOMUSER.D_HOUSEHOLD_CH_HIST a /**Subquery to get a COUNT of all the Node based on NODE_CODE. You link this back to your query above using the NODE CODE**/ JOIN ( SELECT count(ADM_HOUSEHOLD_ID) AS NODECUSTCOUNT, NODE_CODE FROM CVKOMNZP.NZKOMUSER.D_HOUSEHOLD_CH_HIST GROUP BY NODE_CODE ) node on node.node_code = a.node_code WHERE a.HOUSE_STATUS_CODE = 2 AND a.END_DATE = '2999-12-31 00:00:00' AND a.CALL_TIMESTAMP BETWEEN BEGIN_DATE AND END_DATE ) ..JOIN THIS BACK TO YOUR MAIN TABLEIf I am understanding this right, you want to get a COUNT without grouping by BEGIN and END DATE. However, because your Subquery (2nd LEFT JOIN) needs to include the BEGIN and NEED, you do not know how to group without it.
If this is the case, you'll need a subquery for your count and JOIN it back to the same table.
FYI: Your T1.CALL_TIMESTAMP does not make sense in this subquery since you don't have a table called T1. I renamed it to "a". Feel free to change it to what you want.
See if this make sense
LEFT JOIN ( SELECT a.BEGIN_DATE, a.END_DATE, node.NODECUSTCOUNT, a.node_code FROM CVKOMNZP.NZKOMUSER.D_HOUSEHOLD_CH_HIST a /**Subquery to get a COUNT of all the Node based on NODE_CODE. You link this back to your query above using the NODE CODE**/ JOIN ( SELECT count(ADM_HOUSEHOLD_ID) AS NODECUSTCOUNT, NODE_CODE FROM CVKOMNZP.NZKOMUSER.D_HOUSEHOLD_CH_HIST GROUP BY NODE_CODE ) node on node.node_code = a.node_code WHERE a.HOUSE_STATUS_CODE = 2 AND a.END_DATE = '2999-12-31 00:00:00' AND a.CALL_TIMESTAMP BETWEEN BEGIN_DATE AND END_DATE ) ..JOIN THIS BACK TO YOUR MAIN TABLE更多推荐
发布评论