How to Group by in this Situation ? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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 2000 Forums

SQL Server Development (2000)

How to Group by in this Situation ?

Author

Topic

CLages
Posting Yak Master

116 Posts

Posted - 2006-03-27 : 09:33:33

hi i have this table

Wharehouse = WH
GoodsCode = GC
Invoice = IV
SerialNumber = SN
ExpirationDate = ED

WH GC IV SN ED
001 AAAA 123 ABCD 03/05/2008
001 AAAA 352 ABCD 03/05/2008

001 RTRT 444 ABNI 13/05/2009
001 RTRT 666 ABNI 13/05/2019

001 GGGG 777 RRTR 02/09/2008

001 DFDF 444 ABNI 03/05/2007
001 DFDF 666 ABNI 03/08/2007

How to group by to get only rows which ExpirationDate is not the same
for the same WH , GC, SN

in this case i would like to get only

001 RTRT 444 ABNI 13/05/2009
001 RTRT 666 ABNI 13/05/2019

001 DFDF 444 ABNI 03/05/2007
001 DFDF 666 ABNI 03/08/2007

if i use

select WH , GC, SN, count(ED) from table
group by WH , GC, SN
having count(ED) > 1

Doesnt work because in this sample the SerialNumber ABCD has 2 Expirationdate and is OK.

tks

C. Lages

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-03-27 : 09:37:39

Try this

Select * from table T where not exists
(
Select WH from table where WH=T.WH group by WH,GC,SN having min(ED)=max(ED)
)

Madhivanan

Failing to plan is Planning to fail

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-03-27 : 09:44:13

Try this.

declare	@table table
	(
		WH 	varchar(10),
		GC 	varchar(10),
		IV 	varchar(10),
		SN 	varchar(10),
		ED	datetime
	)
insert into @table
select '001', 'AAAA', '123', 'ABCD', '03/05/2008'	union all
select '001', 'AAAA', '352', 'ABCD', '03/05/2008'	union all
select '001', 'RTRT', '444', 'ABNI', '13/05/2009'	union all
select '001', 'RTRT', '666', 'ABNI', '13/05/2019'	union all
select '001', 'GGGG', '777', 'RRTR', '02/09/2008'	union all
select '001', 'DFDF', '444', 'ABNI', '03/05/2007'	union all
select '001', 'DFDF', '666', 'ABNI', '03/08/2007'

select	t.*
from
(
	select	WH, GC, SN
	from
	(
		select	distinct WH, GC, SN, ED
		from	@table
	) d
	group by WH, GC, SN
	having count(*) > 1
) g inner join @table t
on 	g.WH	= t.WH
and	g.GC	= t.GC
and	g.SN	= t.SN

KH
Choice is an illusion, created between those with power, and those without.
Concordantly, while your first question may be the most pertinent, you may or may not realize it is also the most irrelevant

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-03-27 : 13:19:38

Here's another version...

select a.*
from @table a
    inner join (select wh, gc, sn 
                from @table
                group by wh, gc, sn
                having min(ed) != max(ed)
               ) b
        on a.wh = b.wh and a.gc = b.gc and a.sn = b.sn

I guess it's kind of half-way between the others.

Ryan Randall
www.monsoonmalabar.com

Ideas are easy. Choosing between them is the hard part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources