Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 sql query help...please

Author  Topic 

berny978
Starting Member

1 Post

Posted - 2013-04-29 : 17:23:00
Hi to all good people out there!

I need a little help with one sql query. Suppose we have one table with data like this:
Date ID
-----------------
4-1-2013 10
4-2-2013 10
4-3-2013 10
4-4-2013 10
4-5-2013 20
4-6-2013 20
4-7-2013 10
4-8-2013 10
4-9-2013 10
4-10-2013 30
-----------------

Now, I would like to get min and max dates for each ID, but like this:
ID min max
------------------------------
10 4-1-2013 4-4-2013
20 4-5-2013 4-6-2013
10 4-7-2013 4-9-2013
30 4-10-2013 4-10-2013
-------------------------------

Is it possible somehow? Thanks in advance!
B.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-04-29 : 17:30:11
[code];WITH cte AS
(
SELECT *,
ROW_NUMBER() OVER (ORDER BY Date) -
ROW_NUMBER() OVER (PARTITION BY ID ORDER BY date ) AS G
FROM
YourTable
)
SELECT
ID,
MIN(Date) AS [Min],
MAX(Date) AS [Max]
FROM
cte
GROUP BY
ID, G
ORDER BY
[Min],ID[/code]
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-30 : 04:03:45
[code]
select MIN([date]) AS [min],MAX([date]) AS [max],ID
from TABLE t
outer apply (select MIN([Date]) AS Mindate
FROM table
WHERE ID <> t.ID
AND [Date] > t.[Date])t1
GROUP BY ID,Mindate
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
   

- Advertisement -