select max() and min() dates from table

Question

I want to fetch the available max(), max() -1, min(), min() +1 date records for every month. The query gives the required results. Is there a better way to achieve the same result?

select * from itm_price where dt in (
 select MAX(dt) from itm_price GROUP BY MONTH(dt), YEAR(dt)
 UNION
 select MAX(dt)-1 from itm_price GROUP BY MONTH(dt), YEAR(dt)
 UNION
 select MIN(dt)+1 from itm_price GROUP BY MONTH(dt), YEAR(dt)
 UNION
 select MIN(dt) from itm_price GROUP BY MONTH(dt), YEAR(dt)
) order by 2 desc

Answer 1

Hello

One simple query should also work:

SELECT Maxdate
      ,MinDate
      ,DATEADD(day, MaxDate, -1) As Max1
      ,DATEADD(day, MinDate, +1) As Max1
FROM
   (select MAX(dt) AS MaxDate
           MIN(dt) AS MinDate
    from itm_price 
    GROUP BY MONTH(dt), YEAR(dt)
   ) AS Sub

---

Olaf Helper
* cogito ergo sum * errare humanum est * quote erat demonstrandum *
Wenn ich denke, ist das ein Fehler und das beweise ich täglich
Blog Xing

Answer 2

Thanks for your post.

There are couple of things here:

1. I want to select other columns (say price) based on these dates. Outer query should accept these dates as input ( WHERE dt IN (....) )

2. The dates returned must be existing in the table. All dates need not have records in the table.

    'I want to fetch the available max(), max() -1, min(), min() +1 date records for every month'

Answer 3

Try like this :-

select MAX(dt) MAX,

MIN(dt) MIN,

(select MAX(dt) from itm_price where dt < (select MAX(dt) from itm_price)) MAX_MINUS_ONE,
(select MIN(dt) from itm_price where dt > (select MIN(dt) from itm_price)) MIN_PLUS_ONE
 from itm_price

---

Please click the Mark as Answer or Vote As Helpful if a post solves your problem or is helpful!

Answer 4

Thanks for the post. I need the MIN(), MAX(), MIN()-1, MAX()+1 records for ALL months.

Answer 5

Hello siva.k,

I was thinking that your request "for ALL months" was satisfied by the GROUP BY MONTH(dt), YEAR(dt) found in the post of Olaf

http://msdn.microsoft.com/en-us/library/ms177673(SQL.105).aspx

Have a nice day

 

---

Mark Post as helpful if it provides any help.Otherwise,leave it as it is.

Answer 6

The query which i posted first gives me correct result. I wanted to know whether there is a better query to achieve the same result.

1. I want date columns and other columns in the itm_price table as the output.

2. All the MAX(), MIN() dates should be based on the available records.

select * from itm_price where dt in (
 select MAX(dt) from itm_price GROUP BY MONTH(dt), YEAR(dt)
 UNION
 select MAX(dt)-1 from itm_price GROUP BY MONTH(dt), YEAR(dt)
 UNION
 select MIN(dt)+1 from itm_price GROUP BY MONTH(dt), YEAR(dt)
 UNION
 select MIN(dt) from itm_price GROUP BY MONTH(dt), YEAR(dt)
) order by 2 desc

Answer 7

Hello,

Please, could you provide us the full version (2008,2008 R2,2012,... + last installed service pack) and the full edition (Express,Web,Standard,Entreprise,...)  of your SQL Server ? These informations are always useful as the syntax of the T-SQL statements as each new version is arriving with new or depreciated syntax.

As you want to have a query which is optimized , i propose you that a moderator moves your thread towards the TRANSACT-SQL Forum where the answerers are more interested by performances problems.No thread to recreate,no lost posts,this thread will not be dying in a less suitable forum.As only a moderator can do a thread move, i would suggest you to post your agreement for this move ( usually, i don't move a thread without the agreement of the original poster, that's to say you ).Don't create a new thread , as in this case , a moderator will have to do a move + a merge ( the merge operation is always a risky operation )

We are waiting for your feedback to try  to help you more efficiently.

Have a nice day

---

Mark Post as helpful if it provides any help.Otherwise,leave it as it is.