Calendar Count Grouped By Client Customer From Subquery in results showing active client by month

I am running into an issue with a query I wrote that displays an active count of ID’s for all clients per month for the previous 12 months. The issue is that this count displays the same through all clients so when moving the results over to my data visualization, I want to be able to filter by company and display results for the company only rather than all of them together showing the same results.

        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

Here is the example of the output I get

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

I have attempted to group by as well as add certain aspects to this filter to enable this feature but have only been greeted to – “Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.”

Please let me know if anyone has ideas how to get this desired result.

Answer

I guess the problem is in the last query. If you remove ‘distinct’ and add

group by g.ClientGroup

at the end

After this, change the subqueries inside every count. For example:

(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'

etc. There is no filter inside the subquery so it selects every client

Advertisement

Answer