67721

Advanced MySQL Query

Question:

Employees EmpID : int(10) Firstname: varchar(100) Lastname: varchar(100) HireDate: timestamp TerminationDate: timestamp AnnualReviews EmpID: int(10) ReviewDate: timestamp

What is query to calculate the longest period (in days) that the company has gone without a hiring or firing anyone.?

So far, this is my query :

SELECT max(abs(datediff((select max(terminationdate) from employees where terminationdate < t.terminationdate), terminationdate))), max(abs(datediff((select max(hiredate) from employees where hiredate < t.hiredate), hiredate))) FROM employees AS t

I do not know how to compare date for each row at same field..

Answer1:

Still, it is a tough assignment...

SELECT x.date, MIN(y.date) y_date,DATEDIFF(MIN(y.date),x.date) days FROM ( SELECT hiredate date FROM employees UNION SELECT terminationdate FROM employees ) x JOIN ( SELECT hiredate date FROM employees UNION SELECT terminationdate FROM employees UNION SELECT CURDATE()) y ON y.date > x.date GROUP BY x.date ORDER BY days DESC LIMIT 1;

Recommend

  • Variable naming and team members who speak another language
  • How to add custom input field in orangehrm (add vacancy section)
  • How to determine start and end dates from a single column
  • How to select current dates from current month
  • Rails - execute an ajax call via select dropdown menu
  • Obtain date for prior month and prior year issue?
  • HtmlAnchor click() function in Htmlunit is not working
  • Problem with jquery ajax and google chrome
  • How do I center a list?
  • find cout of specific day between two date php without using any loop?
  • HTML5 embed tag - event attributes not firing (onended, onpause etc)
  • Preventing ctrl+z in browser
  • Any Framework functions helping to find the longest common starting substring of multiple strings?
  • SGDClassifier giving different accuracy each time for text classification
  • How to get (-8)^0.333333 = -2 in MATLAB?
  • Grails redirect with reverse proxy
  • Color a heatmap in Python/Matplotlib according to requirement
  • Qt - Cannot get lambda to work [duplicate]
  • Write “NOT IN” in Doctrine Query Language
  • sql left join returns
  • How to get or calculate size of Azure File/Share or Service
  • Quickly or concisely determine the longest string per column in a row-based data collection
  • How to model a mixture of finite components from different parametric families with JAGS?
  • Two columns in subquery in where clause
  • Encrypting credit card details using AngularJS in Braintree
  • UML diagram generator in Visual Studio 2010
  • Autohotkey script running program with command line arguments
  • SQL Server re-calculate or not?
  • Graceful pod termination
  • Receive mouse move even cursor is outside control
  • Replace last two characters in column
  • XSLT foreach repeating nodes to flat
  • How to access culture data in globalize.js V1.0.0
  • Date Conversion from yyyy-mm-dd to dd-mm-yyyy
  • print() is showing quotation marks in results
  • Ajax jQuery multiple calls at the same time - long wait for answer and not able to cancel
  • ORA-29908: missing primary invocation for ancillary operator
  • How to get next/previous record number?
  • How do you join a server to an Active Directory (domain)?
  • How does Linux kernel interrupt the application?