| COMPANY | Transaction ID |Transaction Date | GROUP |---------------------|------------------|------------------|---------- | Company A | t_0001 | 01-01-2014 | 1 | Company A | t_0002 | 02-01-2014 | 1 | Company A | t_0003 | 04-01-2014 | 1 | Company A | t_0003 | 10-01-2014 | 2 | Company B | t_0004 | 02-01-2014 | 1 | Company B | t_0005 | 02-01-2014 | 1 | Company C | t_0006 | 03-01-2014 | 1 | Company C | t_0007 | 05-01-2014 | 2where the transactions and dates are firstly group into companies. The transactions within the company are sorted from the earliest to the latest. The transactions are checked, row by row, if the previous transaction was performed less than 3 days ago in a moving window period. For example, t_0002 and t_0001 are less than 3 days apart so they fall under group 1. t_0003 and t_0002 are less than 3 days apart so they fall under group 1 even though t_0003 and t_0003 are >= 3 days apart. I figured the way to go about doing this is to group the data by companies first, following by sorting the transactions by the dates, but I got stuck after this. Like what methods are there I could use to produce this results? Any help on this? P.S. I am using SQL Server 2014.
