stored procedures - Sql Server: Compare multiple dates, remove duplicates and add new column all in one -


what best solution following problem?

so have stored procedure inserts temporary table information so:

    id | name |   date            |  age  | bin |status| description |  warn1     -----------------------------------------------------------------------     01 | abcd |2016-01-01 15:00:00|  17   | 0   | 40   | done        |  0     01 | abcd |2016-01-01 11:00:00|  17   | 0   | 30   | waiting     |  0     01 | abcd |2016-01-01 10:00:00|  17   | 0   | 10   | started     |  0      02 | zxcv |2016-01-02 11:30:00|  18   | 0   | 35   | error       |  0     02 | zxcv |2016-01-02 11:00:00|  18   | 0   | 30   | waiting     |  0     02 | zxcv |2016-01-02 10:00:00|  18   | 0   | 10   | started     |  0      03 | yttr |2016-01-02 12:30:00|  16   | 0   | 30   | waiting     |  0     03 | yttr |2016-01-02 10:00:00|  16   | 0   | 10   | no desc     |  0      04 | huuo |2016-01-02 11:30:00|  17   | 0   | 40   | done        |  0     04 | huuo |2016-01-02 09:00:00|  17   | 0   | 30   | waiting     |  0     04 | huuo |2016-01-02 08:00:00|  17   | 0   | 10   | started     |  0      05 | test |2016-01-03 10:00:00|  10   | 0   | 11   | error       |  0     05 | test |2016-01-03 09:00:00|  10   | 0   | 10   | started     |  0

i have 2 variables @maxstatus , @description, keep information of same id's have status = @maxstatus , description = @description.

for example if @maxstatus = 40 , @description='done' resulting table should be:

id | name |   date            |  age  | bin |status| description |  warn1 ----------------------------------------------------------------------- 01 | abcd |2016-01-01 15:00:00|  17   | 0   | 40   | done        |  0 01 | abcd |2016-01-01 11:00:00|  17   | 0   | 30   | waiting     |  0 01 | abcd |2016-01-01 10:00:00|  17   | 0   | 10   | started     |  0 04 | huuo |2016-01-02 11:30:00|  17   | 0   | 40   | done        |  0 04 | huuo |2016-01-02 09:00:00|  17   | 0   | 30   | waiting     |  0 04 | huuo |2016-01-02 08:00:00|  17   | 0   | 10   | started     |  0

if @maxstatus = 11 , @description='error' resulting table should be:

 id | name |   date            |  age  | bin |status| description |  warn1  -----------------------------------------------------------------------  05 | test |2016-01-03 10:00:00|  10   | 0   | 11   | error       |  0  05 | test |2016-01-03 09:00:00|  10   | 0   | 10   | started     |  0

edit: clarification: sp should return rows of id if has maximum status = @maxstatus

if example @maxstatus = 30 , @description='waiting' , id has maxium status of 30 , description of waiting '03' ; others have maximum status different @maxstatus

 id | name |   date            |  age  | bin |status| description |  warn1  -----------------------------------------------------------------------  03 | yttr |2016-01-02 12:30:00|  16   | 0   | 30   | waiting     |  0  03 | yttr |2016-01-02 10:00:00|  16   | 0   | 10   | no desc     |  0

you can below:

select  *     tbl     a.id in (select b.id tbl b b.description  = @description) ,     status <= @maxstatus

updated

select  *     tbl     a.id in (select b.id tbl b b.description  = @description) ,     a.id in (select c.id (select b.id, max(status) maxstatus tbl b group b.id) c c.maxstatus <= @maxstatus )

-

Comments

Post a Comment

Popular posts from this blog

angular - Is it possible to get native element for formControl? -

i've got angular2 reactive form . created formcontrol s , assigned input fields by [formcontrol]=... . understand creates nativeelement <-> formcontrol link. my question: possible nativeelement formcontrol ? wanna myformcontrol.nativeelement.focus() i can share 1 terrible solution works me. in reactive forms can use either 1) formcontroldirective ts mycontrol = new formcontrol('') template <input type="text" [formcontrol]="mycontrol"> or 2) formcontrolname ts myform: formgroup; constructor(private fb: formbuilder) {} ngoninit() { this.myform = this.fb.group({ foo: '' }); } template <form [formgroup]="myform"> <input type="text" formcontrolname="foo"> </form> so these directives write patch like 1) formcontroldirective const originformcontrolngonchanges = formcontroldirective.prototype.ngonchanges; formcontroldirective.proto
Read more

unity3d - Rotate an object to face an opposite direction -

i have object , b. when click on object b, rotates object b (a faces b). b doesn't face a. need following : when faces b, need face opposite direction. have code rotating @ b. how rotate face opposite direction after that? vector3 targetdirection = target - transform.position; float step = speed * time.deltatime; vector3 newdirection = vector3.rotatetowards (turretdome.transform.forward, targetdirection, step, 0.0f); turretdome.transform.rotation = quaternion.lookrotation (newdirection); you object facing object b, want inverse direction of object after that? objecta.transform.rotation = quaternion.inverse(objecta.transform.rotation) but lets assume example turretdome object a, (negate direction): turretdome.transform.rotation = quaternion.lookrotation (-newdirection); naturally, both of these snippets not show how smoothen rotation, seem know how use time.deltatime. just incase unsure, quaternion.lerp this
Read more

javascript - Why jQuery Select box change event is now working? -

i working on product filters user select select box according his/her preference not getting selected value user in jquery. html code:- <select name="product_filter" id="product_filter"> <option value="price_low_first">price : low high</option> <option value="price_high_first">price : high low</option> <option value="latest">latest</option> <option value="popular">most popular</option> </select> jquery code: $(function () { $("#product_filter").change(function () { alert("hi") var selectedtext = $(this).find("option:selected").text(); var selectedvalue = $(this).val(); alert("selected text: " + selectedtext + " value: " + selectedvalue); }); }); hi believe code perfect. once please check jquery.min.js
Read more