In this scenario, I’ll have a table that has column that is Fruit, this column will have about 1000 + distinct entries in it an a entry on a per day basis. What I’m trying to do is for each distinct type of Fruit, I want to find where the day has sold 2x the amount of fruit from the previous two days combined.

So for example, if on day 1&2, there was 1 apple sold each day I would have a total of 2 sold, I want to know whenever the following day has sold 2x the previous, so if day 3 has 4 apples sold then this is a positive result, however if 3 were sold, then this would be a negative result. I’ve but together a very clunky and slow cursor that I’m not confident in the results. One of the things to take into consideration is the data always need to be ordered by date desc, sometime the data isn’t populated everyday an the ID will be out of order.

Here is a test query that has the final results I’m looking for.

declare @testTable table(Id int not null identity(1,1),Fruit nvarchar(10),Sold int, DateSold date)

DECLARE @StartDate datetime = '2024-01-22';
DECLARE @EndDate   datetime = Dateadd(day,100,@StartDate);

WITH theDates AS
     (SELECT @StartDate as theDate
      UNION ALL
      SELECT DATEADD(day, 1, theDate)
        FROM theDates
       WHERE DATEADD(day, 1, theDate) <= @EndDate
     )
     insert into @testTable(Fruit,Sold,DateSold)
SELECT 'Apple',90+ROW_NUMBER() OVER(ORDER BY theDate),theDate as theValue
  FROM theDates
  union 
  SELECT 'Orange',90+ROW_NUMBER() OVER(ORDER BY theDate),theDate as theValue
  FROM theDates
    union 
  SELECT 'Pears',90+ROW_NUMBER() OVER(ORDER BY theDate),theDate as theValue
  FROM theDates
    union 
  SELECT 'Plums',90+ROW_NUMBER() OVER(ORDER BY theDate),theDate as theValue
  FROM theDates
OPTION (MAXRECURSION 0)

declare @appleRow int = (SELECT TOP 1 Id FROM @testTable where fruit = 'Apple' ORDER BY NEWID());
declare @ornageRow int = (SELECT TOP 1 Id FROM @testTable where fruit = 'Orange' ORDER BY NEWID());
declare @pearRow int = (SELECT TOP 1 Id FROM @testTable where fruit = 'Pears' ORDER BY NEWID());
declare @plumRow int = (SELECT TOP 1 Id FROM @testTable where fruit = 'Plums' ORDER BY NEWID());

update @testTable
set Sold = Sold + 250
where Id in (@appleRow,@ornageRow,@pearRow,@plumRow);

select * from @testTable;

--these are the ids am looking for
select * from @testTable
where Id in (@appleRow,@ornageRow,@pearRow,@plumRow);