Join Query - 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)

Join Query

Author

Topic

mgandra
Starting Member

15 Posts

Posted - 2006-03-06 : 13:07:57

I have two tables Table1 and Table2

Table1 columns:
CustomerNum varchar(12)
InvDate smalldatetime

Table2 columns
CustomerNum varchar(12)
MainDate smalldatetime
Cog float

Table1 Data (sample):
CustomerNum InvDate
995169 2/4/2002
995169 11/4/2002
995169 1/1/2003
995169 3/4/2003
995169 10/4/2003
995169 1/4/2005
995169 5/4/2005
995169 11/15/2005

Table1 Data (sample):
CustomerNum InvDate Cog
995169 10/3/2001 1.07
995169 10/3/2002 1.1
995169 5/16/2003 1.7
995169 9/30/2003 2.9
995169 1/1/2005 2.8
995169 3/31/2005 2.95
995169 10/31/2005 2.85

After joining the tables, I want the data to be displayed as under:

CustomerNum InvDate Cog
995169 2/4/2002 1.07
995169 11/4/2002 1.1
995169 1/1/2003 1.1
995169 3/4/2003 1.7
995169 10/4/2003 2.9
995169 1/4/2005 2.9
995169 5/4/2005 2.95
995169 11/15/2005 2.85

Any help in constructing sql qury will be appreciated. Thanks in advance.

JoeNak
Constraint Violating Yak Guru

292 Posts

Posted - 2006-03-06 : 13:25:16

select a.customernum, a.invdate, b.cog
from table1 a
inner join table2 b on a.customernum = b.customernum
order by a.invdate

mgandra
Starting Member

15 Posts

Posted - 2006-03-06 : 13:44:32

I'm sorry. I haven't made it clear enough. We ned to pick the correct Cog from Table2 based on table2.Maintdate and Table1.InvDate. For example, for 995169 2/4/2002, I should pick only 1.07 form Table2, since the applicable Table2.Cog for the Table1.invdate is to be determined by Table2.MaintDate and the Table2.Cutsomer. The Cog applicable to a customer changes form time to time. I hope I have made it clear.

JoeNak
Constraint Violating Yak Guru

292 Posts

Posted - 2006-03-06 : 14:18:20

yeah, my first above query won't do what you need. I don't see how you come up with 1.07 for 2/4/2002 from your sample data.

JoeNak
Constraint Violating Yak Guru

292 Posts

Posted - 2006-03-06 : 14:29:17

ok, I think I know what you're looking for, not entirely sure...

select a.customernum, a.invdate, max(b.cog)
from table1 a
inner join table2 b on a.customernum = b.customernum and a.invdate >= b.maindate
group by a.customernum, a.invdate
order by a.customernum, a.invdate

mgandra
Starting Member

15 Posts

Posted - 2006-03-06 : 14:32:25

The applicable Cog for the customer# 995169 invpice date 2/4/2002 is 1.07 based on the following rows in Table2:
995169 10/3/2001 1.07
995169 10/3/2002 1.1
The Cog value for a customer changes from time to time. For the customer 995169, it was originally set at 1.07 on 10/3/2001 and it was changed to 1.1 on 10/3/2002. So for the invoice date of 2/4/2002, the apllicable Cog for this customer is 1.07.

JoeNak
Constraint Violating Yak Guru

292 Posts

Posted - 2006-03-06 : 14:34:28

My last post should do it.

mgandra
Starting Member

15 Posts

Posted - 2006-03-06 : 15:19:42

The query is not giving the expcted result. It gives the following output:
995169 2002-02-04 00:00:00 1.07
995169 2002-11-04 00:00:00 1.10
995169 2003-01-01 00:00:00 1.10
995169 2003-03-04 00:00:00 1.10
995169 2003-10-04 00:00:00 2.90
995169 2005-01-04 00:00:00 2.90
995169 2005-05-04 00:00:00 2.95
995169 2005-11-15 00:00:00 2.95

instead of
995169 2/4/2002 1.07
995169 11/4/2002 1.1
995169 1/1/2003 1.1
995169 3/4/2003 1.7
995169 10/4/2003 2.9
995169 1/4/2005 2.8
995169 5/4/2005 2.95
995169 11/15/2005 2.85

JoeNak
Constraint Violating Yak Guru

292 Posts

Posted - 2006-03-06 : 15:37:43

Sorry, didn't look close enough and thought your cog values only went up.

select y.customernum, y.invdate, z.cog
from table2 z
inner join (
select a.customernum, a.invdate, max(b.maindate) as maindate
from table1 a
inner join table2 b on a.customernum = b.customernum and a.invdate >= b.maindate
group by a.customernum, a.invdate
) as y on z.customernum = y.customernum and z.maindate = y.maindate
order by y.customernum, y.invdate

By the way from what I see the 3/4/2003 invoice date should yield 1.1 since 1.7 isn't unitl 5/16/2003

mgandra
Starting Member

15 Posts

Posted - 2006-03-06 : 15:51:15

Thanks Joe.

The following query also gives the same output.
select a.customernum,
a.invdate,
(select max(dis) from @table2 where maintdate = (select max(t1.maintdate) from @table2 t1 where t1.customernum = a.customernum
and a.invdate >= t1.maintdate)) as discount
from @table1 a

But I'm not sure which query is efficient, your's or this one.

JoeNak
Constraint Violating Yak Guru

292 Posts

Posted - 2006-03-06 : 15:54:05

You can look at the execution plans of both queries to see which is more efficient.

Out of curiosity where did the 'dis' field come from?

mgandra
Starting Member

15 Posts

Posted - 2006-03-06 : 15:57:49

I'm using column name 'Dis' for 'Cog' while testing. Sorry for this confusion.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources