Group by problem - 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 2000 Forums

Transact-SQL (2000)

Group by problem

Author

Topic

Hemantkum
Starting Member

2 Posts

Posted - 2005-12-31 : 03:21:18

I have a problem regarding the SQL Server Query. I have a table name tbl_AffAccStatus in this table i use to keep the information regarding the Affiliate User i.e. what is the Expired date of the affiliate user Account and which plan he has choosen. Following is the structure of the table

TABLE Name : tbl_AffAccStatus]
[AffAccStatID] [int]
[AffAccStatAffID] [bigint]
[AffAccStatExpiredOn] [datetime]
[AffAccStatPlanID] [int]
[AffAccStatCreatedOn] [datetime]
[AffAccStatModifiedOn] [datetime]

Sample Data in the table :
AffAccStatID AffAccStatAffID AffAccStatExpiredOn AffAccStatPlanID AffAccStatCreatedOn AffAccStatModifiedOn
1 1 2006-01-22 1 2005-12-22 2005-12-22
2 2 2006-01-22 1 2005-12-22 2005-12-22
3 3 2006-01-22 1 2005-12-22 2005-12-22
4 4 2006-01-22 1 2005-12-22 2005-12-22
5 1 2006-02-21 2 2005-12-22 2005-12-22
6 4 2006-02-21 2 2005-12-22 2005-12-22
7 2 2007-01-22 3 2005-12-22 2005-12-22
8 1 2007-02-22 3 2005-12-22 2005-12-22

I want to make a query on it such that it show the Unique affiliate ID i.e. 1,2,3,4 there maximun expired date(i.e.AffAccStatExpiredOn) per affiliate ID and current plan(i.e. AffAccStatPlanID) selected. For example i want the output in the under written format

AffAccStatAffID AffAccStatExpiredOn AffAccStatPlanID AffAccStatCreatedOn AffAccStatModifiedOn
1 2007-02-22 3 2005-12-22 2005-12-22
2 2007-01-22 3 2005-12-22 2005-12-22
3 2006-01-22 1 2005-12-22 2005-12-22
4 2006-02-21 2 2005-12-22 2005-12-22

I have made the query if i am taking one AffAccStatAffID at a time. Following is the query

Select a.AffAccStatAffId,a.AffAccStatCreatedOn As CreatedDate, a.AffAccStatExpiredOn as ExpireDate, a.AffAccStatPlanID from tbl_AffAccStatus a where a.AffAccStatAffId=1 and AffAccStatExpiredOn in (select MAX(AffAccStatExpiredOn) FROM tbl_AffAccStatus where AffAccStatAffID=1)

I Need a query which show the result for all AffAccStatAffID as per specified above

Regards
Hemant

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-12-31 : 10:10:32

This should do it (thanks for the sample data and table structure)


select	a.AffAccStatAffID 
	,a.AffAccStatExpiredOn 
	,a.AffAccStatPlanID 
	,a.AffAccStatCreatedOn 
	,a.AffAccStatModifiedOn
from	tbl_affaccstatus a
join	(
	select	AffAccStatAffID
		,max(AffAccStatExpiredOn) AffAccStatExpiredOn
	from	tbl_affaccstatus
	group by AffAccStatAffID
	) b
		on b.AffAccStatAffID = a.AffAccStatAffID
		and b.AffAccStatExpiredOn = a.AffAccStatExpiredOn
order by 1

for future posts, could you provide the sample stuff in this format so we can just run it? Thanks!!


create TABLE  tbl_AffAccStatus 
	([AffAccStatID] int
	,[AffAccStatAffID] bigint
	,[AffAccStatExpiredOn] datetime 
	,[AffAccStatPlanID] [int] 
	,[AffAccStatCreatedOn] [datetime] 
	,[AffAccStatModifiedOn] [datetime] )
go

insert tbl_affaccstatus
select 1, 1, '2006-01-22', 1, '2005-12-22', '2005-12-22' union all
select 2, 2, '2006-01-22', 1, '2005-12-22', '2005-12-22' union all 
select 3, 3, '2006-01-22', 1, '2005-12-22', '2005-12-22' union all
select 4, 4, '2006-01-22', 1, '2005-12-22', '2005-12-22' union all
select 5, 1, '2006-02-21', 2, '2005-12-22', '2005-12-22' union all
select 6, 4, '2006-02-21', 2, '2005-12-22', '2005-12-22' union all
select 7, 2, '2007-01-22', 3, '2005-12-22', '2005-12-22' union all
select 8, 1, '2007-02-22', 3, '2005-12-22', '2005-12-22' 

go
drop table tbl_AffAccStatus

Be One with the Optimizer
TG

Hemantkum
Starting Member

2 Posts

Posted - 2006-01-01 : 08:19:01

Thanks,

I will keep in mind your suggestion

Regards
Hemant

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources