Batch Grouping Stored Procedure

Job ID: 36085643

Budget: $30 – $250 USD

I need a Microsoft SQL Query to split data into multiple groups (batches) of data.

Requirements
- Batch can only have one vendor in it.
- Date Range in Batch can only be a maximum of 30 business days.
- There are multiple entries for the same date.

Goal
- The least number of batches possible.
- Have as many items in batches of 5 items or more.

Batch 1 - 5 items
Batch 2 - 5 items
Batch 3 - 5 items

is better than

Batch 1 - 8 items
Batch 2 - 4 items
Batch 3 - 3 items

When we batch the data manually, we typically start at the last date and determine the first day by subtracting 30 days. Then we determine the last date of the next batch by seeing what the next highest date is that is not in the first batch.

We also look at the data to determine if this is the best option. Sometimes with the data, it makes more sense have a batch of one item in the middle, so we end up with fewer total batches.

We use this site to determine weekdays - https://www.timeanddate.com/date/weekdayadd.html

It would be beneficial to load a date table into our SQL Server.

Please see examples