0

Consider the following table in SQL Server:

enter image description here

I want to write a SQL query to generate the column Indicator. This column should be set to 1 on the first occurrence of Flag = 1 for each category.

For instance, for Category A, column Flag is set to 1 for the dates 1/3/2019, 1/4/2019, 1/5/2019, 1/6/2019. Since 1/3/2019 is the earliest date when Flag was set to 1, the Indicator column for that record should also be set to 1.

What SQL Server query should I write for this?

PS: The figure already shows the desired output for the Indicator column.

Below is the code to generate the table in SQL Server:

CREATE TABLE myTable
(
    Category CHAR(1),
    Date DATE,
    Flag INT
)

INSERT INTO myTable (Category, Date, Flag) 
VALUES ('A', '2019-01-01', 0), ('A', '2019-02-01', 0),
       ('A', '2019-03-01', 1), ('A', '2019-04-01', 1),
       ('A', '2019-05-01', 1), ('A', '2019-06-01', 1),
       ('B', '2019-01-01', 0), ('B', '2019-02-01', 0),
       ('B', '2019-03-01', 0), ('B', '2019-04-01', 0),
       ('B', '2019-05-01', 1), ('B', '2019-06-01', 1),
       ('C', '2019-01-01', 0), ('C', '2019-02-01', 0),
       ('C', '2019-03-01', 0), ('C', '2019-04-01', 1),
       ('C', '2019-05-01', 1), ('C', '2019-06-01', 1),
       ('C', '2019-07-01', 1)
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Siddharth Gosalia
  • 301
  • 1
  • 4
  • 18
  • See [Why should I provide an MCRE for what seems to me to be a very simple SQL query](http://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query) – Strawberry Dec 06 '19 at 19:05
  • 1
    MySQL <> SQL Server. Please only tag the RDBMS you are *really* using. Also, images aren't helpful to those you're asking for help from. Post data as what it is, `text`, and show the results you are after and your attempts in your question; explaining why they aren't working. – Thom A Dec 06 '19 at 19:06
  • 1
    Does this answer your question? [Select first row in each GROUP BY group?](https://stackoverflow.com/questions/3800551/select-first-row-in-each-group-by-group) – Clockwork-Muse Dec 06 '19 at 19:43

3 Answers3

2

One way using a derived table and MIN() to figure out which is the first date for a category that has the flag. Join that back to the original table.

DEMO

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
SQLChao
  • 7,709
  • 1
  • 17
  • 32
1

I am thinking of using the min() function as a window function:

select t.*, 
       (case then t.flag = 1 and
                  t.date = min(t.date) over (partition by t.category, t.flag)
             then 1 else 0
        end) as indicator
from myTable t
order by t.Category, t.date
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
0

Another way

DEMO

CREATE TABLE myTable
 (
    Category char(1),
    Date date,
    Flag int
 )

        INSERT INTO myTable (Category, Date, Flag) VALUES
        ('A','2019-01-01',0),
        ('A','2019-02-01',0),
        ('A','2019-03-01',1),
        ('A','2019-04-01',1),
        ('A','2019-05-01',1),
        ('A','2019-06-01',1),
        ('B','2019-01-01',0),
        ('B','2019-02-01',0),
        ('B','2019-03-01',0),
        ('B','2019-04-01',0),
        ('B','2019-05-01',1),
        ('B','2019-06-01',1),
        ('C','2019-01-01',0),
        ('C','2019-02-01',0),
        ('C','2019-03-01',0),
        ('C','2019-04-01',1),
        ('C','2019-05-01',1),
        ('C','2019-06-01',1),
        ('C','2019-07-01',1);

    select t.* , 
    CASE WHEN T.FLAG=1 AND FIRST_VALUE(T.DATE) OVER (PARTITION BY T.Category ORDER BY t.FLAG desc, t.Date asc)=T.DATE THEN 1 
   ELSE 0 END Indicator
    from myTable t
    order by t.Category, t.date
Samada
  • 56
  • 6