Group By - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

Group By

Author

Topic

Littleterry
Starting Member

9 Posts

Posted - 2008-08-29 : 11:31:30

I have the following table:
date number
2008-01-01 1
2008-02-01 1
2008-03-01 1
2008-04-01 1
2008-05-01 2
2008-06-01 2
2008-07-01 1
2008-08-01 1
2008-09-01 1

i want to group the data to achieve the following results
2008-01-01 2008-04-01 1
2008-05-01 2008-06-01 2
2008-07-01 2008-09-01 1

R.Prabu
Starting Member

33 Posts

Posted - 2008-08-29 : 11:40:59

use this

Select Date, Number
From Table1 Group By Date, Number

Regards,
Prabu R

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-08-29 : 12:16:04

select min(date)as minimumdate,max(date)as maximumdate,number
from table
group by Case when date between '2008-01-01' and '2008-04-01' then 1
when date between '2008-05-01' and '2008-06-01' then 2
else 3 end ,number
order by min(date),max(date)

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2008-08-29 : 12:33:15

[code]
declare @Tbl table (PK int Identity(1,1) Primary key Clustered, mydate datetime,ID int,GroupID int)
Insert Into @Tbl(MyDate,ID)
Select '2008-01-01', 1 Union All
Select '2008-02-01', 1 Union All
Select '2008-03-01', 1 Union All
Select '2008-04-01', 1 Union All
Select '2008-05-01', 2 Union All
Select '2008-06-01', 2 Union All
Select '2008-07-01', 1 Union All
Select '2008-08-01', 1 Union All
Select '2008-09-01', 1

Declare @MyGroup int, @Anchor Int, @ID int
Set @MYGroup = 0

Update a
set
@MyGroup = Case when @ID <> a.ID then @MyGroup + 1
else @MyGroup
end
,a.GroupID = @MyGroup
,@ID = a.ID
,@Anchor = a.PK
from @Tbl a

Select Min(MyDate) as StartDate,Max(MyDate) as EndDate,ID from
@Tbl a
group by GroupID,ID
order by StartDate

/*

StartDate EndDate ID
----------------------- ----------------------- -----------
2008-01-01 00:00:00.000 2008-04-01 00:00:00.000 1
2008-05-01 00:00:00.000 2008-06-01 00:00:00.000 2
2008-07-01 00:00:00.000 2008-09-01 00:00:00.000 1

*/
[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-29 : 13:03:58

[code]SELECT MIN(t.date),c.date,t.number
FROM tbl t
OUTER APPLY(SELECT TOP 1 date
FROM tbl
WHERE number<> t.number
AND date >t.date
ORDER BY date)b
OUTER APPLY(SELECT TOP 1 date
FROM tbl
WHERE number= t.number
AND date<COALESCE(b.date,'31 Dec 2019')
ORDER BY date DESC)c
GROUP BY c.date[/code]

Littleterry
Starting Member

9 Posts

Posted - 2008-08-29 : 15:17:17

Thanks Guys

Littleterry
Starting Member

9 Posts

Posted - 2008-08-29 : 15:19:20

Hey guys i'm really new to SQL, as Experts is there and advice or reference you can give me.

Thanks again

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources