sql - Group by run when there is no run number in data (was Show how changing the length of a production run affects time-to-build) -

it seem there simpler way state problem. please see edit 2, following sample table.

i have number of different products on production line. have date each product entered production. each product has 2 identifiers: item number , serial number have total number of labour hours each product item number , serial number (i.e. can tell how many hours went each object manufactured , average build time each kind of object).

i want determine how (if) varying length of production runs affects average time takes build product (item number). production run sequential production of multiple serial numbers single item number. have historical records going several years production runs varying in length 1 30.

i think achieve this, need able assign 'run id'. me, means building query sorts start date , calculates new unique value @ each change in item number. if knew how that, solve rest of problem on own.

so suggests series of related questions:

1. am thinking right way?
2. if on right track, how generate run id values? calculate , store option, although have (misguided?) preference direct queries. know how generate run numbers in excel, have (misguided?) preference in database.
3. if i'm not on right track, might find track? :)

edit: table structure (simplified) sample data:

autoid    item       serial     startdate    hours     runid (proposed calculation)  1         legend     1234       2010-06-06     10        1  3         legend     1235       2010-06-07      9        1  2         legend     1237       2010-06-08      8        1  4         apex       1236       2010-06-09     12        2  5         apex       1240       2010-06-10     11        2  6         legend     1239       2010-06-11     10        3  7         legend     1238       2010-06-12      8        3 

i have shown start date, serial, , autoid mutually unrelated. have shown expectation labour goes down run length increases (but 'fact' via received wisdom, not data analysis). have shown envision heart of solution, being runid reflects sequential builds of single item. know if runid, group run counts, averages, totals, max, min, etc. in addition, hours/ percentage change start of run. @ point graph trends associated different run lengths either globally across items or on per item basis. (at least think that. might have muck bit, think done.)

edit 2: problem appear be: how 'starting' member (earliest start date) of each run when don't have runid? (the runid shown in sample table not exist , suggesting being able calculate runid potentially viable solution.)

autoid    item   1         legend  4         apex     6         legend  

i'm assuming having learned how find first member of each run able use i've learned find last member of each run , use 2 results other members of each run.

edit 3: version of query uses autoid of first item in run runid units in run. built entirely samples , direction provided simon, has accepted answer. using basis grouping run, can produce variety of run statistics.

select first_product_of_run.autoid runid, run_sibling.autoid itemid, run_sibling.item, run_sibling.serial, run_sibling.startdate, run_sibling.hours (select first_of_run.autoid, first_of_run.item, first_of_run.serial, first_of_run.startdate, first_of_run.hours dbo.production first_of_run left outer join dbo.production earlier_in_run on first_of_run.autoid - 1 = earlier_in_run.autoid , first_of_run.item = earlier_in_run.item (earlier_in_run.autoid null)) first_product_of_run left outer join dbo.production run_sibling on first_product_of_run.item = run_sibling.item , first_product_of_run.autoid run_sibling.autoid , first_product_of_run.startdate product_between.item , first_product_of_run.startdate

could describe table structure more? if "date each product entered production" full time stamp, or if there sequential identifier across products, can write queries identify first , last products of run. that, can assign ids or calculate length of runs.

edit: once you've identified 1,4, , 6 start of run, can use query find other ids in run:

select first_product_of_run.autoid, run_sibling.autoid first_product_of_run left join production run_sibling on first_product_of_run.item = run_sibling.item     , first_product_of_run.autoid <> run_sibling.autoid     , first_product_of_run.startdate < run_sibling.startdate left join production product_between on first_product_of_run.item <> product_between.item     , first_product_of_run.startdate < product_between.startdate     , product_between.startdate < run_sibling.startdate product_between.autoid null 

first_product_of_run can temp table, table variable, or sub-query used find start of run. key where product_between.autoid null. restricts results pairs no different items produced between them.

edit 2, here's how first of each run:

select first_of_run.autoid  ( select product.autoid, product.item, max(previous_product.startdate) previousdate production product left join production previous_product on product.autoid <> previous_product.autoid     , product.startdate > previous_product.startdate group product.autoid, product.item ) first_of_run left join production earlier_in_run     on first_of_run.previousdate = earlier_in_run.startdate     , first_of_run.item = earlier_in_run.item earlier_in_run.autoid null 

it's not pretty, , break if startdate not unique. query simplified adding sequential , unique identifier no gaps. in fact, step necessary if startdate not unique. here's how look:

select first_of_run.autoid production first_of_run left join production earlier_in_run     on (first_of_run.sequence - 1) = earlier_in_run.sequence     , first_of_run.item = earlier_in_run.item earlier_in_run.autoid null 

using outer joins find things aren't still twists brain, it's powerful technique.