get less than 30 days with max date values and check flag condition in sql server -


hi have doubt sql server 

table : emp      empid   | name |  sdate        |  flag | deptno | deptname     1     |test  | 2015-09-18    |   2   |10      |hr      1     |tes   | 2014-09-13    |   2   |10      |ceo     1     |hari  | 2015-09-14    |   2   |10      |pm     1     |balu  | 2015-09-05    |   5   |10      |cm     1     |jai   | 2015-09-12    |   1   |20      |hr     2     |kali  |2015-09-15     |   2   |20      |deo     2     |hni   |2015-09-04     |   5   |20      |br     3     |jai   |2015-09-10     |   3   |20      |ceo     3     |tes   |2015-09-20     |   3   |20      |hr     3     |man   |2015-09-16     |   5   |20      |hal     4     |op    |2015-09-12     |   2   |10      |har     4     |jai   |2014-05-01     |   5   |10      |kal

based on above table want output below

here change flag values, here changing names, because deptname remains row value 2 >compare records based on empid+ deptno , sdate of flag=2 , sdate of flag=5, sdate should less or equal flag=2 sdate , difference between sdates should less 30 days. need apply lesst 30 days flag 2 , 5 values remain flag values(1,3) no need check date conditon.

  empid     | name   | sdate         | flag   | deptno  | deptname     1       | balu   | 2015-09-18    |   2    |10       |hr     1       |jai     | 2015-09-12    |   1    |20       |hr     2       |hni     |2015-09-15     |   2    |20       |deo     3       |jai     |2015-09-10     |   3    |20       |ceo     3       |tes     |2015-09-20     |   3   |20        |hr     3       |man     |2015-09-16     |   5    |20       |hal     4       |op      |2015-09-12     |   2    |10       |har

i tried below :

select distinct a1.[id]       ,isnull(a2.name,a1.name) name,a1.flag,a1.deptno,a1.[sdate]  [testresult].[dbo].[emp1] a1 left join [testresult].[dbo].[emp1] a2    on a1.id=a2.id    , a1.deptno=a2.deptno   , a1.[check] =2 , a2.[check]=5 , datediff(day,a2.sdate,a1.sdate)<=30     not exists (select 1 [testresult].[dbo].[emp1] a3 a3.id = a.id ,  a3.deptno=a1.deptno   , a1.flag = 5 , a3.flag = 2)

above query not give expected result . please tell me how write query achive task in sql server

i'm not sure correctly understand query algorithm, seems me query meet expectations.

declare @emp1 table  (empid int, name nvarchar(10), sdate date, flag int, deptno int, deptname nvarchar(10))  insert @emp1 (empid, name, sdate, flag, deptno, deptname) values (1, 'test', '20150918', 2, 10, 'hr') insert @emp1 (empid, name, sdate, flag, deptno, deptname) values (1, 'tes', '20150913', 2, 10, 'ceo') insert @emp1 (empid, name, sdate, flag, deptno, deptname) values (1, 'hari', '20150914', 2, 10, 'pm') insert @emp1 (empid, name, sdate, flag, deptno, deptname) values (1, 'balu', '20150905', 5, 10, 'cm') insert @emp1 (empid, name, sdate, flag, deptno, deptname) values (1, 'jai', '20150912', 1, 20, 'hr') insert @emp1 (empid, name, sdate, flag, deptno, deptname) values (2, 'kali', '20150915', 2, 20, 'deo') insert @emp1 (empid, name, sdate, flag, deptno, deptname) values (2, 'hni', '20150904', 5, 20, 'br') insert @emp1 (empid, name, sdate, flag, deptno, deptname) values (3, 'jai', '20150910', 3, 20, 'ceo') insert @emp1 (empid, name, sdate, flag, deptno, deptname) values (3, 'man', '20150916', 5, 20, 'hal') insert @emp1 (empid, name, sdate, flag, deptno, deptname) values (4, 'op', '20150915', 2, 10, 'har') insert @emp1 (empid, name, sdate, flag, deptno, deptname) values (4, 'jai', '20140501', 5, 10, 'kal')       select x.empid ,    x.name ,    x.sdate ,    x.flag ,    x.deptno ,    x.deptname       (       select e.empid ,          isnull(x.name,e.name) name ,          e.sdate ,          e.flag ,          e.deptno ,          e.deptname,          row_number() on (partition e.empid, e.deptno, e.flag order e.sdate desc) rn                @emp1 e          left join           (  select *             @emp1 e2                               exists (select * @emp1 e4 e2.empid = e4.empid , e2.deptno = e4.deptno , e4.flag = 2)                 , abs(isnull(datediff(day, (select top 1 e1.sdate @emp1 e1 e2.empid = e1.empid , e2.deptno = e1.deptno , e1.flag = 2), e2.sdate),0)) < 30         ) x          on e.empid = x.empid , e.deptno = x.deptno , x.flag = 5                (e.flag <> 5 or not exists (select 1 @emp1 e3 e.empid = e3.empid , e.deptno = e.deptno , e3.flag = 2))     ) x            x.rn = 1

Comments

Post a Comment

Popular posts from this blog

html - Outlook 2010 Anchor (url/address/link) -

i'm trying link specific pdf page msoutlook'10 or msword'10, #page=... anchor particularly useful , works fine elsewhere. unfortunately when click [ctr-k] in outlook or winword, links re-formatted assuming unc path. pdf file located on shared drive. using "file:" prefix links open pdf in adobe reader (outside of browser), , "http:" open inside browser. issue seem need open in browser "file:" prefix in order anchor work. know anchors work inside browser. outlook uses windows/explorer default program (based on file extension) seems open in adobe reader neglecting anchor. is aware of solution, link specific page or bookmark of shared-pdf ms outlook and/or winword link? common references: mailermailer.com/...anchor-tags-html-emails campaignmonitor.com/...anchor-links-in-email-newsletters/ stackoverflow.com/...anchor-in-pdf-document
Read more

javascript - Why does running this loop 9 times take 100x longer than running it 8 times? -

consider code: test = function() { } t = new test(); (var = 0; < 8; i++) { result = t instanceof test; } if change number of iterations 8 9 , loop take 100 times longer complete in latest version of firefox (41.0.1). tested on 2 different pcs , magic limit 8. here jsperf test used: http://jsperf.com/instanceof-8-times-vs-9-times does have idea why might happen? seems specific instanceof . not happen if else object, example check property. note: filed bugzilla bug this. jan de mooij mozilla team has posted details in bugzilla thread . here's simplistic interpretation of highly technical answers: in i < 8 case, firefox smart enough hoist result = t instanceof test; statement out of loop (according tests, doesn't seem omit altogether). in i < 9 case, apparently doesn't optimization . why? reason not clear, related fact 9 iterations threshold above function considered "hot" enough run through jit compiler. i < 8 cas...
Read more

Getting gateway time-out Rails app with Nginx + Puma running on Digital Ocean -

following this guide setup app on digital ocean droplet, not working. site not loading , it's throwing: "gateway time-out". the puma server works, since ps aux | grep puma prints: root 15878 0.0 0.4 376832 2168 ? sl 12:50 0:00 puma 2.13.4 (unix:///var/www/myapp/shared/tmp/sockets/myapp-puma.sock) [20150930164940] root 15881 0.0 0.7 903676 3692 ? sl 12:50 0:06 puma: cluster worker 0: 15878 [20150930164940] root 24654 0.0 0.1 11748 916 pts/2 s+ 18:00 0:00 grep --color=auto puma my nginx config: user root; worker_processes 1; events { worker_connections 1024; } http { upstream puma { server unix:///var/www/myapp/shared/tmp/sockets/myapp-puma.sock; } server { listen 80; server_name myapp.com; root /var/www/myapp/current/public; access_log /var/www/myapp/current/log/access.log; error_log /...
Read more