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;
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
);
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