CODEWITHSUNDEEP
mysqlsqlsql-server
Thissiteusescookies
Thissiteusescookies

Reputation: 133

How do i get datediff to work with min() max() on sql?

Say I have this table:

                         EMPLOYEES
----------------------------------------------------------
first    last        ID    hire         terminated    wage
------   ---------   ---   ----------   -----------   ----
Ruth     Halburg     1     2010-05-15   2017-03-01    2000
Sally    Hallson     2     2010-08-20   NULL          5000
Merry    Hallway     3     2011-01-24   NULL          3000

and I need to get the difference in days between the max 'hire' date and min 'hire' date of employees still working in the company (terminated = NULL).

I know I need to use something like 

Select *, datediff(d, max(hire), min(hire) as Difference
From Employees
Where terminated = NULL

but i'm not sure how to get it to work.

Upvotes: 3

Views: 11926

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270773

I believe you want:

Select id,
       datediff(day, min(hire) max(hire)) as Difference
From Employees
group by id
having count(terminated) < count(*) -- at least one NULL value

Note that the syntax for datediff() uses the SQL Server version of the function. I don't think this is really important to your question. The key part is the group by and having clauses.

Upvotes: 0

ScaisEdge
ScaisEdge

Reputation: 133400

Assuming you are using mysql datediff return difference in days

and for the query

If the terminated is a string the you must check for 'NULL' if is a date the you should check for is null

  Select  datediff(max(hire), min(hire)) as Difference
  From Employees
  Where terminated = 'NULL'

or 

  Select  datediff( max(hire), min(hire)) as Difference
  From Employees
  Where terminated is null

If you want use aggregation function you can't use column in select clause that is not mentioned in group by eg: 

select  last, datediff(max(hire), min(hire)) as Difference
From Employees
Where terminated is null
group by  last

or 

select  last, datediff(max(hire), min(hire)) as Difference
From Employees
group by  last

for sql-server instead of datediff(date1, date2) for day difference you must add the interval param 

SELECT DATEDIFF(d, max(hire),min(hire))

Upvotes: 1

Related Questions