c# - How to design this algorithm for SQL search? -

c# - How to design this algorithm for SQL search? -

there's set of values stored in ms sql db table. these values indicate,

a. serial number of item

b. capacity of item

c. availability of item

i need design search function has next capabilities , find out item or combined items match/nearer given capacity,

e.g. given capacity 8. available items of capacity 10, 6, 4, 2. there few items above capacity. in best case, result should homecoming items capacity 10 before deciding approach combining algo-part. in event no single item found satisfy given capacity, algo must proceed combine, yet if adequate number of items available combine...

rule of search : combined items must adjacent serial number) combined capacity should exact or near match (greater than) given capacity items must available (boolean value check)

how should approach this? tough part finding total capacity of combined items while adjacent each other. thinking of each pulling info c# , search or entire search within sql via stored procedure. ears hear , seek out suggestions improve performance , satisfies main criteria.

in sentiment stored procedure best bet. set write temp table or table variable first part (a perfect, or possible match, ie > 8) check see # of rows in temp table, 0 == didn't find it, seek find number of items satisfy match.

perhaps in sec part of search items have count lower 8 sorted in descending order of capacity. 1 time identify 1 has highest, (lets phone call @ok_but_not_complete_match_serialnum) @ items below or above 1 until have plenty capacity.

you utilize couple of temp tables , temp indexes create run quite fast.

in query utilize running totals of capacity ordered part number identify items match request.

so assuming serial numbers have discernable order, sec part might work

assuming @ok_but_not_complete_match_serialnum serial num of item identified has highest (but not complete) match,

select * parts serialnum between @ok_but_not_complete_match_serialnum - 10 , @ok_but_not_perfect_match_serialnum + 10

i picked 10 arbitrary range above , beyond item, maybe smaller # desireable. if querying running totals doesn't # need, maintain checking next item serialnum until you've satisfied need.

c# sql-server algorithm search