Grouping by similar categories over time

Question

I looked around for awhile, but couldn't find anything.

I have a table that looks like this:

DATE       | Shift | Parts Used

1/1/15 1:15.....1........1

1/1/15 2:06.....1........2

1/1/15 3:45.....1........3

1/1/15 7:33.....2........1

1/1/15 8:14.....2........2

1/1/15 9:00.....2........3

1/1/15 23:01....1........1

1/1/15 23:55....1........2

I would like to group by each individual shift. UNFORTUNATELY shift one can sometimes end on a monday morning and start again on monday night (sometimes our twelve hour shifts are not exactly 12 hours). Is there a way I can group by shifts individually, essentially grouping by shift until shift changes even if my day is the same (my actual column contains a time too)? I could write a loop to analyze the data, but getting it from sql would be great.

The date is orders the rows. Shifts cannot overlap.

Hopefully my result would be something like

DATE | Shift | AVG parts used

1/1/15.....1........2

1/1/15.....2........2

1/1/15.....1........1.5

Edit****** Worst case scenario with expected results

DATE | Shift | AVG parts used

1/1/15.....1........1.8

1/1/15.....2........2

Answer 1

I'm assuming that shift 1 is suppose to start at midnight and shift 2 starts at noon, but its sometimes off by some amount of time. This query should work if those assumptions are true. I've made a variable called @ShiftFudgeHours to account for how off the shift start times usually are. Adjust that based on your numbers. From your sample 1 hours looks sufficient, but you could easily increase it.

declare @shiftFudgeHours int = 1

select convert(date, DATEADD(hh, @shiftFudgeHours, [date]) ) as [date]
    ,[shift]
    ,avg(convert(float, parts)) as AvgParts
from Table1
group by convert(date, DATEADD(hh, @shiftFudgeHours, [date]) ), [shift]
order by convert(date, DATEADD(hh, @shiftFudgeHours, [date]) ), [shift]