59218910, Sql Server

Language: Layout:

IF OBJECT_ID('yourtable') IS NOT NULL 
  DROP TABLE dbo.yourtable

CREATE TABLE YourTable
(
  Category CHAR(1),
  [Date] DATE,
  Flag BIT
)

INSERT INTO YourTable VALUES ('A', '2019-01-01', 0)
INSERT INTO YourTable VALUES ('A', '2019-01-02', 0)
INSERT INTO YourTable VALUES ('A', '2019-01-03', 1)
INSERT INTO YourTable VALUES ('A', '2019-01-04', 1)
INSERT INTO YourTable VALUES ('A', '2019-01-05', 1)
INSERT INTO YourTable VALUES ('A', '2019-01-06', 1)
INSERT INTO YourTable VALUES ('B', '2019-01-01', 0)
INSERT INTO YourTable VALUES ('B', '2019-01-02', 0)
INSERT INTO YourTable VALUES ('B', '2019-01-03', 0)
INSERT INTO YourTable VALUES ('B', '2019-01-04', 0)
INSERT INTO YourTable VALUES ('B', '2019-01-05', 1)
INSERT INTO YourTable VALUES ('B', '2019-01-06', 1)
INSERT INTO YourTable VALUES ('C', '2019-01-01', 0)
INSERT INTO YourTable VALUES ('C', '2019-01-02', 0)
INSERT INTO YourTable VALUES ('C', '2019-01-03', 0)
INSERT INTO YourTable VALUES ('C', '2019-01-04', 1)
INSERT INTO YourTable VALUES ('C', '2019-01-05', 1)
INSERT INTO YourTable VALUES ('C', '2019-01-06', 1)
INSERT INTO YourTable VALUES ('C', '2019-01-07', 1)

SELECT 
    yt.*
  , ISNULL(b.Indicator, 0) AS Indicator
FROM YourTable yt
LEFT JOIN 
  (SELECT category, MIN(date) AS date, 1 AS Indicator
  FROM dbo.YourTable
  WHERE Flag = 1
  GROUP BY Category) b ON b.Category = yt.Category AND b.date = yt.date

View schema
Execution time: 0,08 sec, rows selected: 19, rows affected: 19, absolute service time: 0,78 sec, absolute service time: 0,83 sec