sql-server - 获取内部案例的分析函数的计数

Question

我有一个返回类似记录的查询。

Name   Total_Case_Count   User_Case_Count   P_Count  Rej_Count  PPP_Count    Active_Count

 XYZ        20                  10            05           02       01        02

我为此使用以下查询。

     select row_number() over (order by result.USER_NAME asc) as row_index,
           row_number() over (order by result.USER_NAME asc) as SERIAL_NO,
           result.USER_NAME, 
           result.USER_ACCOUNT_ID,
           MAX(Total_Case_Count) AS Total_Case_Count,
           MAX(User_Case_COUNT) AS User_Case_COUNT,
           MAX(Pending_Case_Count) AS Pending_Case_Count,
           MAX(Rejected_Case_Count) AS Rejected_Case_Count,
           MAX(Pending_For_Payment_Case_Count) AS Pending_For_Payment_Case_Count,
           MAX(Active_Case_Count) AS Active_Case_Count

    FROM
    (  SELECT               
              UA.USER_ACCOUNT_ID,
              UA.FIRST_NAME AS USER_NAME,
              NVL(PUIA.PARENT_USER_ACCOUNT_ID,C.CREATED_BY) PID,
              COUNT(*) OVER () Total_Case_Count,
              COUNT(*) OVER (PARTITION BY UA.USER_ACCOUNT_ID) User_Case_COUNT,

               CASE 
                        WHEN C.CASE_STATUS_ID = 2 THEN COUNT(*) OVER (PARTITION BY C.CASE_STATUS_ID,UA.USER_ACCOUNT_ID) end as Pending_Case_Count,
               CASE 
                        WHEN C.CASE_STATUS_ID = 4 THEN COUNT(*) OVER (PARTITION BY C.CASE_STATUS_ID,UA.USER_ACCOUNT_ID) end as Rejected_Case_Count,

               CASE 
                        WHEN C.CASE_STATUS_ID = 6 THEN COUNT(*) OVER (PARTITION BY C.CASE_STATUS_ID,UA.USER_ACCOUNT_ID) end as Pending_For_Payment_Case_Count,
               CASE 
                        WHEN C.CASE_STATUS_ID In (1,3,5,7,8,9) THEN  COUNT(*) OVER (PARTITION BY C.CASE_STATUS_ID,UA.USER_ACCOUNT_ID) end as Active_Case_Count

      FROM CASE C 
            INNER JOIN CASE_STATUS CS ON CS.CASE_STATUS_ID = C.CASE_STATUS_ID
            INNER JOIN SSO.PARENT_USER_IN_APPLICATION PUIA ON PUIA.APPLICATION_ID=12 AND PUIA.USER_ACCOUNT_ID=c.created_by
            INNER JOIN SSO.USER_ACCOUNTS UA ON UA.USER_ACCOUNT_ID=C.CREATED_BY
            INNER JOIN CASE_PARTY CP ON cp.sso_user_id=nvl(PUIA.PARENT_USER_ACCOUNT_ID,PUIA.USER_ACCOUNT_ID)
            inner join sso.User_In_Types uit on uit.USER_ACCOUNT_ID = UA.USER_ACCOUNT_ID 
            inner join SSO.USER_TYPES  ut on UT.USER_TYPE_ID = UiT.USER_TYPE_ID AND UT.APPLICATION_ID=12

      where 
            UT.APPLICATION_ID = 12 and UT.USER_TYPE_ID = 2170 
            and UA.USER_ACCOUNT_ID = 2187150 
            and c.case_source not in (4)

    ) result 
            GROUP BY result.USER_NAME, result.USER_ACCOUNT_ID
            ORDER BY USER_NAME

请查看 Active_Case_Count 列。它不会在案例中带来正在传递(1,3,5,6,7,8,9)的状态计数。它只返回任何单个案例状态的计数。

实际上这个语句并没有返回所有状态的计数

CASE 
    WHEN C.CASE_STATUS_ID In (1,3,5,7,8,9) THEN  COUNT(*) OVER (PARTITION BY C.CASE_STATUS_ID,UA.USER_ACCOUNT_ID) end as Active_Case_Count

任何建议真的很感激。

Answer 1

尝试使用:

CASE 
     WHEN C.CASE_STATUS_ID In (1,3,5,7,8,9) THEN 1 ELSE 0 end as Active_Case_Count

代替:

CASE 
     WHEN C.CASE_STATUS_ID In (1,3,5,7,8,9) THEN  COUNT(*) OVER (PARTITION BY C.CASE_STATUS_ID,UA.USER_ACCOUNT_ID) end as Active_Case_Count

和

SUM(Active_Case_Count) AS Active_Case_Count

相反:

MAX(Active_Case_Count) AS Active_Case_Count

这将计算您处于状态 (1,3,5,7,8,9) 的记录总数