none
T-SQL Query RRS feed

  • Question

  • Hi,

    From my data , I want to find out , the number of months a stored has red delivery status ( first month being the max monthend date and so on). If a store is green now and was red one month before, it will not be counted as red

    DECLARE  @temp table
    (
      RID        INT   IDENTITY(1,1),
      MonthEndDate   date,
      DeliveryStatus varchar(100),
      StoreID       INT
    )
    
    INSERT INTO @temp VALUES 
    (CAST('12-31-2019' AS DATE),'GREEN',1),
    (CAST('11-30-2019' AS DATE),'RED',1),
    (CAST('12-31-2019' AS DATE),'RED',2),
    (CAST('11-30-2019' AS DATE),'RED',2),
    (CAST('10-31-2019' AS DATE),'RED',2),
    (CAST('09-30-2019' AS DATE),'RED',2),
    (CAST('08-31-2019' AS DATE),'GREEN',2),
    (CAST('12-31-2019' AS DATE),'RED',3),
    (CAST('11-30-2019' AS DATE),'RED',3),
    (CAST('10-31-2019' AS DATE),'RED',3),
    (CAST('09-30-2019' AS DATE),'GREEN',3),
    (CAST('08-31-2019' AS DATE),'GREEN',3),
    (CAST('12-31-2019' AS DATE),'RED',4),
    (CAST('12-31-2019' AS DATE),'GREEN',4),
    (CAST('12-31-2019' AS DATE),'GREEN',4)
    
    SELECT * FROM @temp 
    
    
    
    

    I want output as

    StoredID , NumberofMonthsSinceithasbeenred

    1,   0

    2,   4

    3,  3

    4, 1

    thanks in advance

    Monday, January 13, 2020 2:50 AM

Answers

  • Is this intended on the same day?

    (CAST('12-31-2019' AS DATE),'RED',4),
    (CAST('12-31-2019' AS DATE),'GREEN',4),
    (CAST('12-31-2019' AS DATE),'GREEN',4)

    Sorry that was mistake

    correct dataset is

    DECLARE  @temp table
    (
      RID        INT   IDENTITY(1,1),
      MonthEndDate   date,
      DeliveryStatus varchar(100),
      StoreID       INT
    )
    
    INSERT INTO @temp VALUES 
    
    (CAST('12-31-2019' AS DATE),'RED',2),
    (CAST('11-30-2019' AS DATE),'RED',2),
    (CAST('10-31-2019' AS DATE),'RED',2),
    (CAST('09-30-2019' AS DATE),'RED',2),
    (CAST('08-31-2019' AS DATE),'GREEN',2),
    (CAST('07-31-2019' AS DATE),'RED',2),
    (CAST('06-30-2019' AS DATE),'RED',2),
    (CAST('05-31-2019' AS DATE),'GREEN',2),
    (CAST('04-30-2019' AS DATE),'RED',2),
    (CAST('12-31-2019' AS DATE),'RED',3),
    (CAST('11-30-2019' AS DATE),'RED',3),
    (CAST('10-31-2019' AS DATE),'RED',3),
    (CAST('09-30-2019' AS DATE),'GREEN',3),
    (CAST('08-31-2019' AS DATE),'GREEN',3),
    (CAST('01-31-2020' AS DATE),'RED',4),
    (CAST('12-31-2019' AS DATE),'RED',4),
    (CAST('11-30-2019' AS DATE),'GREEN',4),
    (CAST('10-31-2019' AS DATE),'GREEN',4)

    DECLARE  @temp table
    (
      RID        INT   IDENTITY(1,1),
      MonthEndDate   date,
      DeliveryStatus varchar(100),
      StoreID       INT
    )
    
    INSERT INTO @temp VALUES 
    (CAST('12-31-2019' AS DATE),'GREEN',1),
    (CAST('11-30-2019' AS DATE),'RED',1),
    (CAST('12-31-2019' AS DATE),'RED',2),
    (CAST('11-30-2019' AS DATE),'RED',2),
    (CAST('10-31-2019' AS DATE),'RED',2),
    (CAST('09-30-2019' AS DATE),'RED',2),
    (CAST('08-31-2019' AS DATE),'GREEN',2),
    (CAST('07-31-2019' AS DATE),'RED',2),
    (CAST('06-30-2019' AS DATE),'RED',2),
    (CAST('05-31-2019' AS DATE),'GREEN',2),
    (CAST('04-30-2019' AS DATE),'RED',2),
    (CAST('12-31-2019' AS DATE),'RED',3),
    (CAST('11-30-2019' AS DATE),'RED',3),
    (CAST('10-31-2019' AS DATE),'RED',3),
    (CAST('09-30-2019' AS DATE),'GREEN',3),
    (CAST('08-31-2019' AS DATE),'GREEN',3),
    (CAST('01-31-2020' AS DATE),'RED',4),
    (CAST('12-31-2019' AS DATE),'RED',4),
    (CAST('11-30-2019' AS DATE),'GREEN',4),
    (CAST('10-31-2019' AS DATE),'GREEN',4)
    
    
    --select  *   FROM @temp
    ;with mycte as   (
    select *, sum(case when DeliveryStatus ='Green' then 1 else 0 end) Over(Partition by storeId order by MonthEndDate desc ) chk
    from @temp)
    
    
    
    select 
     
    
    storeid
    , ISNULL(sum (case when chk=0 and DeliveryStatus ='Red' then 1 else null end),0) NumberofMonthsSinceithasbeenred
    from mycte
     Group by storeid
    
     /*
     storeid	NumberofMonthsSinceithasbeenred
    1	0
    2	4
    3	3
    4	2
     */
     


    • Marked as answer by John_tay Tuesday, January 14, 2020 3:01 AM
    Monday, January 13, 2020 9:54 PM
    Moderator

