SQL Server 2014

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE Peak (
    SA_data INT,
    dateTime DATE
);

INSERT Peak
VALUES
    (12, '2023-01-01'),
    (15, '2023-01-02'),
    (13, '2023-01-03'),
    (11, '2023-01-04'),
    (10, '2023-01-05'),
    ( 5, '2023-01-06'),
    (31, '2023-01-07'),
    ( 9, '2023-01-08'),
    (31, '2023-01-09'),
    (21, '2023-01-10'),
    (22, '2023-01-11'),
    (34, '2023-01-12'),
    ( 8, '2023-01-13'),
    ( 5, '2023-01-14'),
    (33, '2023-01-15'),
    (22, '2023-01-16'),
    ( 9, '2023-01-17'),
    (18, '2023-01-18'),
    (19, '2023-01-19'),
    (22, '2023-01-20'),
    (29, '2023-01-21'),
    (10, '2023-01-22'),
    ( 9, '2023-01-23'),
    ( 8, '2023-01-24'),
    (29, '2023-01-25'),
    (33, '2023-01-26'),
    (55, '2023-01-27'),
    (81, '2023-01-28'),
    ( 3, '2023-01-29'),
    (88, '2023-01-30'),
    (44, '2023-01-31'),
    (10, '2023-02-01'),
    (20, '2023-02-02'),
    (44, '2023-02-03'),
    (83, '2023-02-04'),
    (69, '2023-02-05'),
    (99, '2023-02-06'),
    (20, '2023-02-07'),
    (40, '2023-02-08'),
    (10, '2023-02-09'),
    (32, '2023-02-10'),
    (33, '2023-02-11'),
    (83, '2023-02-12'),
    (63, '2023-02-13'),
    (92, '2023-02-14'),
    (65, '2023-02-15'),
    (42, '2023-02-16'),
    (84, '2023-02-17'),
    (21, '2023-02-18'),
    (23, '2023-02-19'),
    (12, '2023-02-20'),
    (92, '2023-02-21'),
    (65, '2023-02-22'),
    (74, '2023-02-23'),
    (83, '2023-02-24'),
    (10, '2023-02-25');
56 rows affected

CREATE TABLE Peak_fact (
    DataDate DATE,
    peak INT,
    peakType VARCHAR(100)
)
CREATE PROCEDURE SelectPeaksAndInsertIntoPeakFact 
AS
BEGIN
    DECLARE @currentDate DATE = (SELECT MIN(dateTime) FROM Peak); -- Initialize starting date
    DECLARE @lastDate DATE = (SELECT MAX(dateTime) FROM Peak);
    DECLARE @peakType VARCHAR(5) = 'Peak1'; -- Initialize peak type

    WHILE @currentDate <= @lastDate
    BEGIN
        DECLARE @Datadate DATE;
        DECLARE @peak INT;
      
        SELECT TOP 1
            @Datadate = s.dateTime,
            @peak = s.peak
        FROM (
            SELECT
                p.*,
                MAX(p.SA_data) OVER() AS peak
            FROM Peak p
            WHERE p.dateTime BETWEEN @currentDate AND DATEADD(DAY, 6, @currentDate)
        ) s
        WHERE s.SA_data = peak
        ORDER BY s.dateTime;

        INSERT INTO Peak_fact (DataDate, peak, peakType)
        VALUES (@Datadate, @peak, @peakType);

        SET @currentDate = DATEADD(DAY, 8, @Datadate); -- Skip 7 days 
        SET @peakType = CASE @peakType WHEN 'Peak1' THEN 'Peak2' WHEN 'Peak2' THEN 'Peak3' ELSE 'Peak1' END; -- Cycle peak types
    END;
END;
EXEC SelectPeaksAndInsertIntoPeakFact

SELECT *
FROM Peak_fact
ORDER BY DataDate;


DataDate
peak
peakType


2023-01-07
31
Peak1

2023-01-15
33
Peak2

2023-01-28
81
Peak3

2023-02-06
99
Peak1

2023-02-14
92
Peak2

2023-02-24
83
Peak3


SELECT
    s.dateTime AS DataDate,
    s.SA_data AS peak,
    CONCAT(
        'Peak',
        (((ROW_NUMBER() OVER(ORDER BY s.dateTime)) - 1) % 3 + 1)
        ) AS peakType
FROM (
    SELECT
        *,
        MAX(p.SA_data) OVER(
           ORDER BY p.dateTime ROWS BETWEEN 7 PRECEDING AND 1 PRECEDING
           ) AS Max7Before,
        MAX(p.SA_data) OVER(
           ORDER BY p.dateTime ROWS BETWEEN 1 FOLLOWING AND 7 FOLLOWING
           ) AS Max7After
    FROM Peak p
) s
WHERE s.SA_data > s.Max7Before
AND s.SA_data >= s.Max7After
ORDER BY s.dateTime;


DataDate
peak
peakType


2023-01-12
34
Peak1

2023-02-06
99
Peak2

2023-02-14
92
Peak3
DataDate	peak	peakType
2023-01-07	31	Peak1
2023-01-15	33	Peak2
2023-01-28	81	Peak3
2023-02-06	99	Peak1
2023-02-14	92	Peak2
2023-02-24	83	Peak3