I am trying to figure out how to write a query that will give me the correct historical data between dates. But only using sql. I know it is possible coding a loop, but I'm not sure if this is possible in a SQL query. Dates: DD/MM/YYYY

An Example of Data

With a simple query

SELECT ID, Points, MIN(Date), MAX(Date)
FROM table
GROUP BY ID,POINTS

The Min date for 10 points would be 01/01/2018, and the Max Date would be 04/01/2021. Which would be wrong in this instance. As It should be:

I was thinking of using LAG, but need some ideas here. What I haven't told you is there is a record per day. So I would need to group until a change of points. This is to create a view from the data that I already have.

CodePudding user response：

It looks like - for your sample data set - the following lead should suffice:

select id, points, date as MinDate, 
  IsNull(DateAdd(day, -1, Lead(Date,1) over(partition by Id order by Date)), Date) as MaxDate
from t

Example Fiddle

CodePudding user response：

I'm guessing you want the MAX date to be 1 month before the next MIN date.

You can use the window function LEAD.
After calculating a ranking for the Points.

WITH CTE AS (
  SELECT *, 
  Rnk = ROW_NUMBER() OVER (PARTITION BY ID ORDER BY [Date]) 
        ROW_NUMBER() OVER (PARTITION BY ID, Points ORDER BY [Date] DESC) 
  FROM your_table
)
SELECT ID, Points
, MIN([Date]) AS [Min Date] 
, COALESCE(DATEADD(month, -1, LEAD(MIN([Date])) OVER (PARTITION BY ID ORDER BY MIN([Date]))), MAX([Date])) AS [Max Date] 
FROM CTE
GROUP BY ID, Points, Rnk;

ID	Points	Min Date	Max Date
1	10	2018-01-01	2019-01-01
1	20	2019-02-01	2020-02-01
1	25	2020-03-01	2021-03-01
1	10	2021-04-01	2021-04-01

Test on db<>fiddle here

CodePudding user response：

We can do this by creating two tables one with the minimum and one with the maximum date for each grouping and then combining them

CREATE TABLE dataa(
    id INT,
    points INT,
    ddate DATE);
INSERT INTO dataa values(1 , 10 ,'2018-10-01');
INSERT INTO dataa values(1 , 20 ,'2019-01-02');
INSERT INTO dataa values(1 , 25 ,'2020-01-03');
INSERT INTO dataa values(1 , 10 ,'2021-01-04');
SELECT 
    mi.id, mi.points,mi.date minDate, ma.date maxDate 
FROM
    (select id, points, min(ddate) date from dataa group by id,points) mi 
JOIN
    (select id, points, max(ddate) date from dataa group by id,points) ma 
ON
    mi.id = ma.id
AND
    mi.points = ma.points;
DROP TABLE dataa;

this gives the following output

 ------ -------- ------------ ------------ 
| id   | points | minDate    | maxDate    |
 ------ -------- ------------ ------------ 
|    1 |     10 | 2018-10-01 | 2021-01-04 |
|    1 |     20 | 2019-01-02 | 2019-01-02 |
|    1 |     25 | 2020-01-03 | 2020-01-03 |
 ------ -------- ------------ ------------ 

I've used the default date formatting. This could be modified if you wish.