Sql Query to display MIN and MAX of Date column

User_X9S6M

2018-01-01 05:30:00

2018-01-01 05:30:00

2018-01-01 05:30:00

2018-01-01 05:30:00

2018-01-01 05:30:00

2018-01-01 05:30:00

2018-01-01 05:30:00

2018-01-01 05:30:00

2018-01-01 05:30:00

2018-01-01 05:30:00

2018-01-01 05:30:00

2018-01-01 05:30:00

2020-12-01 05:30:00

2020-12-01 05:30:00

2020-12-01 05:30:00

2020-12-01 05:30:00

2020-12-01 05:30:00

2020-12-01 05:30:00

2020-12-01 05:30:00

2020-12-01 05:30:00

2020-12-01 05:30:00

2020-12-01 05:30:00

2020-12-01 05:30:00

2020-12-01 05:30:00

Need MIN(DATE) and MAX(DATE) and No of Months 9i.e count)

Expected o/p:

Months Date_Min Date_Max

36.0 2018-01-01 05:30:00 2020-12-01 05:30:00

Frank Kulash

Hi,@User_X9S6M

Here's one way:

SELECT   MONTHS_BETWEEN ( TRUNC (MAX (hiredate), 'MONTH')
	 		, TRUNC (MIN (hiredate), 'MONTH')
			) + 1   AS months
,	 MIN (hiredate)	   	AS date_min
,	 MAX (hiredate) 	AS date_max
FROM	 scott.emp;

Since I don't have a copy of your sample data, I used the scott.emp table.

The months column includes the month of date_min and the month of date_max, regardless of where the dates are in those months.

Frank Kulash

Hi,@User_X9S6M

Here's one way:

SELECT   MONTHS_BETWEEN ( TRUNC (MAX (hiredate), 'MONTH')
	 		, TRUNC (MIN (hiredate), 'MONTH')
			) + 1   AS months
,	 MIN (hiredate)	   	AS date_min
,	 MAX (hiredate) 	AS date_max
FROM	 scott.emp;

Since I don't have a copy of your sample data, I used the scott.emp table.

The months column includes the month of date_min and the month of date_max, regardless of where the dates are in those months.

User_X9S6M

https://community.oracle.com/tech/developers/discussion/comment/16798144#Comment_16798144

Is it possible to do group by on Unique key column (EMPNO) in the same query?

Frank Kulash

Hi, @User_X9S6M

Is it possible to do group by ... in the same query?

Sure, just add a GROUP BY clause.

Since that will generally result in multiple rows of output, you'll probably want to add the GROUP BY expression(s) to the SELECT clause, and you'll probably want some kind of ORDER BY clause, but these things are optional.

I still don't have a copy of your table, or know what the exact results you want are, so I'll continue to use the scott.emp table to illustrate:

SELECT   MONTHS_BETWEEN ( TRUNC (MAX (hiredate), 'MONTH')
	 		, TRUNC (MIN (hiredate), 'MONTH')
			) + 1  AS months
,	 MIN (hiredate)	  	AS date_min
,	 MAX (hiredate) 	AS date_max
,	 job 		-- probably wanted
FROM	 scott.emp
GROUP BY job
ORDER BY months		-- or whatever you want
;

Output:

  MONTHS DATE_MIN    DATE_MAX    JOB
-------- ----------- ----------- ---------
       1 17-Nov-1981 17-Nov-1981 PRESIDENT
       3 02-Apr-1981 09-Jun-1981 MANAGER
       8 20-Feb-1981 28-Sep-1981 SALESMAN
      65 03-Dec-1981 19-Apr-1987 ANALYST
      78 17-Dec-1980 23-May-1987 CLERK

Is it possible to do group by on Unique key column (EMPNO) in the same query?

Yes, but do you really want to? If you GROUP BY anything that's unique, then there will only be one row in each group, so the months column will always be 1, and the date_min will be the same as date_max.