Find MAX(SalesDate) of a customer by Sales Rep-CodePudding

Need to find Min sales date and Max sales date by Sales Rep for customers. For a customer, the sales rep gets change over the period of time. So Max sale date will be Min sales date of next sales rep of the customer.

CREATE TABLE REPSALES (Customer_Number NVARCHAR(10),Document_Rep   NVARCHAR(10) ,document_date  DATE)

INSERT INTO REPSALES VALUES ( ' 5','DD','2016-01-01')
INSERT INTO REPSALES VALUES ( ' 5','DD','2016-05-01')
INSERT INTO REPSALES VALUES ( ' 5','DD','2016-11-01')
INSERT INTO REPSALES VALUES ( ' 5','LL','2017-12-01')
INSERT INTO REPSALES VALUES ( ' 5','LL','2018-05-01')
INSERT INTO REPSALES VALUES ( ' 5','LL','2021-12-01')
INSERT INTO REPSALES VALUES ( ' 5','BB','2022-01-01')
INSERT INTO REPSALES VALUES ( ' 5','BB','2022-04-01')
INSERT INTO REPSALES VALUES ( ' 4','AA','2017-02-01')
INSERT INTO REPSALES VALUES ( ' 4','AA','2021-08-01')
INSERT INTO REPSALES VALUES ( ' 4','CC','2021-01-01')
INSERT INTO REPSALES VALUES ( ' 4','CC','2021-06-01')

Expected output:

Customer_Number	Document_Rep	MinSalesDate	MaxSaledate
4	AA	01/02/2017	31/12/2020
4	CC	01/01/2021	31/12/2022
5	DD	01/01/2016	30/11/2017
5	LL	01/12/2017	31/12/2021
5	BB	01/01/2022	31/12/2022

MinSalesDate is correct but Maxsalesdate is not coming correct. Can you please how to achieve this result. I have written this SQL query

select 
    s.Customer_Number,Document_Rep, 
    DATEADD(month, DATEDIFF(month, 0, min(document_date)), 0) MinSalesDate,
    min(MaxSaleDate.Maxsalesdate)-1 MaxSaledate
from
REPSALES s
inner join  (
        select Customer_Number,DATEADD(month, DATEDIFF(month, 0, min(document_date)), 0) Maxsalesdate,
        row_number() over(partition by Customer_Number 
        order by DATEADD(month, DATEDIFF(month, 0, min(document_date)), 0) ) seq
        from REPSALES 
        group by Customer_Number,Document_Rep)MaxSaleDate 
on MaxSaleDate.Customer_Number =s.Customer_Number
where  seq >1
group by s.Customer_Number,Document_Rep
ORDER BY s.Customer_Number,min(document_date)

CodePudding user response：

Use lead() to get the next minimum document_date by Customer_Number. coalesce() to return the date or 2022-12-31 (which you didn't explain how this date comes about) if it is null.

with cte as
(
    select Customer_Number, Document_Rep, 
           MinSalesDate = min(document_date), 
           MaxSalesDate = max(document_date),
           NextMinDate = lead(min(document_date)) over (partition by Customer_Number 
                                                         order by min(document_date))
    from   REPSALES
    group by Customer_Number, Document_Rep
) 
select Customer_Number, Document_Rep, MinSalesDate, 
       MaxSalesDate = coalesce(dateadd(day, -1, NextMinDate), '20221231')
from   cte
order by Customer_Number, Document_Rep