GROUPING - 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)

GROUPING

Author

Topic

Leigh79
Starting Member

28 Posts

Posted - 2008-04-03 : 07:35:21

Hi Guys

I'm not sure this is possible, but here is what I'm trying to achieve:

I have 4 records which I would like to group together on their reference number. The 4 records all have the same ref so this is fine, however, they have 4 different transaction dates.

I would like to group these 4 records to give me one record and to show the transaction date as the lastest date to have occurred out of the 4.

Can anyone help?

Thanks
Leigh

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2008-04-03 : 07:39:09

Use max on the date.


select RefNo, max(transactionDate)
from table
group by RefNo

Leigh79
Starting Member

28 Posts

Posted - 2008-04-03 : 08:25:07

That's brill thanks! One other thing, on the same query if 3 of the records had the Customer Name as TBA however the latest transaction had the Customer Name of Smith, how would I pull this into the query without it giving 2 results - one for TBA and one for Smith?

Leigh79
Starting Member

28 Posts

Posted - 2008-04-03 : 09:58:13

Hi Guys

Another way of explaining what I am trying to achieve is this...

I have 4 files, I would like to take all of the customer information from the last file added as above, but I would like to reference all 4 files to give me the sum of the costs e.g.

NAME | FILENO | COST | DATE
TBA | 0001 | 120.00 | 01/02/2008
TBA | 0001 | 22.00 | 02/02/2008
TBA | 0001 | -20.00 | 03/02/2008
Smith | 0001 | 0.00 | 04/02/2008

This would give me one result:

Smith | 0001 | 122.00 | 04/02/2008

Thanks again

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-03 : 10:21:30

quote:
Originally posted by Leigh79

Hi Guys

Another way of explaining what I am trying to achieve is this...

I have 4 files, I would like to take all of the customer information from the last file added as above, but I would like to reference all 4 files to give me the sum of the costs e.g.

NAME | FILENO | COST | DATE
TBA | 0001 | 120.00 | 01/02/2008
TBA | 0001 | 22.00 | 02/02/2008
TBA | 0001 | -20.00 | 03/02/2008
Smith | 0001 | 0.00 | 04/02/2008

This would give me one result:

Smith | 0001 | 122.00 | 04/02/2008

Thanks again

SELECT t.*
FROM Table t
INNER JOIN (SELECT FILENO,MAX(DATE) AS MAXDATE
            FROM Table
            GROUP BY FILENO) t1
ON t1.FILENO=t.FILENO
AND t1.MAXDATE = t.DATE

Leigh79
Starting Member

28 Posts

Posted - 2008-04-03 : 10:41:47

Hi, thanks for getting back to me, I tried this and it is still giving me 2 seperate results one for the name Smith and then one for the name TBA...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-03 : 10:47:36

As per your sample data it wont. Unless you have two records with names Smith ,TBA & same max date value (4/02/2008) for file no 0001

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2008-04-03 : 11:02:19

With the data given, it does work.


declare @Table table (
		[NAME] varchar(255), 
		FILENO varchar(4),
		COST decimal(10,2),
		DATE datetime
)

insert into @table
select 'TBA','0001',120.00,'2008-02-01'
union select 'TBA','0001',22.00,'2008-02-02'
union select 'TBA','0001',-20.00,'2008-02-03'
union select 'Smith','0001',0.00,'2008-02-04'


SELECT t.NAME, t.FILENO, t1.COST, t.DATE
FROM @Table t
INNER JOIN (SELECT FILENO,MAX(DATE) AS MAXDATE, SUM(COST) as COST
            FROM @Table
            GROUP BY FILENO) t1
ON t1.FILENO=t.FILENO
AND t1.MAXDATE = t.DATE

Leigh79
Starting Member

28 Posts

Posted - 2008-04-03 : 11:57:01

Sorry my mistake, I've got this working now! Thanks Rick I can go home and chill out now!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources