我遇到了一个问题，我写了一个查询，显示了一个活跃的计数id的所有客户每月为过去12个月。问题是，此计数在所有客户机中都显示相同的结果，因此当将结果移到我的数据可视化时，我希望能够按公司筛选并仅显示公司的结果，而不是将所有结果一起显示相同的结果。

SELECT DISTINCT
IF OBJECT_ID('tempdb.#TESTDATA21234') IS NOT NULL DROP TABLE #TESTDATA212
SELECT DISTINCT
    C.ClientGroup,
    C.Client,
    C.AlternateCaseID,
    C.CaseOpenDate,
    C.CaseCloseDate,
    BR.Prod
    INTO #TESTDATA21234
FROM
    TEST1 C
    LEFT JOIN TEST2 BR on BR.Id = C.id
    LEFT JOIN TEST3 CL on CL.Id = C.Id
WHERE
    Cl.EndDate > GETDATE()
    AND C.CaseOpenDate between '2019-07-01' and '2020-07-01'
    OR
    Cl.EndDate > GETDATE()
    AND C.CASECLOSEDATe between '2019-07-01' and '9999-12-31'
 SELECT DISTINCT   
    g.ClientGroup,
    (SELECT COUNT(*) from #Testdata21234 where '2019-07-01' BETWEEN dateadd(month, datediff(MONTH, 0, #testdata21234.CaseOpenDate), 0) and dateadd(month, datediff(MONTH, 0, #testdata21234.CaseCloseDate), 0) ) as 'July19',
    (SELECT COUNT(*) from #Testdata21234 where '2019-08-01' BETWEEN dateadd(month, datediff(MONTH, 0, #testdata21234.CaseOpenDate), 0) and dateadd(month, datediff(MONTH, 0, #testdata21234.CaseCloseDate), 0) ) as 'August19',
    (SELECT COUNT(*) from #Testdata21234 where '2019-09-01' BETWEEN dateadd(month, datediff(MONTH, 0, #testdata21234.CaseOpenDate), 0) and dateadd(month, datediff(MONTH, 0, #testdata21234.CaseCloseDate), 0) ) as 'September19',
    (SELECT COUNT(*) from #Testdata21234 where '2019-10-01' BETWEEN dateadd(month, datediff(MONTH, 0, #testdata21234.CaseOpenDate), 0) and dateadd(month, datediff(MONTH, 0, #testdata21234.CaseCloseDate), 0) ) as 'October19',
    (SELECT COUNT(*) from #Testdata21234 where '2019-11-01' BETWEEN dateadd(month, datediff(MONTH, 0, #testdata21234.CaseOpenDate), 0) and dateadd(month, datediff(MONTH, 0, #testdata21234.CaseCloseDate), 0) ) as 'November19',
    (SELECT COUNT(*) from #Testdata21234 where '2019-12-01' BETWEEN dateadd(month, datediff(MONTH, 0, #testdata21234.CaseOpenDate), 0) and dateadd(month, datediff(MONTH, 0, #testdata21234.CaseCloseDate), 0) ) as 'December19',
    (SELECT COUNT(*) from #Testdata21234 where '2020-01-01' BETWEEN dateadd(month, datediff(MONTH, 0, #testdata21234.CaseOpenDate), 0) and dateadd(month, datediff(MONTH, 0, #testdata21234.CaseCloseDate), 0) ) as 'January20',
    (SELECT COUNT(*) from #Testdata21234 where '2020-02-01' BETWEEN dateadd(month, datediff(MONTH, 0, #testdata21234.CaseOpenDate), 0) and dateadd(month, datediff(MONTH, 0, #testdata21234.CaseCloseDate), 0) ) as 'February20',
    (SELECT COUNT(*) from #Testdata21234 where '2020-03-01' BETWEEN dateadd(month, datediff(MONTH, 0, #testdata21234.CaseOpenDate), 0) and dateadd(month, datediff(MONTH, 0, #testdata21234.CaseCloseDate), 0) ) as 'March20',
    (SELECT COUNT(*) from #Testdata21234 where '2020-04-01' BETWEEN dateadd(month, datediff(MONTH, 0, #testdata21234.CaseOpenDate), 0) and dateadd(month, datediff(MONTH, 0, #testdata21234.CaseCloseDate), 0) ) as 'April20',
    (SELECT COUNT(*) from #Testdata21234 where '2020-05-01' BETWEEN dateadd(month, datediff(MONTH, 0, #testdata21234.CaseOpenDate), 0) and dateadd(month, datediff(MONTH, 0, #testdata21234.CaseCloseDate), 0) ) as 'May20',
    (SELECT COUNT(*) from #Testdata21234 where '2020-06-01' BETWEEN dateadd(month, datediff(MONTH, 0, #testdata21234.CaseOpenDate), 0) and dateadd(month, datediff(MONTH, 0, #testdata21234.CaseCloseDate), 0) ) as 'June20'
    from #testdata21234 g
        DROP TABLE #TESTDATA21234

下面是我得到的输出示例

ClientGroup July19  August19    September19 October19   November19  December19  January20   February20  March20 April20 May20   June20
Comp1   45597   46350   45954   47141   45524   45978   48027   46608   50039   49093   43437   44524
Comp2   45597   46350   45954   47141   45524   45978   48027   46608   50039   49093   43437   44524
Comp3   45597   46350   45954   47141   45524   45978   48027   46608   50039   49093   43437   44524
Comp4   45597   46350   45954   47141   45524   45978   48027   46608   50039   49093   43437   44524
Comp5   45597   46350   45954   47141   45524   45978   48027   46608   50039   49093   43437   44524

我尝试按分组，并为此筛选器添加某些方面以启用此功能，但只收到“子查询返回的值超过1”。当子查询后跟=，！=，<，<=，>，>=或者子查询用作表达式时。”
请让我知道，如果有人有办法得到这个理想的结果。

你不需要临时的table。但如果必须有条件聚合，只需使用条件聚合。从我从你的数据中可以看出，你希望一个月内至少有一天的案件重叠。如果这是正确的，那么：

SELECT g.ClientGroup,
       SUM(CASE WHEN g.CaseOpenDate < '2019-08-01' AND g.CaseCloseDate >= '2019-07-01'
                THEN 1 ELSE 0
           END) as July19,
       SUM(CASE WHEN g.CaseOpenDate < '2019-09-01' AND g.CaseCloseDate >= '2019-08-01'
                THEN 1 ELSE 0
           END) as August19,
       SUM(CASE WHEN g.CaseOpenDate < '2019-10-01' AND g.CaseCloseDate >= '2019-09-01'
                THEN 1 ELSE 0
           END) as September19,
       . . .
from #testdata21234 g
group by g.ClientGroup;

对于完全重叠或其他情况，很容易调整此逻辑。
除了更短更清晰之外，这个版本将比您的子查询版本快得多。
此外，您根本不需要临时表。你只需添加 group by 您的原始查询。

展开查看全部

2条答案

按热度按时间

7hiiyaii1#

我想问题出在最后一个问题上了。如果删除“distinct”并添加

group by g.ClientGroup

最后
在此之后，更改每个计数内的子查询。例如： (SELECT COUNT(*) from #Testdata21234 where '2019-07-01' BETWEEN dateadd(month, datediff(MONTH, 0, #testdata21234.CaseOpenDate), 0) and dateadd(month, datediff(MONTH, 0, #testdata21234.CaseCloseDate), 0) AND ClientGroup = q.ClientGroup ) as 'July19' 子查询中没有筛选器，因此它选择每个客户机

赞(0）回复(0）举报 2021-07-24

scyqe7ek2#

结果中按客户分组的日历计数，按月显示活动客户

2条答案

相关问题

热门标签

最新问答