SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE tablename (Name VARCHAR(5), Date DATE, Value INTEGER);

INSERT INTO tablename (Name, Date, Value) VALUES
  ('name1', '2021-07-01', '7'),
  ('name1', '2021-02-02', '10'),
  ('name1', '2020-08-10', '12'),
  ('name1', '2020-03-07', '5'),
  ('name2', '2018-09-11', '8'),
  ('name2', '2019-04-04', '2'),
  ('name2', '2021-05-17', '5'),
  ('name3', '2020-03-03', '0'),
  ('name3', '2019-12-12', '15'),
  ('name3', '2018-10-12', '11'),
  ('name4', '2010-10-11', '5'),
  ('name4', '2012-02-26', '7');
12 rows affected

SELECT DISTINCT Name,
      CASE 
        WHEN Name IN ('name1', 'name2') THEN
          MAX(CASE WHEN Date <= '2021-07-01' THEN Value END) OVER (PARTITION BY Name)
        WHEN Name IN ('name3', 'name4') THEN
          SUM(Value) OVER (PARTITION BY Name)
      END Value    
FROM tablename


Name
Value


name1
12

name2
8

name3
26

name4
12


SELECT Name,
       CASE 
         WHEN Name IN ('name1', 'name2') THEN MAX(CASE WHEN Date <= '2021-07-01' THEN Value END)
         WHEN Name IN ('name3', 'name4') THEN SUM(Value)
       END Value    
FROM tablename
GROUP BY Name


Name
Value


name1
12

name2
8

name3
26

name4
12