Add records in table 1 since a date in table 2

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)

Add records in table 1 since a date in table 2

Author

Topic

crackerbox
Starting Member

21 Posts

Posted - 2007-02-21 : 15:51:10

I have two tables that have dates in both tables. I need to add up all the data in table 1 that has a date later than or equal to the date in table 2. The tables are joined with the Aircraft no.

Here's some sample data.
declare @t1 table (Aircraft int, dt datetime, hours int)

insert @t1
select 38, '4/19/06', .5 union all
Select 38, '4/21/06', 1 union all
select 38, '5/01/06', 2 union all
select 38, '6/19/06', 4 union all
select 40, '1/20/06', 15 union all
select 40, '8/1/06', 35

declare @t2 table (aircraft int, dt datetime)

insert @t2
select 38, '4/20/06' union all
select 40, '1/20/06'

This is what I'm looking for (I also need to include the date from table 2 and the latest date from table 1:
Aircraft dt dt2 Hours
38 6/19/06 4/20/06 7
40 8/1/06 1/20/06 45

Any help would be appreciated.
Thanks

PurpleSun
Yak Posting Veteran

50 Posts

Posted - 2007-02-21 : 16:58:05

Hope this will help

select t1.aircraft, max(t1.dt), sum(t1.hours) as hours, t2.dt
from t1 inner join t2 on t1.aircraft=t2.aircraft
group by t1.aircraft, t2.dt
having max(t1.dt) >= t2.dt

crackerbox
Starting Member

21 Posts

Posted - 2007-02-21 : 17:16:28

That worked great! You're my hero.
Thanks a bunch.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources