none
Counting the number of changes in availability Status RRS feed

  • Question

  • Hi All,

    Hope you are doing well!..I am trying to count the change in availability status for the items ..Available status is 1 ..Any other status other than that is unavailable... 1 count is basically when the item changes from becoming available (status -1 old_status) to again becoming available (status -1 new_status).. Please find below the input and output DDL..can you please help here...

    create table #input
    (ts datetime2,
    itemid int,
    old_status int,
    new_status int)

    insert into #input values
    ('2019-07-22 12:22:28.113','121','1','2'),
    ('2019-07-23  12:22:28.113','121','2','3'),
    ('2019-07-25  12:22:28.113','121','3','1'),
    ('2019-07-28  10:22:28.113','121','1','3'),
    ('2019-07-31  10:22:28.113','121','3','1'),
    ('2019-08-01  10:22:28.113','241','1','2'),
    ('2019-08-03  11:22:28.113','241','2','1'),
    ('2019-08-03  13:22:28.113','241','1','3'),
    ('2019-08-03  14:22:28.113','241','3','1'),
    ('2019-08-05  14:22:28.113','241','1','5'),
    ('2019-08-06  14:22:28.113','241','5','3'),
    ('2019-08-07  14:22:28.113','241','3','1'),
    ('2019-08-08  14:22:28.113','1231','1','3'),
    ('2019-08-08  18:22:28.113','1231','3','1'),
    ('2019-08-10  14:22:28.113','1231','1','2'),
    ('2019-08-11 14:22:28.113','1231','2','1'),
    ('2019-08-13 14:22:28.113','1231','1','3'),
    ('2019-08-15 14:22:28.113','1231','3','1'),
    ('2019-08-17 14:22:28.113','1231','1','2'),
    ('2019-08-19 14:22:28.113','1231','2','1')

    create table #output
    (itemid int,
    countofchangeinstatus int)

    insert into #output values
    ('121','2'),
    ('241','3'),
    ('1231','4')

    Thanks,

    Arun


    Arun

    Tuesday, August 27, 2019 10:17 AM

Answers

  • In your table  a numeric value does not need inside single quotations.

    Please choose right data type for your table. You are not using datetime2 so change it to datetime.

    Please remove them in your code. I have corrected them for your posted scripts.

    Here is a solution for your today's question:

    create table #input
    (ts datetime,
    itemid int,
    old_status int,
    new_status int)
    
    insert into #input values
    ('2019-07-22 12:22:28.113',121,1,2),
    ('2019-07-23  12:22:28.113',121,2,3),
    ('2019-07-25  12:22:28.113',121,3,1),
    ('2019-07-28  10:22:28.113',121,1,3),
    ('2019-07-31  10:22:28.113',121,3,1),
    ('2019-08-01  10:22:28.113',241,1,2),
    ('2019-08-03  11:22:28.113',241,2,1),
    ('2019-08-03  13:22:28.113',241,1,3),
    ('2019-08-03  14:22:28.113',241,3,1),
    ('2019-08-05  14:22:28.113',241,1,5),
    ('2019-08-06  14:22:28.113',241,5,3),
    ('2019-08-07  14:22:28.113',241,3,1),
    ('2019-08-08  14:22:28.113',1231,1,3),
    ('2019-08-08  18:22:28.113',1231,3,1),
    ('2019-08-10  14:22:28.113',1231,1,2),
    ('2019-08-11 14:22:28.113',1231,2,1),
    ('2019-08-13 14:22:28.113',1231,1,3),
    ('2019-08-15 14:22:28.113',1231,3,1),
    ('2019-08-17 14:22:28.113',1231,1,2),
    ('2019-08-19 14:22:28.113',1231,2,1)
    
    create table #output
    (itemid int,
    countofchangeinstatus int)
    
    insert into #output values
    (121,2),
    (241,3),
    (1231,4)
    
    select itemid
    ,Sum(Case when new_status=1 then 1 else 0 end)  countofchangeinstatus 
    from #input
    Group by itemid
    
    select * from #output
    
    drop table  #output,#input
    

    • Marked as answer by Hellothere8028 Wednesday, August 28, 2019 6:55 AM
    Tuesday, August 27, 2019 2:27 PM
    Moderator
  • Hi Hellothere8028,

    Is there possible that the first number is not 1 or the last number is not one? If so, we need to consider the following scenario:(2,1;1,3;3,1;1,2;2,1;1,2) or (2,1;1,3;3,1;1,2;2,1). Here's the code you can refer to: 

    select itemid, case 
    when count(old_status)=count(new_status) then count (old_status)
    else case 
    	when count(old_status)> count(new_status) then count(new_status)
    	else count(old_status)
    	end 
    end as countofchangeinstatus
    from #input
    where old_status=1 
    group by itemid
    
    /*
    itemid      countofchangeinstatus
    ----------- ---------------------
    121         2
    241         3
    1231        4
    */

    Hope it could help.

    Regards,

    Sabrina


    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.

    • Marked as answer by Hellothere8028 Wednesday, August 28, 2019 6:53 AM
    Wednesday, August 28, 2019 2:57 AM
  • Hi Guoxiong,

    Quick question!..Is it necessary to join on the row number too in the inner join....

    Thanks,

    Arun


    Arun

    I think it is necessary. This way you can guarantee the new_status = 1 is after old_status = 1. For example, if you want to do the calculation after July 22, that means the first row of your sample data should be excluded and then you cannot just count the rows with status = 1. It will give you the wrong information. I modified my query which should return 1 for itemid = 121:

    ;WITH CTE_Availability_Old AS (
    	SELECT ts, itemid, old_status, ROW_NUMBER() OVER(PARTITION BY itemid ORDER BY ts DESC) AS RowNumber
    	FROM #input
    	WHERE old_status = 1
    ),
    CTE_Availability_New AS (
    	SELECT ts, itemid, new_status, ROW_NUMBER() OVER(PARTITION BY itemid ORDER BY ts DESC) AS RowNumber
    	FROM #input
    	WHERE new_status = 1
    )
    
    SELECT o.itemid, COUNT(*) AS countofchangeinstatus
    FROM CTE_Availability_Old AS o
    INNER JOIN CTE_Availability_New AS n ON n.itemid = o.itemid AND n.ts >= o.ts AND n.RowNumber = o.RowNumber
    GROUP BY o.itemid;



    A Fan of SSIS, SSRS and SSAS

    Wednesday, August 28, 2019 1:53 PM

All replies

  • Is this what you need?

    select itemid, count(new_status) from #input where new_status = 1 group by itemid
    Tuesday, August 27, 2019 11:35 AM
  • ;WITH CTE_Availability_Old AS (
    	SELECT ts, itemid, old_status, ROW_NUMBER() OVER(PARTITION BY itemid ORDER BY ts) AS RowNumber
    	FROM #input
    	WHERE old_status = 1
    ),
    CTE_Availability_New AS (
    	SELECT ts, itemid, new_status, ROW_NUMBER() OVER(PARTITION BY itemid ORDER BY ts) AS RowNumber
    	FROM #input
    	WHERE new_status = 1
    )
    
    SELECT o.itemid, COUNT(*) AS countofchangeinstatus
    FROM CTE_Availability_Old AS o
    INNER JOIN CTE_Availability_New AS n ON n.itemid = o.itemid AND n.ts >= o.ts AND o.RowNumber = n.RowNumber
    GROUP BY o.itemid;


    A Fan of SSIS, SSRS and SSAS

    Tuesday, August 27, 2019 2:05 PM
  • In your table  a numeric value does not need inside single quotations.

    Please choose right data type for your table. You are not using datetime2 so change it to datetime.

    Please remove them in your code. I have corrected them for your posted scripts.

    Here is a solution for your today's question:

    create table #input
    (ts datetime,
    itemid int,
    old_status int,
    new_status int)
    
    insert into #input values
    ('2019-07-22 12:22:28.113',121,1,2),
    ('2019-07-23  12:22:28.113',121,2,3),
    ('2019-07-25  12:22:28.113',121,3,1),
    ('2019-07-28  10:22:28.113',121,1,3),
    ('2019-07-31  10:22:28.113',121,3,1),
    ('2019-08-01  10:22:28.113',241,1,2),
    ('2019-08-03  11:22:28.113',241,2,1),
    ('2019-08-03  13:22:28.113',241,1,3),
    ('2019-08-03  14:22:28.113',241,3,1),
    ('2019-08-05  14:22:28.113',241,1,5),
    ('2019-08-06  14:22:28.113',241,5,3),
    ('2019-08-07  14:22:28.113',241,3,1),
    ('2019-08-08  14:22:28.113',1231,1,3),
    ('2019-08-08  18:22:28.113',1231,3,1),
    ('2019-08-10  14:22:28.113',1231,1,2),
    ('2019-08-11 14:22:28.113',1231,2,1),
    ('2019-08-13 14:22:28.113',1231,1,3),
    ('2019-08-15 14:22:28.113',1231,3,1),
    ('2019-08-17 14:22:28.113',1231,1,2),
    ('2019-08-19 14:22:28.113',1231,2,1)
    
    create table #output
    (itemid int,
    countofchangeinstatus int)
    
    insert into #output values
    (121,2),
    (241,3),
    (1231,4)
    
    select itemid
    ,Sum(Case when new_status=1 then 1 else 0 end)  countofchangeinstatus 
    from #input
    Group by itemid
    
    select * from #output
    
    drop table  #output,#input
    

    • Marked as answer by Hellothere8028 Wednesday, August 28, 2019 6:55 AM
    Tuesday, August 27, 2019 2:27 PM
    Moderator
  • Hi Hellothere8028,

    Is there possible that the first number is not 1 or the last number is not one? If so, we need to consider the following scenario:(2,1;1,3;3,1;1,2;2,1;1,2) or (2,1;1,3;3,1;1,2;2,1). Here's the code you can refer to: 

    select itemid, case 
    when count(old_status)=count(new_status) then count (old_status)
    else case 
    	when count(old_status)> count(new_status) then count(new_status)
    	else count(old_status)
    	end 
    end as countofchangeinstatus
    from #input
    where old_status=1 
    group by itemid
    
    /*
    itemid      countofchangeinstatus
    ----------- ---------------------
    121         2
    241         3
    1231        4
    */

    Hope it could help.

    Regards,

    Sabrina


    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.

    • Marked as answer by Hellothere8028 Wednesday, August 28, 2019 6:53 AM
    Wednesday, August 28, 2019 2:57 AM
  • Hi Sabrina,

    I am considering only the scenario when 1 is the old_status and it becomes changed to the new_status 1...Appreciate your help!!...

    Thanks,

    Arun


    Arun

    Wednesday, August 28, 2019 6:54 AM
  • Hi Guoxiong,

    Quick question!..Is it necessary to join on the row number too in the inner join....

    Thanks,

    Arun


    Arun

    Wednesday, August 28, 2019 6:57 AM
  • Hi,

    I am looking at the scenario where 1 is the old status and then the corresponding new status for that item becomes 1...

    Thanks,

    Arun


    Arun

    Wednesday, August 28, 2019 7:03 AM
  • Hi Guoxiong,

    Quick question!..Is it necessary to join on the row number too in the inner join....

    Thanks,

    Arun


    Arun

    I think it is necessary. This way you can guarantee the new_status = 1 is after old_status = 1. For example, if you want to do the calculation after July 22, that means the first row of your sample data should be excluded and then you cannot just count the rows with status = 1. It will give you the wrong information. I modified my query which should return 1 for itemid = 121:

    ;WITH CTE_Availability_Old AS (
    	SELECT ts, itemid, old_status, ROW_NUMBER() OVER(PARTITION BY itemid ORDER BY ts DESC) AS RowNumber
    	FROM #input
    	WHERE old_status = 1
    ),
    CTE_Availability_New AS (
    	SELECT ts, itemid, new_status, ROW_NUMBER() OVER(PARTITION BY itemid ORDER BY ts DESC) AS RowNumber
    	FROM #input
    	WHERE new_status = 1
    )
    
    SELECT o.itemid, COUNT(*) AS countofchangeinstatus
    FROM CTE_Availability_Old AS o
    INNER JOIN CTE_Availability_New AS n ON n.itemid = o.itemid AND n.ts >= o.ts AND n.RowNumber = o.RowNumber
    GROUP BY o.itemid;



    A Fan of SSIS, SSRS and SSAS

    Wednesday, August 28, 2019 1:53 PM
  • Hi Guoxiong,

    Thanks!..Appreciate your response!!...

    Thanks,

    Arun


    Arun

    Friday, August 30, 2019 8:33 AM