How to get the difference in dates in SQL Server -


i'm having trouble writing query difference between updatedate , creationdate of 2 records if id lowets , difference between recent , second recent updatedate. here's query:

select      a.id, a.requestid, b.krstatus, b.crdate , b.updatedate,      datediff (hour, b.crdate, b.updatedate) timedifference, case when a.id = (select max(a.id) [dbo].[krdocs_hist] a.requestid = 1)     'yes'     else 'no' end ismax, case when a.id = (select min(a.id) [dbo].[krdocs_hist] a.requestid = 1)     'yes'     else 'no' end ismi [dbo].[krdocs_hist] a, [dbo].krdocs_details_hist b      a.requestid = b.requestid     , a.id = b.id      , a.requestid = 1 order b.requestid

here's current result: enter image description here

what i'd last possible record, check see if there existing 1 before it. if there wasn't compare updatedate , crdate (updatedate minus crdate. if there record before want updatedate minus previous updatedate.

using query:

select b.id, b.requestid, b.updatedate, b.krstatus [dbo].[krdocs_details_hist] b b.requestid = 1

has result: enter image description here

and using query:

select a.* [dbo].[krdocs_hist] requestid = 1

has result: enter image description here

update since lag available sql 2012, can use below:

select         id,          requestid,          crdate,          updatedate,          krstatus,          datediff(hour, previousupdatedate, updatedate) timedifference      (select          id,          requestid,          crdate,          updatedate,          krstatus,          lag(updatedate, 1, crdate) on (order year(id)) previousupdatedate      [dbo].krdocs_details_hist) tmp

i think can try this:

 select      case          when count(*) <= 1  datediff(hour,              (select crdate [dbo].krdocs_details_hist),             (select updatedate [dbo].krdocs_details_hist))         when count(*) > 1 datediff(hour,              (select max(updatedate) [dbo].krdocs_details_hist updatedate < ( select max(updatedate) [dbo].krdocs_details_hist)),             (select max(updatedate) [dbo].krdocs_details_hist))     end timedifference  [dbo].krdocs_details_hist

Comments

Post a Comment

Popular posts from this blog

php - Vagrant up error - Uncaught Reflection Exception: Class DOMDocument does not exist -

i've set bento/ubuntu-16.04 vagrant box using php 5.6, , getting following error quite few times when try , run shell script: "uncaught reflection exception: class domdocument not exist". i've tried adding following top of script (not together), each no success: "sudo apt-get install php5.6-xml", "sudo apt-get install php5-xml", "sudo apt-get install php-dom" i've tried resetting server "sudo service apache2 restart". does have suggestions?
Read more

vue.js - Create hooks for automated testing -

Image
qa guys complained can't automate frontend testing. because our html looks same outside. lazy hack did this <template> <div :role="$options.name"> ... </div> </template> <script> export default { name: 'vmcomponentname' } </script> which takes name script , applies html. in browser get: <div role="vmcomponentname"> ... </div> line :role="$options.name" goes in every component. is there dry er solutions? please share. additional details i'll explain in details looking for. imagine have vmusercreate form creates user. test case can create user . to automatically test without role, have use following selector: .wrapper > .wrapper > .wrapper > .submit-button the test extremely brittle (will break lot). if use roles can use [role="vmusercreateform"] .submit-button selector. many folds less brittle. so basically, i'm ...
Read more

Add new key value to json node in java -

i have json file : { "id":1, "name":"abc", "addressdetails": { "city":"newyork" } } i wanted add 1 more key-value ("pincode" : "414141") node 'addressdetails'. i tried using : objectmapper mapper = new objectmapper(); jsonnode root = mapper.readtree(new file("d://test.json")); objectnode node = mapper.createobjectnode(); node.with("addressdetails").put("pincode", "414141"); but it's not getting added, there way can ? you can try you should modify file path objectmapper mapper = new objectmapper(); objectnode nodes = mapper.readvalue(new file("d:\\test.txt"), objectnode.class); nodes.with("addressdetails").put("pincode", "414141"); mapper.writer().writevalue(new file("d:\\test.txt"), nodes);
Read more