SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select @@version;


(No column name)


Microsoft SQL Server 2017 (RTM-CU12) (KB4464082) - 14.0.3045.24 (X64) 
	Oct 18 2018 23:11:05 
	Copyright (C) 2017 Microsoft Corporation
	Express Edition (64-bit) on Windows Server 2012 R2 Standard 6.3 <X64> (Build 9600: ) (Hypervisor)



CREATE TABLE Table_1
    (Name varchar(1), Activity int, LogTime datetime)
;
INSERT INTO Table_1
    (Name, Activity, LogTime)
VALUES
    ('A', 0, '2018-12-17 10:16:04.877'),
    ('A', 1, '2018-12-15 10:16:04.877'),
    ('A', 0, '2018-12-16 10:16:04.877'),
    ('A', 0, '2018-12-10 10:16:04.877'),
    ('A', 0, '2018-12-10 10:10:04.877'),
    ('B', 1, '2018-12-16 10:16:04.877'),
    ('B', 0, '2018-12-17 10:16:04.877'),
    ('C', 1, '2018-12-14 10:16:04.877'),
    ('C', 1, '2018-12-12 10:16:04.877'),
    ('C', 1, '2018-12-18 10:16:04.877')
;
10 rows affected

 SELECT 
     Name, 
     COUNT(CASE WHEN Activity = 0 THEN 1 END) AS TOTALActivity_0,
     COUNT(CASE WHEN Activity = 1 THEN 1 END) AS TOTALActivity_1,
     MAX(CASE WHEN Activity = 0 THEN LogTime END) AS LatestActivity_0_Logtime,
     MAX(CASE WHEN Activity = 1 THEN LogTime END) AS LatestActivity_1_Logtime
 FROM
     Table_1
 GROUP BY
     Name ;


Name
TOTALActivity_0
TOTALActivity_1
LatestActivity_0_Logtime
LatestActivity_1_Logtime


A
4
1
17/12/2018 10:16:04
15/12/2018 10:16:04

B
1
1
17/12/2018 10:16:04
16/12/2018 10:16:04

C
0
3
null
18/12/2018 10:16:04


Warning: Null value is eliminated by an aggregate or other SET operation.

Name	TOTALActivity_0	TOTALActivity_1	LatestActivity_0_Logtime	LatestActivity_1_Logtime
A	4	1	17/12/2018 10:16:04	15/12/2018 10:16:04
B	1	1	17/12/2018 10:16:04	16/12/2018 10:16:04
C	0	3	null	18/12/2018 10:16:04