How to get the 5 most recent dates by customer?-CodePudding

I have a query in Microsoft SQL Server Management Studio 18 and I am trying to show the 5 most recent dates for each customer.

Here is the query I use:

SELECT 
    Customer, Plant, ForecastDate  
FROM 
    (SELECT 
         *, 
         ROW_NUMBER() OVER (PARTITION BY Plant, ForecastDate ORDER BY Customer DESC) AS ROW_NUM 
     FROM 
         table) AS T
WHERE 
    ROW_NUM = 1

Here is the output of this query:

Output

This is what I would like the output to look like:

Desired output

Here is some Consumable sample data

I am dealing with weekly forecasts that my customers give to me that changes every week or so. I upload the data into SQL Server. I am trying to to compare and contrast the Quantity to other forecast dates. This is why I want the past 5 dates. I have multiple rows on the same date but I need the past date as well because I am trying to compare past forecasts to each other.

CodePudding user response：

You're on the right track.

DECLARE @Data TABLE (Customer VARCHAR(3), Plant NVARCHAR(20), ForcastDate DATE)
INSERT INTO @Data (Customer, Plant, ForcastDate) VALUES
('ACA', '1100', '2022-11-21'),('ACA', '1100', '2022-11-22'),('ACA', '1100', '2022-11-23'),('ACA', '1100', '2022-11-24'),('ACA', '1100', '2022-11-25'),
('ACA', '1200', '2022-11-21'),('ACA', '1200', '2022-11-22'),('ACA', '1200', '2022-11-23'),('ACA', '1200', '2022-11-24'),('ACA', '1200', '2022-11-25'),
('ACA', '1300', '2022-11-21'),('ACA', '1300', '2022-11-22'),('ACA', '1300', '2022-11-23'),('ACA', '1300', '2022-11-24'),('ACA', '1300', '2022-11-25'),
('ACA', '1400', '2022-11-21'),('ACA', '1400', '2022-11-22'),('ACA', '1400', '2022-11-23'),('ACA', '1400', '2022-11-24'),('ACA', '1400', '2022-11-25'),
('ACA', '1500', '2022-11-21'),('ACA', '1500', '2022-11-22'),('ACA', '1500', '2022-11-23'),('ACA', '1500', '2022-11-24'),('ACA', '1500', '2022-11-25'),
('ACA', '1600', '2022-11-21'),('ACA', '1600', '2022-11-22'),('ACA', '1600', '2022-11-23'),('ACA', '1600', '2022-11-24'),('ACA', '1600', '2022-11-25'),
('ACA', '1700', '2022-11-21'),('ACA', '1700', '2022-11-22'),('ACA', '1700', '2022-11-23'),('ACA', '1700', '2022-11-24'),('ACA', '1700', '2022-11-25'),
('ACA', '1100', '2022-11-25'),('ACA', '1100', '2022-11-27'),('ACA', '1100', '2022-11-28'),('ACA', '1100', '2022-11-29'),('ACA', '1100', '2022-11-30'),
('ACA', '1200', '2022-11-25'),('ACA', '1200', '2022-11-27'),('ACA', '1200', '2022-11-28'),('ACA', '1200', '2022-11-29'),('ACA', '1200', '2022-11-30'),
('ACA', '1300', '2022-11-25'),('ACA', '1300', '2022-11-27'),('ACA', '1300', '2022-11-28'),('ACA', '1300', '2022-11-29'),('ACA', '1300', '2022-11-30'),
('ACA', '1400', '2022-11-25'),('ACA', '1400', '2022-11-27'),('ACA', '1400', '2022-11-28'),('ACA', '1400', '2022-11-29'),('ACA', '1400', '2022-11-30'),
('ACA', '1500', '2022-11-25'),('ACA', '1500', '2022-11-27'),('ACA', '1500', '2022-11-28'),('ACA', '1500', '2022-11-29'),('ACA', '1500', '2022-11-30'),
('ACA', '1600', '2022-11-25'),('ACA', '1600', '2022-11-27'),('ACA', '1600', '2022-11-28'),('ACA', '1600', '2022-11-29'),('ACA', '1600', '2022-11-30'),
('ACA', '1700', '2022-11-25'),('ACA', '1700', '2022-11-27'),('ACA', '1700', '2022-11-28'),('ACA', '1700', '2022-11-29'),('ACA', '1700', '2022-11-30')

This is just some sample data in a table variable for portability.

SELECT *
  FROM 
       (
        SELECT *, ROW_NUMBER() OVER (PARTITION BY Customer, Plant ORDER BY ForcastDate DESC) AS rn
          FROM @Data
       ) a
 WHERE rn <= 5

I wasn't sure if you wanted the most recent five days per plant, or just per customer. This is per customer and plant.

SELECT *
  FROM 
       (
        SELECT *, ROW_NUMBER() OVER (PARTITION BY Customer ORDER BY ForcastDate DESC) AS rn
          FROM @Data
       ) a
 WHERE rn <= 5

This is per customer. Note how the PARTITION BY only contains Customer now.

CodePudding user response：

Figured it out by using a view. That way it removed the multiple rows that had the same dates. Here is that code I used:


SELECT Customer, Plant, ForecastDate
  FROM 
       (
        SELECT *, ROW_NUMBER() OVER (PARTITION BY Customer, Plant ORDER BY ForecastDate DESC) AS rn
          FROM View
       ) a
WHERE rn <= 5
order by Customer,Plant,ForecastDate desc