Count specific values in rows

Question

I have the following table, I am trying to count the number of POC's under each Assessment for each client.

clientId    ProcDate        ProcDesc    
7180        2018-06-13      Assessment
7180        2018-06-13      POC 20
7180        2018-06-13      POC 4b
7180        2018-06-20      POC 20
7180        2018-06-20      POC 4b
7180        2018-06-27      POC 20
7180        2018-06-27      POC 4b
7180        2018-07-04      Assessment
7180        2018-07-04      POC 20
7180        2018-07-04      POC 4b
7180        2018-07-11      POC 20
7180        2018-07-18      POC 20
7180        2018-07-18      POC 4b
7180        2018-09-05      Assessment
7180        2018-09-05      POC 20
7180        2018-09-12      POC 20
7180        2018-09-12      POC 4b
7180        2018-09-19      POC 20
7180        2018-09-19      POC 4b

2584        2018-10-03      Assessment
2584        2018-10-03      POC 20
2584        2018-10-03      POC 4b
2584        2018-11-04      Assessment
2584        2018-11-04      POC 20
2584        2018-11-04      POC 4b
2584        2018-11-11      POC 20
2584        2018-11-18      POC 20
2584        2018-11-18      POC 4b
7585        2018-11-04      Assessment
7585        2018-11-04      POC 20
7585        2018-11-04      POC 4b
7585        2018-11-11      POC 20
7585        2018-11-18      POC 20
7585        2018-11-18      POC 4b
6581        2018-11-04      CommAssessment
6581        2018-11-04      POC 20
6581        2018-11-04      POC 4b
6581        2018-11-11      POC 20

I would like to get result as the below.

ClientId    AssessDate      Type            CountPOC
7180        2018-06-13      Assessment      6
7180        2018-07-04      Assessment      5
7180        2018-09-05      Assessment      5
2584        2018-10-03      Assessment      2
2584        2018-11-04      Assessment      5
7585        2018-11-04      Assessment      5
6581        2018-11-04      CommAssessment      3

I am unable to figure out how to count the number of POC's below each assessment.

SELECT ClientId, ProcDate, ProcDesc
FROM ProcJoins
WHERE ProcDesc] in ('Assessment','POC 20','POC 4b')  
GROUP BY ClientId, ProcDate, ProcDesc
ORDER BY ProcedureDate

Luis Cazares · Accepted Answer · 2019-05-16 13:34:35Z

1

Here are 2 options of getting what you need.

The first one assumes that each client will always start by an Assessment.

WITH CTE AS(
    SELECT *, 
        ROW_NUMBER() OVER( ORDER BY clientId, ProcDate) rn
    FROM ProcJoins
)
SELECT clientId, 
       ProcDate,
       ProcDesc,
       LEAD(rn, 1, t.totalProc) OVER(ORDER BY clientId, rn) - rn - 1
FROM CTE
CROSS JOIN( SELECT COUNT(*) + 1 AS totalProc FROM CTE) t
WHERE ProcDesc = 'Assessment'
ORDER BY clientId DESC, ProcDate;

The second one just queries using ranges.

WITH CTE AS(
    SELECT *, 
         LEAD(ProcDate, 1, '99990101') OVER(PARTITION BY clientId ORDER BY ProcDate) EndDate
    FROM ProcJoins
    WHERE ProcDesc = 'Assessment'
)
SELECT c.clientId, 
       c.ProcDate,
       c.ProcDesc,
       COUNT(*)
FROM CTE c
JOIN ProcJoins p ON p.ProcDate >= c.ProcDate
                AND p.ProcDate < c.EndDate
                AND p.clientId = c.clientId
WHERE p.ProcDesc <> 'Assessment'
GROUP BY c.clientId, 
       c.ProcDate,
       c.ProcDesc
ORDER BY clientId DESC, ProcDate;

Third option compatible with versions 2005, 2008 & 2008R2

WITH CTE AS(
    SELECT *, 
        ROW_NUMBER() OVER( PARTITION BY clientId ORDER BY ProcDate) rn
    FROM ProcJoins
    WHERE ProcDesc NOT LIKE 'POC%'
)
SELECT c.clientId, 
       c.ProcDate,
       c.ProcDesc,
       COUNT(*)
FROM CTE       c
LEFT JOIN CTE  n  ON c.ClientId = n.clientId 
                 AND c.rn = n.rn-1
JOIN ProcJoins p  ON c.ClientId = p.clientId 
                 AND c.ProcDate <= p.ProcDate 
                 AND ISNULL(n.ProcDate, '99991231') > p.ProcDate
WHERE p.ProcDesc LIKE 'POC%'
GROUP BY c.clientId, 
       c.ProcDate,
       c.ProcDesc
ORDER BY c.ProcDate;

edited May 16, 2019 at 13:34

answered May 15, 2019 at 21:00

Luis Cazares

3,60510 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

jk1844 Over a year ago

Hi I get this error: The Parallel Data Warehouse (PDW) features are not enabled. I think the LEAD function is not compatible, I am using SQL SERVER 2008 R2.

