Forum Stats

  • 3,769,310 Users
  • 2,252,947 Discussions
  • 7,874,983 Comments

Discussions

Sql Query to display MIN and MAX of Date column

User_X9S6M
User_X9S6M Member Posts: 56 Red Ribbon

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

Tagged:

Best Answer

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,223 Red Diamond
    Accepted Answer

    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.

Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,223 Red Diamond
    Accepted Answer

    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
    User_X9S6M Member Posts: 56 Red Ribbon

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

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,223 Red Diamond

    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.