0

I want to extract the last 35 days prior to the 7days before the max date in the dataset. Here the analysis is for days and not weeks. For example: Max date is 10 March 2022 so I want date range to be between 29 Jan to 3 March. I want to repeat this every 3 months and automate it, instead of writing the code every time.

Example data

date            ids

23/01/2022      12345
29/01/2022      13452
30/01/2022      21345
25/01/2022      13482
24/01/2022      12245
04/02/2022      13052
06/02/2022      11145
10/02/2022      13452
06/02/2022      12945
07/02/2022      13222
09/02/2022      14345
11/02/2022      13452
12/02/2022      12245
12/02/2022      13432
13/02/2022      13455
12/02/2022      12344
03/03/2022      13452
06/02/2022      12310
08/02/2022      17893
09/03/2022      10987
10/03/2022      11346

Expected Output

date            ids

29/01/2022      13452
30/01/2022      21345
04/02/2022      13052
06/02/2022      11145
10/02/2022      13452
06/02/2022      12945
07/02/2022      13222
09/02/2022      14345
11/02/2022      13452
12/02/2022      12245
12/02/2022      13432
13/02/2022      13455
12/02/2022      12344
03/03/2022      13452
06/02/2022      12310
08/02/2022      17893
Progman
  • 16,827
  • 6
  • 33
  • 48
Nithya
  • 33
  • 4
  • Just use WHERE theDate BETWEEN maxDate-41 and maxDate-7. BTW Jan 29 through Mar 3 is 34 days in non-leap year. – Fred Jan 27 '23 at 18:23
  • You can use a CTE or a subquery in the FROM clause to SELECT MAX(theDate) AS maxDate FROM theTable) and CROSS JOIN to that, or even use a Scalar Subquery (twice) within the BETWEEN. – Fred Jan 27 '23 at 18:31

0 Answers0