Table with dates, table with week numbers, join together?

I have two tables. Table 1:

StuAp_Id    StuAp_StaffID   StuAp_Date  StuAp_Attended
16          77000002659366  2011-09-07  Yes
17          77000002659366  2011-09-14  Yes
18          77000002659366  2011-09-14  Yes
19          77000002659366  2011-09-14  No
20          77000001171783  2011-09-19  Yes

Table 2:

Year    Week    Start
2011    1   2011-09-05 00:00:00.000
2011    2   2011-09-12 00:00:00.000
2011    3   2011-09-19 00:00:00.000
2011    4   2011-09-26 00:00:00.000
2011    5   2011-10-03 00:00:00.000
2011    6   2011-10-10 00:00:00.000
2011    7   2011-10-17 00:00:00.000
2011    8   2011-10-24 00:00:00.000
2011    9   2011-10-31 00:00:00.000

开发者_如何学CHow would I join these two tables to make something like this:

StuAp_Id    StuAp_StaffID   StuAp_Date  StuAp_Attended  Week
16          77000002659366  2011-09-07  Yes             1
17          77000002659366  2011-09-14  Yes             2
18          77000002659366  2011-09-14  Yes             2
19          77000002659366  2011-09-14  No              2
20          77000001171783  2011-09-19  Yes             3

Thanks in advance

You can write simple INNER JOIN using GROUP BY clause.

SELECT  Table1.*
        ,MAX(WEEK) AS WEEK 
            FROM Table1
                    INNER JOIN Table2 ON STUAP_DATE >= START 
            GROUP BY STUAP_ID,STUAP_STAFFID,STUAP_DATE,STUAP_ATTENDED

don't know about specifics on sql2k5 (don't have one around to test) but I would use a sub select eg.

select table_1.*, 
       [week] = (select isnull(max([week]), 0) 
                   from table_2 
                  where table_1.StuAp_Date >= table_2.start)
  from table_1

CTEs to the rescue!

create table StuAp (
    StuAp_Id        int,
    StuAp_StaffID   bigint,
    StuAp_Date      datetime,
    StuAp_Attended  varchar(3)
)

create table Weeks (
    Year    int,
    Week    int,
    Start   datetime
)

insert into StuAp
values (16, 77000002659366, {d '2011-09-07'}, 'Yes'),
    (17, 77000002659366, {d '2011-09-14'}, 'Yes'),
    (18, 77000002659366, {d '2011-09-14'}, 'Yes'),
    (19, 77000002659366, {d '2011-09-14'}, 'No'),
    (20, 77000001171783, {d '2011-09-19'}, 'Yes')

insert into Weeks
values (2011, 1, {d '2011-09-05'}),
(2011, 2, {d '2011-09-12'}),
(2011, 3, {d '2011-09-19'}),
(2011, 4, {d '2011-09-26'}),
(2011, 5, {d '2011-10-03'}),
(2011, 6, {d '2011-10-10'}),
(2011, 7, {d '2011-10-17'}),
(2011, 8, {d '2011-10-24'}),
(2011, 9, {d '2011-10-31'})



;with OrderedWeeks as (
    select ROW_NUMBER() OVER (ORDER BY year, week) as row, w.*
    from Weeks w
), Ranges as (
    select w1.*, w2.Start as Finish
    from OrderedWeeks w1 inner join
        OrderedWeeks w2 on w1.row = w2.row - 1
)
select s.StuAp_Id, s.StuAp_StaffID, s.StuAp_Date, s.StuAp_Attended, r.Week
from StuAp s inner join
    Ranges r on s.StuAp_Date >= r.Start and s.StuAp_Date < r.Finish

This should scale quite well too.

Honestly though, if you find yourself doing queries like this often, you should really consider changing the stucture of your Weeks table to include a finish date. You could even make it an indexed view, or (assuming that the data changes rarely), you could keep your original table and use triggers or a SQL Agent job to keep a copy that contains Finish up to date.

SET ANSI_WARNINGS ON;
GO

DECLARE @Table1 TABLE
(
     StuAp_Id       INT PRIMARY KEY
    ,StuAp_StaffID  NUMERIC(14,0) NOT NULL
    ,StuAp_Date     DATETIME NOT NULL
    ,StuAp_Attended VARCHAR(3) NOT NULL
    ,StuAp_DateOnly AS DATEADD(DAY, DATEDIFF(DAY,0,StuAp_Date), 0) PERSISTED
);

INSERT  @Table1 
SELECT  16,77000002659366  ,'2011-09-07','Yes'
UNION ALL
SELECT  17,77000002659366  ,'2011-09-14','Yes'
UNION ALL
SELECT  18,77000002659366  ,'2011-09-14','Yes'
UNION ALL
SELECT  19,77000002659366  ,'2011-09-14','No'
UNION ALL
SELECT  20,77000001171783  ,'2011-09-19','Yes';

DECLARE @Table2 TABLE
(
     Year   INT NOT NULL
    ,Week    INT NOT NULL
    ,Start  DATETIME NOT NULL
    ,[End] AS DATEADD(DAY,6,Start) PERSISTED
    ,PRIMARY KEY(Year, Week)
    ,UNIQUE(Start)
);

INSERT  @Table2
SELECT  2011,1   ,'2011-09-05 00:00:00.000'
UNION ALL
SELECT  2011,2   ,'2011-09-12 00:00:00.000'
UNION ALL
SELECT  2011,3   ,'2011-09-19 00:00:00.000'
UNION ALL
SELECT  2011,4   ,'2011-09-26 00:00:00.000'
UNION ALL
SELECT  2011,5   ,'2011-10-03 00:00:00.000'
UNION ALL
SELECT  2011,6   ,'2011-10-10 00:00:00.000'
UNION ALL
SELECT  2011,7   ,'2011-10-17 00:00:00.000'
UNION ALL
SELECT  2011,8   ,'2011-10-24 00:00:00.000'
UNION ALL
SELECT  2011,9   ,'2011-10-31 00:00:00.000';

--Solution 1 : if StuAp_Date has only date part
SELECT  a.*, b.Week
FROM    @Table1 a
INNER JOIN @Table2 b ON a.StuAp_Date BETWEEN b.Start AND b.[End]

--Solution 2 : if StuAp_Date has only date part
SELECT  a.*, b.Week
FROM    @Table1 a
INNER JOIN @Table2 b ON a.StuAp_Date BETWEEN b.Start AND DATEADD(DAY,6,b.Start)

--Solution 3 : if StuAp_Date has date & time 
SELECT  a.*, b.Week
FROM    @Table1 a
INNER JOIN @Table2 b ON a.StuAp_DateOnly BETWEEN b.Start AND b.[End]

--Solution 4 : if StuAp_Date has date & time 
SELECT  a.*, b.Week
FROM    @Table1 a
INNER JOIN @Table2 b ON DATEADD(DAY, DATEDIFF(DAY,0,a.StuAp_Date), 0) BETWEEN b.Start AND DATEADD(DAY,6,b.Start)