Luis Cazares Over a year ago

You should let people know that you're using a version that has almost reached the end of support. These solutions only work from 2012+ versions.

Luis Cazares Over a year ago

The third option is very similar to the second one in terms of logic, but using tools available in previous versions.

jk1844 Over a year ago

Luis Cazares, Thanks for the help, I would like to calculate the time duration in weeks between the first POC and last POC within each assessment group and display in a into a column in each row. Is this possible? thank you

Luis Cazares Over a year ago

You're already counting them, so you just need to get the time difference from the max and min dates. Depending on requirements, you might want to use DATEDIFF with weeks or with days divided by 7.

Zhorov · Accepted Answer · 2019-05-16 08:00:30Z

0

Another possible approach is to define groups and get the count:

Input:

CREATE TABLE #Data (
    ClientId int, 
    ProcDate date,
    ProcDesc varchar(10)
)
INSERT INTO #Data 
    (ClientId, ProcDate, ProcDesc)
VALUES
    (7180, '20180613',  'Assessment'),
    (7180, '20180613',  'POC 20'),
    (7180, '20180613',  'POC 4b'),
    (7180, '20180620',  'POC 20'),
    (7180, '20180620',  'POC 4b'),
    (7180, '20180627',  'POC 20'),
    (7180, '20180627',  'POC 4b'),
    (7180, '20180704',  'Assessment'),
    (7180, '20180704',  'POC 20'),
    (7180, '20180704',  'POC 4b'),
    (7180, '20180711',  'POC 20'),
    (7180, '20180718',  'POC 20'),
    (7180, '20180718',  'POC 4b'),
    (7180, '20180905',  'Assessment'),
    (7180, '20180905',  'POC 20'),
    (7180, '20180912',  'POC 20'),
    (7180, '20180912',  'POC 4b'),
    (7180, '20180919',  'POC 20'),
    (7180, '20180919',  'POC 4b'),
    (2584, '20181003',  'Assessment'),
    (2584, '20181003',  'POC 20'),
    (2584, '20181003',  'POC 4b'),
    (2584, '20181104',  'Assessment'),
    (2584, '20181104',  'POC 20'),
    (2584, '20181104',  'POC 4b'),
    (2584, '20181111',  'POC 20'),
    (2584, '20181118',  'POC 20'),
    (2584, '20181118',  'POC 4b'),
    (7585, '20181104',  'Assessment'),
    (7585, '20181104',  'POC 20'),
    (7585, '20181104',  'POC 4b'),
    (7585, '20181111',  'POC 20'),
    (7585, '20181118',  'POC 20'),
    (7585, '20181118',  'POC 4b'),
    (6581, '20181104',  'CommAssessment'),
    (6581, '20181104',  'POC 20'),
    (6581, '20181104',  'POC 4b'),
(6581, '20181111',  'POC 20')

Statement (if SUM (...) OVER (ORDER BY ...) is supported):

;WITH GroupsCTE AS (
    SELECT 
        ClientId, ProcDate, ProcDesc,
        SUM(CASE WHEN ProcDesc IN ('Assessment', 'CommAssessment') THEN 1 ELSE 0 END) 
        OVER (ORDER BY ClientId, ProcDate, CASE WHEN ProcDesc IN ('Assessment', 'CommAssessment') THEN 0 ELSE 1 END) AS Groups
    FROM #Data
), CountCTE AS (
    SELECT
       ClientId, ProcDate, ProcDesc,
       COUNT(*) OVER (PARTITION BY Groups) AS [Count]
    FROM GroupsCTE      
)
SELECT 
    ClientId, ProcDate, ProcDesc, [Count] - 1
FROM CountCTE
WHERE ProcDesc IN ('Assessment', 'CommAssessment')
ORDER BY ClientId, ProcDate

Statement (if SUM (...) OVER (ORDER BY ...) is not supported):

;WITH GroupsCTE AS (
    SELECT 
        d.ClientId, d.ProcDate, d.ProcDesc,
        c.[Group]
    FROM #Data d
    CROSS APPLY(
        SELECT
        SUM(CASE WHEN ProcDesc IN ('Assessment', 'CommAssessment') THEN 1 ELSE 0 END) AS [Group]
        FROM #Data
        WHERE (ClientId = d.ClientId) AND (ProcDate <= d.ProcDate)
    ) c
), CountCTE AS (
    SELECT ClientId, [Group], COUNT(*) - 1 AS [Count]
    FROM GroupsCTE
    GROUP BY ClientId, [Group]
)
SELECT 
    g.ClientId, g.ProcDate, g.ProcDesc,
    c.[Count]
FROM GroupsCTE g
CROSS APPLY (
    SELECT ClientId, [Group], COUNT(*) - 1 AS [Count]
    FROM GroupsCTE
    WHERE (ClientId = g.ClientId) AND ([Group] = g.[Group])
    GROUP BY ClientId, [Group]
) c
WHERE g.ProcDesc IN ('Assessment', 'CommAssessment')
ORDER BY g.ClientId, g.ProcDate, CASE WHEN g.ProcDesc IN ('Assessment', 'CommAssessment') THEN 0 ELSE 1 END, g.ProcDesc

Output:

ClientId    ProcDate    ProcDesc    (No column name)
2584        2018-10-03  Assessment  2
2584        2018-11-04  Assessment  5
6581        2018-11-04  CommAssessment  3
7180        2018-06-13  Assessment  6
7180        2018-07-04  Assessment  5
7180        2018-09-05  Assessment  5
7585        2018-11-04  Assessment  5

edited May 16, 2019 at 8:00

answered May 16, 2019 at 6:19

Zhorov

30.3k6 gold badges32 silver badges56 bronze badges

4 Comments

jk1844 Over a year ago

Hi I get this error: The Parallel Data Warehouse (PDW) features are not enabled. I am using SQL SERVER 2008 R2.

jk1844 Over a year ago

After reading on google, it looks like OVER ... ORDER BY is not supported for aggregates in SQL Server 2008 R2. Hence i get the error message, any alternative? thanks

Zhorov Over a year ago

@jk1844 I've updated the answer. Strange, but based on documentation, SUM OVER and 'COUNT OVER' shoudl be supported starting from SQL Server 2008.

jk1844 Over a year ago

this works well Many thanks. I also need to calculate the time duration in weeks between the first POCand last POC within each assessment into a duration column.

Xabi · Accepted Answer · 2019-05-16 10:38:21Z

0

Sample data:

declare @Test table
    (ClientId int, ProcDate date,ProcDesc varchar(20))
insert @Test values
(7180,'2018-06-13','Assessment'),
(7180,'2018-06-13','POC 20'),
(7180,'2018-06-13','POC 4b'),
(7180,'2018-06-20','POC 20'),
(7180,'2018-06-20','POC 4b'),
(7180,'2018-06-27','POC 20'),
(7180,'2018-06-27','POC 4b'),
(7180,'2018-07-04','Assessment'),
(7180,'2018-07-04','POC 20'),
(7180,'2018-07-04','POC 4b'),
(7180,'2018-07-11','POC 20'),
(7180,'2018-07-18','POC 20'),
(7180,'2018-07-18','POC 4b'),
(7180,'2018-09-05','Assessment'),
(7180,'2018-09-05','POC 20'),
(7180,'2018-09-12','POC 20'),
(7180,'2018-09-12','POC 4b'),
(7180,'2018-09-19','POC 20'),
(7180,'2018-09-19','POC 4b'),
(2584,'2018-10-03','Assessment'),
(2584,'2018-10-03','POC 20'),
(2584,'2018-10-03','POC 4b'),
(2584,'2018-11-04','Assessment'),
(2584,'2018-11-04','POC 20'),
(2584,'2018-11-04','POC 4b'),
(2584,'2018-11-11','POC 20'),
(2584,'2018-11-18','POC 20'),
(2584,'2018-11-18','POC 4b'),
(7585,'2018-11-04','Assessment'),
(7585,'2018-11-04','POC 20'),
(7585,'2018-11-04','POC 4b'),
(7585,'2018-11-11','POC 20'),
(7585,'2018-11-18','POC 20'),
(7585,'2018-11-18','POC 4b'),
(1585,'2018-11-04','CommAssessment'),
(1585,'2018-11-04','POC 20'),
(1585,'2018-11-04','POC 4b'),
(1585,'2018-11-11','POC 20'),
(1585,'2018-11-18','POC 20'),
(1585,'2018-11-18','POC 4b')

Proposed "old school" solution:
EDITED: I had forgotten the "ClientId"
Fiddle

select ClientId, AssessDate = ProcDate, [Type] = ProcDesc, CountPOC = (
        select count(*)
        from @Test
        where ProcDesc like 'POC%' --- <> 'Assessment'
            and ClientId = t.ClientId
            and ProcDate >= t.ProcDate
            and ProcDate < isNull((
                    select top 1 ProcDate
                    from @Test
                    where ClientId = t.ClientId
                        and ProcDate > t.ProcDate
                        and ProcDesc not like 'POC%' -- = 'Assessment'
                    order by ProcDate
                    ), getdate())
        )
from @Test t
where t.ProcDesc not like 'POC%' -- = 'Assessment'

edited May 16, 2019 at 10:38

answered May 15, 2019 at 21:09

Xabi

5135 silver badges8 bronze badges

3 Comments

jk1844 Over a year ago

Hi, there are other clients with same assessment dates, and therefore the count is coming out incorrectly. I have added extra rows to your insert statement to show the counts. Can you please adjust your query to resolve this.

jk1844 Over a year ago

Hi i have another ProcType CommAssessment, can this be factored in as well? I have edited my original post. thanks.

Xabi Over a year ago

Now you can add new "ProcType", while countable descriptions start with "POC"

Collectives™ on Stack Overflow

Count specific values in rows

3 Answers 3

5 Comments

4 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

4 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related