Query Help Request 
Author Message
 Query Help Request

Well people, here I am with another plea for assistance.  I have two tables
in a production database which I've stripped down to the bare essentials
here for purposes of this request.  The first table (tblService) contains a
record off all services given and the second table (tblAddress) has a list
peoples address which records their addresses including changes over time.
What I need is a query that will pull the data from tblService and add a
field which shows the person's zip code at the time the service was
delivered.  Anyone give me a hand.

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

Any assistance would be greatly appreciated.



Thu, 16 Feb 2006 03:18:51 GMT
 Query Help Request

Quote:

> Well people, here I am with another plea for assistance.  I have two tables
> in a production database which I've stripped down to the bare essentials
> here for purposes of this request.  The first table (tblService) contains a
> record off all services given and the second table (tblAddress) has a list
> peoples address which records their addresses including changes over time.
> What I need is a query that will pull the data from tblService and add a
> field which shows the person's zip code at the time the service was
> delivered.  Anyone give me a hand.

> 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

> Any assistance would be greatly appreciated.

INSERT INTO tblResults (CtId, Service, DateOfService, Zip)
SELECT S.CtId, S.Service, S.DateOfService, A.Zip
FROM tblService AS S
            LEFT OUTER JOIN
            tblAddress AS A
            ON S.CtId = A.CtId AND
                   S.DateOfService BETWEEN A.DateStart AND
                   COALESCE(A.DateStop, '99991231')

SELECT *
FROM tblResults
ORDER BY CtId, Service

CtId Service DateOfService Zip
1 1 2003-03-01 00:00:00.000 90010
1 2 2003-03-10 00:00:00.000 90010
2 1 2003-04-04 00:00:00.000 90054
2 2 2003-05-03 00:00:00.000 90043
3 1 2003-03-02 00:00:00.000 NULL
4 1 2003-04-04 00:00:00.000 90038

Regards,
jag



Thu, 16 Feb 2006 03:48:18 GMT
 
 [ 2 post ] 

 Relevant Pages 

1. Query help requested for Login information

2. Query help request

3. Query help request !

4. Query Help Request

5. Query Help request

6. Query Help Requested!

7. SQL QUERY Help Requested

8. Query Help Requested

9. Request help with English Query for Analysis Services

10. Urgent help request: for a VERY complex query

11. Requesting help with complicated query


 
Powered by phpBB® Forum Software