sql - MSSQL create group with multiple condition

Question

Scenario: I'm collecting a customer survey. Data collected at a TimePoint(Baseline, 24 Hours, 48 Hours, and 3-5 Days... etc). Table1 contains ID, demographic... etc. Table 2 contains ID, Collect Timepoints. When I do a left join, the output table has multiple Timepoint for one ID. Here is an example of the table :

ID |  HasCollected   |TimeCollect
---|-----------------|----------------
1  |  YES            |Baseline
1  |  NO             |24 Hours
1  |  NO             |48 Hours
2  |  YES            |Baseline
2  |  YES            |24 Hours
2  |  NO             |48 Hours
3  |  NO             |Baseline
3  |  YES            |24 Hours
3  |  NO             |48 Hours
4  |  NO             |Baseline
4  |  YES            |24 Hours
4  |  YES            |48 Hours

How do I know how many ID ONLY has Baseline collected? or ONLY has Baseline and 24 Hours collected.

    For example, ID = 1 collect only Baseline, so he will be in the Baseline group.
                 ID = 2 collected Baseline and 24 Hours. He will be in the BaselineAnd24Hours group but Baseline group.
                 ID = 3 collected 24 Hours, he is in 24Hours group ONLY.
                 ID = 4 collected 24 and 48 hours. He is in 24-48Hours group ONLY. (he is not in group 24 or 48 hours.)

I've tried with IIF :

  IIF(HasCollected= 'YES' and TimeCollect= 'Baseline' and TimeCollect = '24 Hours', 'G-Base24','NoGroup') as G-BaseAnd24,
  IIF(HasCollected= 'YES' and TimeCollect= 'Baseline', 'G-Base24','NoGroup') as G-Baseline,

or with Case

 case
        when (HasCollected = 'YES' and CollectTime = 'Baseline') then 'G-Baseline'
        when (HasCollected  = 'YES' and CollectTime  = '24 Hours') then 'G-24Hours'
        when (HasCollected  = 'YES' and CollectTime  = '48 Hours') then 'G-48Hours'
        when ((HasCollected = 'YES' and CollectTime  = 'Baseline') and (HasCollected  = 'YES' and CollectTime  = '24 Hours')) then 'G-BaselineAnd24Hours'
        when ((HasCollected  = 'YES' and CollectTime  = '24 Hours') and (HasCollected  = 'YES' and CollectTime  = '48 Hours')) then 'G-24And48Hours'        
        else 'NoFilter'
        end as filterGroup

Outputs are similar in both cases. It can realize ID which has ONLY one timepoint, but if the ID has two or more timepoint, it will still using the first timepoint caught, and not counting them in double timepoint group.

question from:https://stackoverflow.com/questions/65848697/mssql-create-group-with-multiple-condition

Answer 1

Do you mean something like this?

DECLARE @Table TABLE (ID INT,HasCollected NVARCHAR(10),TimeCollect NVARCHAR(50))
INSERT @Table
VALUES
(1,'YES','Baseline'),
(1,'NO', '24 Hours'),
(1,'NO', '48 Hours'),
(2,'YES','Baseline'),
(2,'YES','24 Hours'),
(2,'NO', '48 Hours'),
(3,'NO', 'Baseline'),
(3,'YES','24 Hours'),
(3,'NO', '48 Hours'),
(4,'NO', 'Baseline'),
(4,'YES','24 Hours'),
(4,'YES','48 Hours')

SELECT TimeCollect, COUNT(*) NumIds
FROM(
    SELECT DISTINCT
        ID,
        STUFF((SELECT ',' + t.TimeCollect
               FROM @Table t
               WHERE t.ID = t2.ID
               AND t.HasCollected = 'YES'
               FOR XML PATH('')), 1, 1,'') AS TimeCollect
    FROM @Table t2
    GROUP BY t2.ID
)a
GROUP BY TimeCollect

Results:

TimeCollect         NumIds
24 Hours            1
24 Hours,48 Hours   1
Baseline            1
Baseline,24 Hours   1

Or if you have SQL Server 2017+:

SELECT TimeCollect, COUNT(*) NumIds
FROM(
    SELECT ID,
           STRING_AGG(TimeCollect,', ') AS TimeCollect
    FROM @Table t
    WHERE t.HasCollected = 'YES'
    GROUP BY ID
)a
GROUP BY TimeCollect