Sum of the units based on the most recent date

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)

Sum of the units based on the most recent date

Author

Topic

ttran
Starting Member

23 Posts

Posted - 2006-09-26 : 20:42:56

Hi,

I'm really appreciate if someone help me with this query.
I want the sum of the units for each employee on table a where the complete date is after his most recent date of another table (table b).

table a
id complete date units
123 09/01/1985 3.0
123 01/10/1987 3.0
123 01/10/1987 3.0
123 10/10/1990 3.0
123 10/10/1990 3.0
125 01/01/1995 3.0
125 06/30/1996 3.0
127 02/04/2000 3.0

table b
id degree date
123 BA 06/30/1987
123 MA 06/30/1990
125 MA 02/15/1995
125 DR 04/15/1996

The result should be:
id sum
123 6.0
125 3.0

Thank you in advance.
todoan

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2006-09-26 : 20:59:59

Is this a homework question??

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-09-26 : 23:51:26

[code]declare @tablea table
(
id int,
complete_date datetime,
units decimal(10,2)
)
insert into @tablea
select 123, '09/01/1985', 3.0 union all
select 123, '01/10/1987', 3.0 union all
select 123, '01/10/1987', 3.0 union all
select 123, '10/10/1990', 3.0 union all
select 123, '10/10/1990', 3.0 union all
select 125, '01/01/1995', 3.0 union all
select 125, '06/30/1996', 3.0 union all
select 127, '02/04/2000', 3.0

declare @tableb table
(
id int,
degree varchar(10),
date datetime
)

insert into @tableb
select 123, 'BA', '06/30/1987' union all
select 123, 'MA', '06/30/1990' union all
select 125, 'MA', '02/15/1995' union all
select 125, 'DR', '04/15/1996'

select a.id, sum(units) as [sum]
from @tablea a inner join
(
select id, max(date) as max_date
from @tableb b
group by id
) b
on a.id = b.id
and a.complete_date >= b.max_date
group by a.id
[/code]

ttran
Starting Member

23 Posts

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

Sorry... it's not a homework question. Our school district is going the conversion process from from VAX to MSSQL Server, I have to manipulate and move the data over to SQL, and I got stuck with this problem for half day, couldn't figure out the query. I have other deadline to meet and save me some time.... so I posted the problem.

Todoan

quote:
Originally posted by timmy

Is this a homework question??

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-09-27 : 11:21:07

"Our school district is going the conversion process from from VAX to MSSQL Server"
VAX Machine ? DEC VMS ? It is still around ?

ttran
Starting Member

23 Posts

Posted - 2006-09-27 : 11:55:59

Yes, that VAX VMS. Lots of people laugh when I mention it.

Thank you for the query.
It works well when I run with your coding, but when I apply to my tables (taking out the insert coding since the tables' definition and data are already in the database), it gives me the errors:
Must declare the table variable "@tblEmpEducation"
Must declare the table variable "tblEmpDegree"

How can I declare them properly? Do I have to code the entire table definition again? They're already defined in the database.
Sorry...if my question is stupid but I'm new and we have to convert the data without getting training much on sql.

Thank you again.

quote:
Originally posted by khtan

"Our school district is going the conversion process from from VAX to MSSQL Server"
VAX Machine ? DEC VMS ? It is still around ?

KH

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-09-27 : 12:01:24

you only need this part. Replace the @tablea and @tableb with you actual table name


select 	a.id, sum(units) as [sum]
from 	@tablea a inner join 
	(
		select	id, max(date) as max_date
		from	@tableb b
		group by id
	) b
	on	a.id		= b.id
	and	a.complete_date	>= b.max_date
group by a.id

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-09-27 : 12:05:03

"Yes, that VAX VMS. Lots of people laugh when I mention it."
The previous company that i work with in the early 90s had one of the VAX VMS machine that was not used (switch off) for several years. One day we decided to just switch it on and it still working fine. Damn reliable machine from DEC. Too bad you can't find these kind of machine any more.

ttran
Starting Member

23 Posts

Posted - 2006-09-27 : 12:22:05

Thank you so much. It saves me a lot of time.
Todoan

quote:
Originally posted by khtan

you only need this part. Replace the @tablea and @tableb with you actual table name

select a.id, sum(units) as [sum]
from @tablea a inner join
(
select id, max(date) as max_date
from @tableb b
group by id
) b
on a.id = b.id
and a.complete_date >= b.max_date
group by a.id

KH

ttran
Starting Member

23 Posts

Posted - 2006-09-27 : 14:54:37

yes, that dinosaur is very reliable.

khtan,

Is it possible if I want to update a column on the tablec based on the id and the sum from that result?

tablec has columns: id, level

If the id match and tablec.level = 3 and the sum >= 30 then level = 2
If the id match and tablec.level = 5 and the sum >= 30 then level = 4

Thank you,
todoan

quote:
Originally posted by khtan

"Yes, that VAX VMS. Lots of people laugh when I mention it."
The previous company that i work with in the early 90s had one of the VAX VMS machine that was not used (switch off) for several years. One day we decided to just switch it on and it still working fine. Damn reliable machine from DEC. Too bad you can't find these kind of machine any more.

KH

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-09-27 : 17:37:01

quote:
Originally posted by khtan

"Yes, that VAX VMS. Lots of people laugh when I mention it."
The previous company that i work with in the early 90s had one of the VAX VMS machine that was not used (switch off) for several years. One day we decided to just switch it on and it still working fine. Damn reliable machine from DEC. Too bad you can't find these kind of machine any more.

KH

They are still around and selling new systems, or at least VMS on Alpha and Integrity cpus. They haven't made VAX systems for a number of years, but HP still offers support for them.
http://h71000.www7.hp.com/

I'll be happy if Microsoft can ever offer a feature like a 96 node clusters with the database active on all nodes, and the nodes up to 500 km apart.

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources