sql - return value of stored procedure based on different rules -

-

i writing stored procedure values based on following tables.

enter image description here

i filling apartment flats according nationality of buyers.

the stored procedure has return next non-occupied flat based on following rules:

  • if new entry comes, need suggest non-occupied flat next occupied-flat having same nationality of new person

  • if no match found above condition, allocate first flat in floor no flats occupied

  • if no match found above condition, allocate flat having at-least 2 empty flats on both sides

  • if no match found above condition, allocate first flat empty according sort order

note:

  • each flat identified combination of floor & flat no

  • sort order flat , floors while searching should 1 n

sample input: name: randy nationality: brazil

sample output: floor:1 flat no: 4 (w.r.t. attached image)

the key create columns each of criteria, i.e. 1 column if next door flat owner has same nationality, column if floor empty.

you can take criteria , place them within order of row_number() function flats in order defined. key part in below query this:

rownumber = row_number() over(order previsnationalitymatch desc,                                          nextisnationalitymatch desc,                                          emptyfloor desc,                                          emptyflatseitherside desc,                                         floor,                                          flatno)

the 4 columns (previsnationalitymatch, nextisnationalitymatch, emptyfloor', 'emptyflatseitherside), bit fields, if row exists previous flat owned of same nationality ranked 1 row_number function, otherwise looks if next flat owned of same nationality (i added rule seemed logical removed removing order by), , on , on until left sorting floor , flat no.

declare @newownernationality varchar(20) = 'brazil'; flatownernationality (   select  flatmaster.floor,              flatmaster.flatno,              flatmaster.isoccupied,             isnationalitymatch = case when ownermaster.ownernationality = @newownernationality 1 else 0 end        flatmaster             left join ownermaster                 on ownermaster.ownername = flatmaster.ownername ), flats (   select  flatmaster.floor,             flatmaster.flatno,             flatmaster.isoccupied,             emptyflatseitherside = case when prevflat.isoccupied = 'no' , nextflat.isoccupied  = 'no' 1 else 0 end,             emptyfloor = case when count(case when flatmaster.isoccupied = 'yes' 1 end) over(partition flatmaster.floor) = 0 1 else 0 end,             previsnationalitymatch = isnull(prevflat.isnationalitymatch, 0),             nextisnationalitymatch = isnull(nextflat.isnationalitymatch, 0)        flatmaster             left join flatownernationality prevflat                 on prevflat.floor = flatmaster.floor                 , prevflat.flatno = flatmaster.flatno - 1             left join flatownernationality nextflat                 on nextflat.floor = flatmaster.floor                 , nextflat.flatno = flatmaster.flatno + 1 ), rankedflats (   select  *,             rownumber = row_number() over(order previsnationalitymatch desc,                                                      nextisnationalitymatch desc,                                                      emptyfloor desc,                                                      emptyflatseitherside desc,                                                     floor,                                                      flatno)        flats       isoccupied = 'no' ) select  floor,         flatno,         matchedon = case when previsnationalitymatch = 1 'first flat after same nationality owner'                         when nextisnationalitymatch = 1 'first flat before same nationality owner'                         when emptyfloor = 1 'no nationality match, placed on empty floor'                         when emptyflatseitherside = 1 'next flat empty flats either side'                         else 'first available flat'                     end    rankedflats   rownumber = 1;

brazil example - floor 1, flat 4

england example - floor 1, flat 2

spain example - floor 2, flat 1

edit

declare @newownernationality varchar(20) = 'brazil';  flatownernationality (   select  flatmaster.floor,              flatmaster.flatno,              flatmaster.isoccupied,             isnationalitymatch = case when ownermaster.ownernationality = @newownernationality 1 else 0 end        flatmaster             left join ownermaster                 on ownermaster.ownername = flatmaster.ownername ), flats (   select  flatmaster.floor,             flatmaster.flatno,             flatmaster.isoccupied,             emptyflatseitherside = case when prevflat.isoccupied = 'no' , nextflat.isoccupied  = 'no' , prevflat2.isoccupied = 'no' , nextflat2.isoccupied  = 'no' 1 else 0 end,             emptyfloor = case when count(case when flatmaster.isoccupied = 'yes' 1 end) over(partition flatmaster.floor) = 0 1 else 0 end,             previsnationalitymatch = isnull(prevflat.isnationalitymatch, 0),             nextisnationalitymatch = isnull(nextflat.isnationalitymatch, 0)        flatmaster             left join flatownernationality prevflat                 on prevflat.floor = flatmaster.floor                 , prevflat.flatno = flatmaster.flatno - 1             left join flatownernationality nextflat                 on nextflat.floor = flatmaster.floor                 , nextflat.flatno = flatmaster.flatno + 1             left join flatmaster prevflat2                 on prevflat2.floor = flatmaster.floor                 , prevflat2.flatno = flatmaster.flatno - 2             left join flatmaster nextflat2                 on nextflat2.floor = flatmaster.floor                 , nextflat2.flatno = flatmaster.flatno + 2  ), rankedflats (   select  *,             rownumber = row_number() over(order previsnationalitymatch desc,                                                      nextisnationalitymatch desc,                                                      emptyfloor desc,                                                      emptyflatseitherside desc,                                                     floor,                                                      flatno)        flats       isoccupied = 'no' ) select  floor,         flatno,         matchedon = case when previsnationalitymatch = 1 'first flat after same nationality owner'                         when nextisnationalitymatch = 1 'first flat before same nationality owner'                         when emptyfloor = 1 'no nationality match, placed on empty floor'                         when emptyflatseitherside = 1 'next flat empty flats either side'                         else 'first available flat'                     end    rankedflats   rownumber = 1;

Comments

Post a Comment

Popular posts from this blog

c++ - Function signature as a function template parameter -

-
i'd use function signature template argument. works great classes, when try same trick function templates, msvc throws error: error c2768: 'func' : illegal use of explicit template arguments here's code: template <typename signature> void func(); template <typename r, typename a1> void func<r(a1)>(); what should make work? you cannot partially specialize function template, not supported language. can create partially specialized class template static member function, , possibly trampoline function instantiate class template , invoke static function. something this: namespace detail { template<typename signature> struct helper; template<typename r, typename a1> struct helper<r(a1)> { static void call() { // stuff r , a1... } }; } template<typename signature> void func() { detail::helper<signature>::call(); }
Read more

algorithm - What are some ways to combine a number of (potentially incompatible) sorted sub-sets of a total set into a (partial) ordering of the total set? -

-
i have sorting problem i'm pretty sure doesn't have canonical "answer" - has number of different approaches, each pros/cons. i'm interested in hearing different approaches. suppose bunch of people {p_i | i=1,...,m} ranking favorite ice cream flavors. in total there n different flavors, single person p_i ranks n_i << n flavors or familiar with. i'm interested in combining these sub-rankings plausible overall ranking of n flavors. for concrete situation: m , n both 1000 (by coincidence), , each n_i 20 . can assume there's enough overlap in flavors people rank such no flavor "isolated". again, i'm interested in hearing different ways approach this, if there isn't single clear answer. thanks! one way approach problem construct directed cyclic graph representing of constraints on ordering of elements. nodes in graph represent different elements compared , have edge first node second node if had ranked first e...
Read more

How to call a javascript function after the page loads with a chrome extension? -

-
there's function on page want call after loads. not own page, can't insert html it. can please provide specific examples how chrome extension or greasemonkey? (function submitaction() ). both chrome extensions , greasemonkey offer ways specify when script inject if executed. in chrome extension, you set in manifest : "content_scripts": [ { "run_at" : "document_idle", in greasemonkey compatible userscripts, set in metadata // @run-at document-end in both cases, have add script if want ensure resources loaded when function executed : document.addeventlistener('load', submitaction);
Read more