SQL Server SQL Query to Count IDs with Consecutive Status Followed by a keyword

kjthegm6  于 2023-10-15  发布在  其他
关注(0)|答案(2)|浏览(125)

I have the following data in my database:

I'm trying to write an SQL query that will count the Retailer IDs where the FSL Status is "Imputed" for three consecutive months, followed immediately by a "Projected" status. I want to include only those cases where the "Projected" status occurs right after three consecutive "Imputed" statuses.

Can someone help me construct this SQL query?

8mmmxcuj

8mmmxcuj1#

It's not elegant but it works:

select count(distinct a.RetailerId)
from MyTable a
inner join MyTable b
  on a.RetailerId = b.RetailerId
  and dateadd(month, -1, a.Period) = b.Period
  and b.[FSL Status] = 'Imputed'
inner join MyTable c
  on b.RetailerId = c.RetailerId
  and dateadd(month, -1, b.Period) = c.Period
  and c.[FSL Status] = 'Imputed'
inner join MyTable d
  on c.RetailerId = d.RetailerId
  and dateadd(month, -1, c.Period) = d.Period
  and d.[FSL Status] = 'Imputed'
where a.[FSL Status] = 'Projected'

In the select I put a count distinct, but Count(*) could be enough. You know the data, you choose.

jtjikinw

jtjikinw2#

You can make use of cumulative sum sum() over (order by ...) to identify the required group (3 Imputed + 1 Projected).

cte identify the grp . cte2 counts number of rows within the grp

with cte as
(
  select *, 
         grp = sum(case when Status = 'Imputed'   then 0
                        when Status = 'Projected' then 1
                        end) 
               over (partition by RetailerID 
                         order by Period desc)
  from   yourTable
),
cte2 as
(
  select *, cnt = count(*) over (partition by RetailerID, grp)
  from   cte
)
select *
from   cte2
where  cnt >= 4   -- 1 Projected & 3 Imputed
order by RetailerID, Period

相关问题