Home > front end >  Locate items with no changes over a period of time
Locate items with no changes over a period of time

Time:04-10

Is there a T-SQL query that would allow me to see products that have had no changes in quantity for the past 4 days?

Product Date Quantity
Coke 2022-04-06 0
Coke 2022-04-07 0
Coke 2022-04-08 0
Coke 2022-04-09 0
Pepsi 2022-04-06 0
Pepsi 2022-04-07 1
Pepsi 2022-04-08 1
Pepsi 2022-04-09 1
Sprite 2022-04-06 1
Sprite 2022-04-07 0
Sprite 2022-04-08 0
Sprite 2022-04-09 1
Tango 2022-04-05 2
Tango 2022-04-06 1
Tango 2022-04-07 1
Tango 2022-04-08 1
Tango 2022-04-09 1

Result

Product Quantity
Coke 0

Edit: this is how I started off my code

DECLARE @CurrentDate date = GETDATE();
DECLARE @PreviousDate date = DATEADD (Day, -4, @CurrentDate)
DECLARE @Quantity AS Decimal(8,5)
DECLARE @Count AS int

SELECT  
    [Date], [Product], [Quantity]
FROM 
    Table1
WHERE
    [Date] = @PreviousDate 
    AND [Product] IN (SELECT [Product]
                      FROM Table1
                      WHERE [DATE] BETWEEN @PreviousDate AND @CurrentDate)

CodePudding user response:

If I understand correctly you could use first_value() analytic function to partition the products accordingly and check the first and last quantities match,

select distinct product, quantity from (
    select *, 
        First_Value(quantity) over(partition by product order by date) f, 
        First_Value(quantity) over(partition by product order by date desc) l
    from t
    where date >= DateAdd(day,-4,GetDate())
)t
where f = l;

Demo Fiddle

Edit

Seems I didn't quite understand but do now (I hope) so how about an approach using not exists?

with cv as (
    select *, 
        First_Value(quantity) over(partition by product order by date desc) currentValue,
        Count(*) over(partition by product) qty
    from t
    where date >= DateAdd(day,-4,GetDate())
)
select distinct product, quantity
from cv t
where qty=4 and not exists (
  select * from cv t2
    where t2.product = t.product 
    and t2.quantity != currentValue
);

Demo Fiddle (2)

CodePudding user response:

You select all records from @PreviousDate (WHERE [Date] = @PreviousDate) and which have modifications (AND [Product] IN (...))

SELECT  
    t1.[Date], t1.[Product], t1.[Quantity], t2.Quantity  "PreviousQuantity"
FROM 
    Table1 t1
LEFT JOIN Table1 t2 On t1.Product = t2.Product and t2.[Date] = @PreviousDate
WHERE t1.[Date] = @CurrentDate
  and t1.Quantity = t2.Quantity

But this will show nothing because @PreviousDate has the value 2022-04-05 (when @CurrentDate=2022-04-09)

So, you might need to change @PreviousDate to: DECLARE @PreviousDate date = DATEADD (Day, -3, @CurrentDate)

see: DBFIDDLE

  • Related