Distinct/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)

Distinct/Group by

Author

Topic

Shiblee
Starting Member

1 Post

Posted - 2009-09-04 : 09:15:40

Hi
I have data in a table like this----
From_Date To_Date Val_1 Val_2 Val_3
01/09/2009 04/09/2009 2.05 2.05 2.05
15/08/2009 31/08/2009 2.05 2.05 2.05
01/08/2009 14/08/2009 1.99 1.99 1.99
15/07/2009 30/07/2009 2.05 2.05 2.05

But Want the out put in a view like this

From_Date To_Date Val_1 Val_2 Val_3
15/08/2009 04/09/2009 2.05 2.05 2.05
01/08/2009 14/08/2009 1.99 1.99 1.99
15/07/2009 30/07/2009 2.05 2.05 2.05

Can anybody help me

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-09-04 : 11:16:34

cant see how to group by...only able to know the first two row can combine into 1 row...

Hope can help...but advise to wait pros with confirmation...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-09-04 : 21:24:47

[code]
declare @sample table
(
From_Date datetime,
To_Date datetime,
Val_1 decimal(5,2),
Val_2 decimal(5,2),
Val_3 decimal(5,2)
)
set dateformat dmy
insert into @sample
select '01/09/2009', '04/09/2009', 2.05, 2.05, 2.05 union all
select '15/08/2009', '31/08/2009', 2.05, 2.05, 2.05 union all
select '01/08/2009', '14/08/2009', 1.99, 1.99, 1.99 union all
select '15/07/2009', '30/07/2009', 2.05, 2.05, 2.05

; with
data
as
(
select From_Date, To_Date, Val_1, Val_2, Val_3, row_no = row_number() over (order by From_Date)
from @sample
),
cte
as
(
select From_Date, To_Date, Val_1, Val_2, Val_3, row_no, Grp = convert(int, 1)
from data
where row_no = 1

union all

select d.From_Date, d.To_Date, d.Val_1, d.Val_2, d.Val_3, d.row_no,
Grp = case when d.From_Date = dateadd(day, 1, c.To_Date)
and d.Val_1 = c.Val_1 and d.Val_2 = c.Val_2 and d.Val_3 = c.Val_3
then c.Grp
else c.Grp + 1
end
from data d
inner join cte c on d.row_no = c.row_no + 1
)
select From_Date = min(From_Date),
To_Date = max(To_Date),
Val_1, Val_2, Val_3
from cte
group by Grp, Val_1, Val_2, Val_3
[/code]

KH
[spoiler]Time is always against us[/spoiler]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-05 : 03:03:05

Using Quirky update technique http://sqlblogcasts.com/blogs/madhivanan/archive/2009/06/10/quirky-update-in-sql-server.aspx


declare @sample table
(
	From_Date 	datetime,
	To_Date 	datetime,
	Val_1 		decimal(5,2),
	Val_2 		decimal(5,2),
	Val_3		decimal(5,2),
	seq_no		int
)
set dateformat dmy
insert into @sample(From_Date,To_Date,Val_1 ,Val_2 ,Val_3 )
select	'01/09/2009', '04/09/2009', 2.05, 2.05, 2.05 union all
select	'15/08/2009', '31/08/2009', 2.05, 2.05, 2.05 union all
select	'01/08/2009', '14/08/2009', 1.99, 1.99, 1.99 union all
select	'15/07/2009', '30/07/2009', 2.05, 2.05, 2.05 

declare @value decimal(5,2),@seq_no int, @runningtotal int,@product_name varchar(100)


set @seq_no=0


update @sample 
set seq_no =@seq_no  ,
@seq_no=case when @value=val_3  then @seq_no+1 else 1 end,@value=val_3 


select From_Date,To_Date,Val_1 ,Val_2 ,Val_3 from @sample 
where seq_no=1

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources