sql server - How to include loan rules in Employee and Loan sql query? -

-

i have 3 tables employee, loan , loaninstallment.

employee table has 1 many relation loan, , loan has 1 many loaninstallment.

  • employee (empid, name, isonprobation)
  • loan (loanid, empid, startdate, enddate).

now need write query fetch records of employee in following output.

output records (empid, name, status, reason)

rules

  • if employee has not taken loan never status should eligible , reason loan not taken.

  • if employee has taken loan within 1 year (i.e enddate less 1 year) status should not eligible , reason loan taken.

  • if employee on probation status should not eligible , reason on probation

  • if employee has taken laon 1 year ago status should eligible , reason loan taken 1 year ago.

i have written simple query unable understand how include 4 rules , include reason column in single query.

select     e.employeeid, e.fullname,l.enddate,      (case         when datediff(year, max(l.enddate), getdate()) < 0              'eligible'            else 'not eligible'      end) status      employee e  left join     loan l on e.employeeid = l.employeeid group     e.employeeid, e.fullname, l.enddate

you can add rest of conditions case statement. reason column, case statement pretty same instead of status need set reason. also, case when datediff(year, max(l.enddate), getdate()) < 0 wrong result can never less 0.

this should it:

select e.employeeid, e.fullname,l.enddate,         (case when l.employeeid null 'eligible'              when datediff(month, max(l.enddate), getdate()) > 12  'eligible'               when datediff(month, max(l.enddate), getdate()) =<  12  'not eligible'               when l.isonprobation = 1 'not eligible'          else 'not eligible'         end) status,          (case when l.employeeid null 'loan not taken'              when datediff(month, max(l.enddate), getdate()) > 12  'loan taken on 1 year ago'               when datediff(month, max(l.enddate), getdate()) <=  12  'loan taken'               when l.isonprobation = 1 'on probation'          else 'not eligible'         end) reason employee e      left join loan l on e.employeeid = l.employeeid group e.employeeid, e.fullname, l.enddate

Comments

Post a Comment

Popular posts from this blog

ruby - Trying to change last to "x"s to 23 -

-
i have been playing around irb , have string looks this: irb(main):072:0> puts ["[\"4354 5432 5432 xxxxx\", \"6547 6547 8543 xxxxx\", \"2344 6543 6674 xxxxx\", \"2346 6236 7543 xxxxx\", \"1273 5585 5587 xxxxx\"]"] => nil irb(main):073:0> what want gsub last 2 "x" s of each 17 digit combination, 23 . what i've tried far (i've been using rubular): a.gsub(/\d[x]/,"23") <= grabs "x" s i'm close a.gsub(/\w[x{2}]/,"23") <= grabs "x" s grabs two digits each combination. is there easier way i'm not understanding? a.gsub(/\d{4} \d{4} \d{4} xxx\kxx/, '23') \d{4} = 4 digits what \k drop matched far. can use don't replace entire number, still able validate have in desired format.
Read more

jquery - Clone last and append item to closest class -

-
this bare-bones code on page: <div class="list-container"> <div class="single-list-item"> <button>remove</button> </div> <div class="single-list-item"> <button>remove</button> </div> <div class="single-list-item"> <button>remove</button> </div> </div> <p><button class="add-list-item">add</button></p> <div class="list-container"> <div class="single-list-item"> <button>remove</button> </div> <div class="single-list-item"> <button>remove</button> </div> <div class="single-list-item"> <button>remove</button> </div> </div> <p><button class="add-list-item">add</button></p> <di
Read more

c - Unrecognised emulation mode: elf_i386 on MinGW32 -

-
i'm trying make kernel, , cannot link c output assembly. ld . i'm getting error: unrecognized emulation mode: elf_i386 i'm using windows 10 professional mingw32 , msys. code using: link.ld /* * link.ld */ output_format(elf32-i386) entry(start) sections { . = 0x100000; .text : { *(.text) } .data : { *(.data) } .bss : { *(.bss) } } kernel.c /* * kernel.c */ void kmain(void) { const char *str = "my first kernel"; char *vidptr = (char*)0xb8000; //video mem begins here. unsigned int = 0; unsigned int j = 0; /* loops clears screen * there 25 lines each of 80 columns; each element takes 2 bytes */ while(j < 80 * 25 * 2) { /* blank character */ vidptr[j] = ' '; /* attribute-byte - light grey on black screen */ vidptr[j+1] = 0x07; j = j + 2; } j = 0; /* loop writes string video memory */ while(str[j] != '\0') { /
Read more