All replies

  • Hi John_tay, 

    Please try following script . 

    DECLARE  @temp table
    (
      RID        INT   IDENTITY(1,1),
      MonthEndDate   date,
      DeliveryStatus varchar(100),
      StoreID       INT
    )
    
    INSERT INTO @temp VALUES 
    (CAST('12-31-2019' AS DATE),'GREEN',1),
    (CAST('11-30-2019' AS DATE),'RED',1),
    (CAST('12-31-2019' AS DATE),'RED',2),
    (CAST('11-30-2019' AS DATE),'RED',2),
    (CAST('10-31-2019' AS DATE),'RED',2),
    (CAST('09-30-2019' AS DATE),'RED',2),
    (CAST('08-31-2019' AS DATE),'GREEN',2),
    (CAST('12-31-2019' AS DATE),'RED',3),
    (CAST('11-30-2019' AS DATE),'RED',3),
    (CAST('10-31-2019' AS DATE),'RED',3),
    (CAST('09-30-2019' AS DATE),'GREEN',3),
    (CAST('08-31-2019' AS DATE),'GREEN',3),
    (CAST('12-31-2019' AS DATE),'RED',4),
    (CAST('12-31-2019' AS DATE),'GREEN',4),
    (CAST('12-31-2019' AS DATE),'GREEN',4)
    ;with cte as (
    SELECT distinct a.RID,a.MonthEndDate,
    case when a.DeliveryStatus='RED'and b.DeliveryStatus='GREEN' then 'GREEN' else a.DeliveryStatus end DeliveryStatus,
     a.StoreID
    FROM @temp a 
    left join @temp b on a.MonthEndDate=dateadd(mm,-1,b.MonthEndDate) and a.StoreID=b.StoreID)
    select distinct StoreID,
    sum(case when DeliveryStatus='RED'then 1 else 0 end)over(partition by StoreID) NumberofMonthsSinceithasbeenred
    from cte 
    /*
    StoreID     NumberofMonthsSinceithasbeenred
    ----------- -------------------------------
    1           0
    2           4
    3           3
    4           1
    */
    
    

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, January 13, 2020 3:25 AM
  • DECLARE  @temp table
    (
      RID        INT   IDENTITY(1,1),
      MonthEndDate   date,
      DeliveryStatus varchar(100),
      StoreID       INT
    )
    
    INSERT INTO @temp VALUES 
    (CAST('12-31-2019' AS DATE),'GREEN',1),
    (CAST('11-30-2019' AS DATE),'RED',1),
    (CAST('12-31-2019' AS DATE),'RED',2),
    (CAST('11-30-2019' AS DATE),'RED',2),
    (CAST('10-31-2019' AS DATE),'RED',2),
    (CAST('09-30-2019' AS DATE),'RED',2),
    (CAST('08-31-2019' AS DATE),'GREEN',2),
    (CAST('12-31-2019' AS DATE),'RED',3),
    (CAST('11-30-2019' AS DATE),'RED',3),
    (CAST('10-31-2019' AS DATE),'RED',3),
    (CAST('09-30-2019' AS DATE),'GREEN',3),
    (CAST('08-31-2019' AS DATE),'GREEN',3),
    (CAST('12-31-2019' AS DATE),'RED',4),
    (CAST('12-31-2019' AS DATE),'GREEN',4),
    (CAST('12-31-2019' AS DATE),'GREEN',4)
    
    
    ;with mycte as (
    SELECT *
    , row_number() over(Partition by storeId Order by MonthEndDate    desc) rn  
    FROM @temp)
    ,mycte1 as (
    select *, sum(case when rn=1 and DeliveryStatus ='Green' then 1 else 0 end) Over(Partition by storeId  ) chk
    from mycte)
    
    select storeid, ISNULL(sum (case when chk=0 and DeliveryStatus ='Red' then 1 else null end),0) NumberofMonthsSinceithasbeenred
    from mycte1
    Group by storeid

    Monday, January 13, 2020 2:35 PM
    Moderator
  • >> From my data , I want to find out , the number of months a store has red delivery status (first month being the max month end date and so on). If a store is green now and was red one month before, it will not be counted as red <<

    Did you know that table must have a key? This is not not not an option. This is a definition. Did you know that identifiers can never be numeric? They use what is called a nominal scale and when you get around to taking a class in basic data modeling, they will discuss it in detail. You also don't know the only format for a date in ANSI/ISO Standard SQL is based on ISO 8601, not your local dialect. I also prefer using a proposed, but not yet standard convention, for months. You will be able to fix this, when Microsoft finally implements the Standard SQL INTERVAL temporal types. Here's an attempt at correcting your DDL:

    Your specs are not clear. You talk about measuring something from "now" back in time. But "now" changes from month to month as we get more delivery data!  Please explain what you mean. 

    CREATE TABLE Deliveries
    (store_id CHAR(3) NOT NULL
     CHECK (store_id LIKE '[0-9][0-9][0-9]'), 
    delivery_month CHAR(10) NOT NULL
     CHECK (delivery_month LIKE '[12][0-9][0-9][0-9]-[01][0-9]-00')
      REFERENCES Month_Calendar (cal_month),
    delivery_status CHAR(5) NOT NULL
     CHECK (delivery_status IN ('RED', 'GREEN')), 
      PRIMARY KEY (store_id, delivery_month)
    );


    INSERT INTO Deliveries 
    VALUES 
    ('001', '2019-11-00' , 'RED'), 
    ('001', '2019-12-00', 'GREEN'), 
    ('002', '2019-08-00', 'GREEN')
    ('002', '2019-09-00', 'RED'),
    ('002', '2019-10-00', 'RED'),
    ('002', '2019-11-00', 'RED'),
    ('002', '2019-12-00', 'RED'),
    ('003', '2019-08-00', 'GREEN'), 
    ('003', '2019-09-00', 'GREEN'), 
    ('003', '2019-10-00', 'RED'), 
    ('003', '2019-11-00', 'RED'), 
    ('003', '2019-12-00', 'RED'), 
    ('004', '2019-12-00', 'GREEN'),
    ('004', '2019-12-00', 'GREEN'), 
    ('004', '2019-12-00', 'RED');

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Monday, January 13, 2020 7:53 PM
  • DECLARE  @temp table
    (
      RID        INT   IDENTITY(1,1),
      MonthEndDate   date,
      DeliveryStatus varchar(100),
      StoreID       INT
    )
    
    INSERT INTO @temp VALUES 
    (CAST('12-31-2019' AS DATE),'GREEN',1),
    (CAST('11-30-2019' AS DATE),'RED',1),
    (CAST('12-31-2019' AS DATE),'RED',2),
    (CAST('11-30-2019' AS DATE),'RED',2),
    (CAST('10-31-2019' AS DATE),'RED',2),
    (CAST('09-30-2019' AS DATE),'RED',2),
    (CAST('08-31-2019' AS DATE),'GREEN',2),
    (CAST('12-31-2019' AS DATE),'RED',3),
    (CAST('11-30-2019' AS DATE),'RED',3),
    (CAST('10-31-2019' AS DATE),'RED',3),
    (CAST('09-30-2019' AS DATE),'GREEN',3),
    (CAST('08-31-2019' AS DATE),'GREEN',3),
    (CAST('12-31-2019' AS DATE),'RED',4),
    (CAST('12-31-2019' AS DATE),'GREEN',4),
    (CAST('12-31-2019' AS DATE),'GREEN',4)
    
    
    ;with mycte as (
    SELECT *
    , row_number() over(Partition by storeId Order by MonthEndDate    desc) rn  
    FROM @temp)
    ,mycte1 as (
    select *, sum(case when rn=1 and DeliveryStatus ='Green' then 1 else 0 end) Over(Partition by storeId  ) chk
    from mycte)
    
    select storeid, ISNULL(sum (case when chk=0 and DeliveryStatus ='Red' then 1 else null end),0) NumberofMonthsSinceithasbeenred
    from mycte1
    Group by storeid

    Hi Jingyang,

    This code has some issue as with data below , for store_id = 2 , it returns answer as 5

    DECLARE  @temp table
    (
      RID        INT   IDENTITY(1,1),
      MonthEndDate   date,
      DeliveryStatus varchar(100),
      StoreID       INT
    )
    
    INSERT INTO @temp VALUES 
    (CAST('12-31-2019' AS DATE),'GREEN',1),
    (CAST('11-30-2019' AS DATE),'RED',1),
    (CAST('12-31-2019' AS DATE),'RED',2),
    (CAST('11-30-2019' AS DATE),'RED',2),
    (CAST('10-31-2019' AS DATE),'RED',2),
    (CAST('09-30-2019' AS DATE),'RED',2),
    (CAST('08-31-2019' AS DATE),'GREEN',2),
    (CAST('07-31-2019' AS DATE),'RED',2),
    (CAST('06-30-2019' AS DATE),'GREEN',2),
    (CAST('12-31-2019' AS DATE),'RED',3),
    (CAST('11-30-2019' AS DATE),'RED',3),
    (CAST('10-31-2019' AS DATE),'RED',3),
    (CAST('09-30-2019' AS DATE),'GREEN',3),
    (CAST('08-31-2019' AS DATE),'GREEN',3),
    (CAST('12-31-2019' AS DATE),'RED',4),
    (CAST('12-31-2019' AS DATE),'GREEN',4),
    (CAST('12-31-2019' AS DATE),'GREEN',4)
    

    Monday, January 13, 2020 8:26 PM
  • DECLARE  @temp table
    (
      RID        INT   IDENTITY(1,1),
      MonthEndDate   date,
      DeliveryStatus varchar(100),
      StoreID       INT
    )
    
    INSERT INTO @temp VALUES 
    (CAST('12-31-2019' AS DATE),'GREEN',1),
    (CAST('11-30-2019' AS DATE),'RED',1),
    (CAST('12-31-2019' AS DATE),'RED',2),
    (CAST('11-30-2019' AS DATE),'RED',2),
    (CAST('10-31-2019' AS DATE),'RED',2),
    (CAST('09-30-2019' AS DATE),'RED',2),
    (CAST('08-31-2019' AS DATE),'GREEN',2),
    (CAST('07-31-2019' AS DATE),'RED',2),
    (CAST('06-30-2019' AS DATE),'GREEN',2),
    (CAST('12-31-2019' AS DATE),'RED',3),
    (CAST('11-30-2019' AS DATE),'RED',3),
    (CAST('10-31-2019' AS DATE),'RED',3),
    (CAST('09-30-2019' AS DATE),'GREEN',3),
    (CAST('08-31-2019' AS DATE),'GREEN',3),
    (CAST('12-31-2019' AS DATE),'RED',4),
    (CAST('12-31-2019' AS DATE),'GREEN',4),
    (CAST('12-31-2019' AS DATE),'GREEN',4)
    
    --select  *   FROM @temp
    ;with mycte as   (
    select *, sum(case when DeliveryStatus ='Green' then 1 else 0 end) Over(Partition by storeId order by MonthEndDate desc, DeliveryStatus desc ) chk
    from @temp)
    
    select storeid
    , ISNULL(sum (case when chk=0 and DeliveryStatus ='Red' then 1 else null end),0) NumberofMonthsSinceithasbeenred
    from mycte
     Group by storeid
     

    Monday, January 13, 2020 8:59 PM
    Moderator
  • Is this intended on the same day?

    (CAST('12-31-2019' AS DATE),'RED',4),
    (CAST('12-31-2019' AS DATE),'GREEN',4),
    (CAST('12-31-2019' AS DATE),'GREEN',4)

    Monday, January 13, 2020 9:15 PM
    Moderator
  • Is this intended on the same day?

    (CAST('12-31-2019' AS DATE),'RED',4),
    (CAST('12-31-2019' AS DATE),'GREEN',4),
    (CAST('12-31-2019' AS DATE),'GREEN',4)

    Sorry that was mistake

    correct dataset is

    DECLARE  @temp table
    (
      RID        INT   IDENTITY(1,1),
      MonthEndDate   date,
      DeliveryStatus varchar(100),
      StoreID       INT
    )
    
    INSERT INTO @temp VALUES 
    
    (CAST('12-31-2019' AS DATE),'RED',2),
    (CAST('11-30-2019' AS DATE),'RED',2),
    (CAST('10-31-2019' AS DATE),'RED',2),
    (CAST('09-30-2019' AS DATE),'RED',2),
    (CAST('08-31-2019' AS DATE),'GREEN',2),
    (CAST('07-31-2019' AS DATE),'RED',2),
    (CAST('06-30-2019' AS DATE),'RED',2),
    (CAST('05-31-2019' AS DATE),'GREEN',2),
    (CAST('04-30-2019' AS DATE),'RED',2),
    (CAST('12-31-2019' AS DATE),'RED',3),
    (CAST('11-30-2019' AS DATE),'RED',3),
    (CAST('10-31-2019' AS DATE),'RED',3),
    (CAST('09-30-2019' AS DATE),'GREEN',3),
    (CAST('08-31-2019' AS DATE),'GREEN',3),
    (CAST('01-31-2020' AS DATE),'RED',4),
    (CAST('12-31-2019' AS DATE),'RED',4),
    (CAST('11-30-2019' AS DATE),'GREEN',4),
    (CAST('10-31-2019' AS DATE),'GREEN',4)

    Monday, January 13, 2020 9:29 PM
  • Hi John_tay, 

    Please try following script . 

    DECLARE  @temp table
    (
      RID        INT   IDENTITY(1,1),
      MonthEndDate   date,
      DeliveryStatus varchar(100),
      StoreID       INT
    )
    
    INSERT INTO @temp VALUES 
    (CAST('12-31-2019' AS DATE),'GREEN',1),
    (CAST('11-30-2019' AS DATE),'RED',1),
    (CAST('12-31-2019' AS DATE),'RED',2),
    (CAST('11-30-2019' AS DATE),'RED',2),
    (CAST('10-31-2019' AS DATE),'RED',2),
    (CAST('09-30-2019' AS DATE),'RED',2),
    (CAST('08-31-2019' AS DATE),'GREEN',2),
    (CAST('12-31-2019' AS DATE),'RED',3),
    (CAST('11-30-2019' AS DATE),'RED',3),
    (CAST('10-31-2019' AS DATE),'RED',3),
    (CAST('09-30-2019' AS DATE),'GREEN',3),
    (CAST('08-31-2019' AS DATE),'GREEN',3),
    (CAST('12-31-2019' AS DATE),'RED',4),
    (CAST('12-31-2019' AS DATE),'GREEN',4),
    (CAST('12-31-2019' AS DATE),'GREEN',4)
    ;with cte as (
    SELECT distinct a.RID,a.MonthEndDate,
    case when a.DeliveryStatus='RED'and b.DeliveryStatus='GREEN' then 'GREEN' else a.DeliveryStatus end DeliveryStatus,
     a.StoreID
    FROM @temp a 
    left join @temp b on a.MonthEndDate=dateadd(mm,-1,b.MonthEndDate) and a.StoreID=b.StoreID)
    select distinct StoreID,
    sum(case when DeliveryStatus='RED'then 1 else 0 end)over(partition by StoreID) NumberofMonthsSinceithasbeenred
    from cte 
    /*
    StoreID     NumberofMonthsSinceithasbeenred
    ----------- -------------------------------
    1           0
    2           4
    3           3
    4           1
    */
    
    

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Hi,

    This is giving wrong answer for storeid = 2 try this data . answer should be 4 .. it doesn't matter how many time status was red in the past.


    DECLARE  @temp table
    (
      RID        INT   IDENTITY(1,1),
      MonthEndDate   date,
      DeliveryStatus varchar(100),
      StoreID       INT
    )
    
    INSERT INTO @temp VALUES 
    
    (CAST('12-31-2019' AS DATE),'RED',2),
    (CAST('11-30-2019' AS DATE),'RED',2),
    (CAST('10-31-2019' AS DATE),'RED',2),
    (CAST('09-30-2019' AS DATE),'RED',2),
    (CAST('08-31-2019' AS DATE),'GREEN',2),
    (CAST('07-31-2019' AS DATE),'RED',2),
    (CAST('06-30-2019' AS DATE),'RED',2),
    (CAST('05-31-2019' AS DATE),'GREEN',2),
    (CAST('04-30-2019' AS DATE),'RED',2),
    (CAST('12-31-2019' AS DATE),'RED',3),
    (CAST('11-30-2019' AS DATE),'RED',3),
    (CAST('10-31-2019' AS DATE),'RED',3),
    (CAST('09-30-2019' AS DATE),'GREEN',3),
    (CAST('08-31-2019' AS DATE),'GREEN',3),
    (CAST('01-31-2020' AS DATE),'RED',4),
    (CAST('12-31-2019' AS DATE),'RED',4),
    (CAST('11-30-2019' AS DATE),'GREEN',4),
    (CAST('10-31-2019' AS DATE),'GREEN',4)
    
    ;with cte as (
    SELECT distinct a.RID,a.MonthEndDate,
    case when a.DeliveryStatus='RED'and b.DeliveryStatus='GREEN' then 'GREEN' else a.DeliveryStatus end DeliveryStatus,
     a.StoreID
    FROM @temp a 
    left join @temp b on a.MonthEndDate=dateadd(mm,-1,b.MonthEndDate) and a.StoreID=b.StoreID)
    select distinct StoreID,
    sum(case when DeliveryStatus='RED'then 1 else 0 end)over(partition by StoreID) NumberofMonthsSinceithasbeenred
    from cte 

    Monday, January 13, 2020 9:33 PM
  • Is this intended on the same day?

    (CAST('12-31-2019' AS DATE),'RED',4),
    (CAST('12-31-2019' AS DATE),'GREEN',4),
    (CAST('12-31-2019' AS DATE),'GREEN',4)

    Sorry that was mistake

    correct dataset is

    DECLARE  @temp table
    (
      RID        INT   IDENTITY(1,1),
      MonthEndDate   date,
      DeliveryStatus varchar(100),
      StoreID       INT
    )
    
    INSERT INTO @temp VALUES 
    
    (CAST('12-31-2019' AS DATE),'RED',2),
    (CAST('11-30-2019' AS DATE),'RED',2),
    (CAST('10-31-2019' AS DATE),'RED',2),
    (CAST('09-30-2019' AS DATE),'RED',2),
    (CAST('08-31-2019' AS DATE),'GREEN',2),
    (CAST('07-31-2019' AS DATE),'RED',2),
    (CAST('06-30-2019' AS DATE),'RED',2),
    (CAST('05-31-2019' AS DATE),'GREEN',2),
    (CAST('04-30-2019' AS DATE),'RED',2),
    (CAST('12-31-2019' AS DATE),'RED',3),
    (CAST('11-30-2019' AS DATE),'RED',3),
    (CAST('10-31-2019' AS DATE),'RED',3),
    (CAST('09-30-2019' AS DATE),'GREEN',3),
    (CAST('08-31-2019' AS DATE),'GREEN',3),
    (CAST('01-31-2020' AS DATE),'RED',4),
    (CAST('12-31-2019' AS DATE),'RED',4),
    (CAST('11-30-2019' AS DATE),'GREEN',4),
    (CAST('10-31-2019' AS DATE),'GREEN',4)

    DECLARE  @temp table
    (
      RID        INT   IDENTITY(1,1),
      MonthEndDate   date,
      DeliveryStatus varchar(100),
      StoreID       INT
    )
    
    INSERT INTO @temp VALUES 
    (CAST('12-31-2019' AS DATE),'GREEN',1),
    (CAST('11-30-2019' AS DATE),'RED',1),
    (CAST('12-31-2019' AS DATE),'RED',2),
    (CAST('11-30-2019' AS DATE),'RED',2),
    (CAST('10-31-2019' AS DATE),'RED',2),
    (CAST('09-30-2019' AS DATE),'RED',2),
    (CAST('08-31-2019' AS DATE),'GREEN',2),
    (CAST('07-31-2019' AS DATE),'RED',2),
    (CAST('06-30-2019' AS DATE),'RED',2),
    (CAST('05-31-2019' AS DATE),'GREEN',2),
    (CAST('04-30-2019' AS DATE),'RED',2),
    (CAST('12-31-2019' AS DATE),'RED',3),
    (CAST('11-30-2019' AS DATE),'RED',3),
    (CAST('10-31-2019' AS DATE),'RED',3),
    (CAST('09-30-2019' AS DATE),'GREEN',3),
    (CAST('08-31-2019' AS DATE),'GREEN',3),
    (CAST('01-31-2020' AS DATE),'RED',4),
    (CAST('12-31-2019' AS DATE),'RED',4),
    (CAST('11-30-2019' AS DATE),'GREEN',4),
    (CAST('10-31-2019' AS DATE),'GREEN',4)
    
    
    --select  *   FROM @temp
    ;with mycte as   (
    select *, sum(case when DeliveryStatus ='Green' then 1 else 0 end) Over(Partition by storeId order by MonthEndDate desc ) chk
    from @temp)
    
    
    
    select 
     
    
    storeid
    , ISNULL(sum (case when chk=0 and DeliveryStatus ='Red' then 1 else null end),0) NumberofMonthsSinceithasbeenred
    from mycte
     Group by storeid
    
     /*
     storeid	NumberofMonthsSinceithasbeenred
    1	0
    2	4
    3	3
    4	2
     */
     


    • Marked as answer by John_tay Tuesday, January 14, 2020 3:01 AM
    Monday, January 13, 2020 9:54 PM
    Moderator
  • THANKS IT WORKS GREAT
    Tuesday, January 14, 2020 3:01 AM