sql server programming: Well, guys and gals, here I am again with a problem I can't quite figure
out. Anyone give me a push in the right direction? I have two tables in a
database, one contains a list of services given and the other has a list of
people and their addresses. I've stripped these two down to the essentials
for purposes of this request. What I need to do is create a third table
which will give me all the data from the services table and include the
person's zip code at the time the service was delivered. Here is some DLL
to show what I'm after.

CREATE TABLE tblService (
[CtId] [int] NOT NULL,
[Service] [int] NOT NULL,
[DateOfService] [datetime] NOT NULL
)

CREATE TABLE tblAddress (
[CtId] [int] NOT NULL,
[DateStart] [datetime] NOT NULL,
[DateStop] [datetime] NULL,
[Zip] [int] NULL
)

CREATE TABLE tblResults (
[CtId] [int],
[Service] [int],
[DateOfService] [datetime],
[Zip] [int]
)

INSERT tblService (1, 1, '3/1/2003')
INSERT tblService (1, 2, '3/10/2003')
INSERT tblService (2, 1, '4/4/2003')
INSERT tblService (2, 2, '5/3/2003')
INSERT tblService (3, 1, '3/2/2003')
INSERT tblService (4, 1, '4/4/2003')

INSERT tblAddress (1, '2/1/1995', '2/28/2003', 90028)
INSERT tblAddress (1, '3/1/2003', NULL, 90010)
INSERT tblAddress (2, '3/3/1998', '4/3/2003', 90078)
INSERT tblAddress (2, '4/4/2003', '5/1/2003', 90054)
INSERT tblAddress (2, '5/2/2003', NULL, 90043)
INSERT tblAddress (3, '3/5/2003', NULL, 90042)
INSERT tblAddress (4, '3/3/2002', '4/1/2003', 90028)
INSERT tblAddress (4, '4/2/2003', '5/30/2003', 90038)

Primary Key for all tables is CtId.

Expected Results:
tblResults:
CtId Service DateOfService Zip
1 1 3/1/2003 90010
1 2 3/10/2003 90010
2 1 4/4/2003 90054
2 2 5/3/2003 90043
3 1 3/2/2003 NULL
4 1 4/4/2003 90038

Sure would appreciate any help anyone can give me.

select s.CtID, s.Service, s.DateOfService, a.Zip

from Service s

join Address a

on s.CtId = a.CtId

where s.DateOfService >= a.DateStart

and (s.DateOfService <= a.DateStop or a.DateStop is null)


--