-2

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
ekad
  • 14,436
  • 26
  • 44
  • 46
user1904766
  • 49
  • 1
  • 6
  • What is the output you would like to get from the sample table you provided? Please include the time in you sample data since that might be relevant to the query. – MartianCodeHound Jun 17 '15 at 14:13
  • Your question needs better information. Is there a column that specifies the ordering of the rows (SQL tables are inherently unordered)? Can the shifts overlap? What results do you actually want? And, finally, what version of SQL Server are you using? Please add that as a tag to the question. – Gordon Linoff Jun 17 '15 at 14:19
  • Is date actually "1/1x" or is there a full date and time there that can be used? – MartianCodeHound Jun 17 '15 at 14:27
  • It's a full datetime – user1904766 Jun 17 '15 at 14:28
  • Please add a real example with your worst case data, with expected results. It's really not possible to give any suggestions based on "1/1x". – James Z Jun 17 '15 at 14:35

1 Answers1

0

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]
MartianCodeHound
  • 355
  • 1
  • 8