Partitioning Data in a Rolling 12 month window

Analytics
Highlighted
Junior Supporter

Re: Partitioning Data in a Rolling 12 month window

@newb1 @dnoeth

I added dnoeth's example to the sample data and got the following when I reran:

truck sts sts_dt end_date

AAG191D2015-04-192016-04-19
AAG191D2016-02-192016-04-19
AAG191D2016-05-082017-02-19
AAG191D2016-09-012017-02-19
AAG191D2017-03-022018-03-02
AAG191D2017-06-122018-03-02
AAG191D2017-08-202018-03-02
AAG191D2018-02-222018-03-02

So dnoeth has a point, but it depends on when or how you want to reset the 12 month window.  Since May 2016 is past the end_date of April 2016, the end date has reset, but it resets to February of 2017, keying off of the Feb 2016 status rather than keying off of the May 2016 status.

If that reset behavior is acceptable, then you're good, but if not, then I may need to turn in my solved badge.

 

Junior Contributor

Re: Partitioning Data in a Rolling 12 month window

There was a very similar question on StackOverflow yesterday, as the number of rows is limited you might try a recursive query over a Volatile Table