sql server - use outer apply to find for each row have a record based on first result from detail table -

-

i have 2 tables; problem problem (as master) group of problem , problem_log (as detail)

problem

problem_id      problem          pgroup 1               line down        network 2               node down        network 3               hardware error   hardware 4               pm               hardware 5               disk error       hardware

problem_log

term        problem           down_date s1dip0314   service           15-03-26 s1dip0314   pm                15-01-01 s1dip0314   service           15-01-02 s1dip0314   disk error        15-01-06 s1dip0314   hardware error    15-01-28

at first find term have problem = 'pm' (in case use dateadd+7 days)

select  term,problem,down_date   [problem_log]   problem = 'pm'

but problem when find problem = 'pm' date have pm + 7 days have other pgroup = 'hardware' or not example there more 1 problem within 7 days there 'disk error'
result should this

term        problem       down_date s1dip0314   pm            15-01-01 s1dip0314   disk error    15-01-06

and when found more problem find more problem within 30 days last problem found

term        problem           down_date s1dip0314   pm                15-01-01 s1dip0314   disk error        15-01-06 s1dip0314   hardware error    15-01-28

i must use outer apply make condition come true

i tried this

select pt.term,        pts.problem,        pts.down_date   problem_log pt        left outer join atm on pt.term = atm.term        outer apply(                      select px.term,                            px.problem,                            px.down_date                       problem_log px                            left join problem pf on pf.problem = px.problem                       px.problem = pt.problem                         , pgroup = 'hardware' ) pts   pt.problem = 'pm'   , ('20' + pt.down_date + ' ' + down_time) <= dateadd( day,7,('20' + pt.down_date + ' ' + pt.down_time))) pts;

i think need logs of problems same group can use simple query this:

declare @term varchar(10) = 's1dip0314' declare @problem varchar(10) = 'pm'  select pt.term, pt.problem, pt.down_date  problem_log pt pt.term = @term      , pt.problem in (select p.problem problem p                         p.pgroup = (select p1.pgroup problem p1                                            p1.problem = @problem))

Comments

Post a Comment

Popular posts from this blog

google chrome - Developer tools - How to inspect the elements which are added momentarily (by JQuery)? -

-
i looking @ how website designed using inspector in chrome. can use firefox developer tools or firefox web developer add-on or chrome web developer extension. so website has slide-show , using jquery it. looking @ markup , css in inspector, i see 2 things constantly changing slides change: one opacity css property understandable, i'd see changing values, changes fast see read anything. second- a div added in div slides change momentarily , moment when appears short notice except div . so question is there way stop slideshow can temporarily rid of blinking sort of effect (by speedy adding , removal , changing of elements , values)? where can inspect div element gets added such short moment , , gets removed? , can see changes in value of opacity takes place? is there way stop slideshow can temporarily rid of blinking sort of effect (by speedy adding , removal , changing of elements , values)? if opacity animated css transition or animation,
Read more

angularjs - Showing an empty as first option in select tag -

-
my app showing empty select field first entry. have read other solution given , tried still not working. my controller code is: .controller('usercreatecontroller', function(user,profile,auth) { alert("hello"); var vm = this; vm.type = 'create'; profile.all() //this service fetch profiles shown in select field .success(function(data){ vm.profile = data; }); vm.userdata.profile = vm.profile[0]; //here have set default value select field and html page is: <label class="col-sm-2 control-label">profile</label> <div class="col-sm-8"> <select ng-model="user.userdata.profile" ng-options="person._id person.profilename person in user.profile"> </select> </div> hierarchy of vm.profile { "_id": 46, "profile": objectid("5516498e95b84548156db754"), "isactive": true, "password": "$2
Read more

php - Cloud9 cloud IDE and CakePHP -

-
i attempting cakephp running on cloud9, cloud based ide. unaware, cloud collaboration ide intermixes services such bitbucket. site gives sudo rights, , installed cakephp using these instructions: https://www.digitalocean.com/community/tutorials/how-to-install-cakephp-on-an-ubuntu-12-04-vps when attempt run database exported machine cakephp installed, error message "url rewriting not configured on server." attempted solve issue using official guide cakephp: http://book.cakephp.org/2.0/en/installation/url-rewriting.html but no cigar, same error message. has gotten cakephp working, or aware of cloud ide free/low cost cloud9 support cakephp? here's how got working now: sudo apt-get update sudo apt-get install php5-intl curl -ss https://getcomposer.org/installer | php -- --filename=composer ./composer create-project --prefer-dist cakephp/app this creates project in /app default. move workspace docroot: mv app/* ./ mv app/.* ./ rm -rf app then de
Read more