T-SQL question

Given a date I need to be able to return the period in which it belongs.  table1 lists the periods.  table2 are the given dates.  I need to query table 2 and lookup the period from table1.  If the date falls between two periods, take the lower period.  Note that there is not any patterns in the spacing of periods.  See below.

thanks,
Sopheak

table1
Period      Date
1      1/1/07
2      1/13/07
3      1/16/07
4      2/22/07
5      2/27/07


table 2
Date      Period
1/2/07      1
2/1/07      3
2/5/07      3
1/15/07      2

Query (for records in table 2 derive period from table1)
Date      Period
1/2/07      1
2/1/07      3
2/5/07      3
1/15/07      2
sopheakAsked:
Who is Participating?
 
YveauConnect With a Mentor Commented:
Assuming the dates in T1 are the period breaks. That is the one period ends at the given date and the next starting on it, I come to this result:

create table #T1 (period int, [date] datetime)
insert into #T1 values (1, '1/1/07')
insert into #T1 values (2, '1/13/07')
insert into #T1 values (3, '1/16/07')
insert into #T1 values (4, '2/22/07')
insert into #T1 values (5, '2/27/07')

create table #T2 ([date] datetime)
insert into #T2 values ('1/2/07')
insert into #T2 values ('2/1/07')
insert into #T2 values ('2/5/07')
insert into #T2 values ('1/15/07')

select  Periods.Period
,       #T2.[Date]
from    #T2
,(      select  A.Period
        ,       A.[date] as [From]
        ,       B.[date] as [To]
        from    #T1 A
        ,       #T1 B
        where B.[date] = (select min([date]) from #T1 where [date] > a.[date])
        union
        select  period
        ,       [date]
        ,       '12/31/2100'
        from    #T1
        where   [date] = (select max([date]) from #T1)
        ) as Periods
where   #T2.[Date] between Periods.[From] and Periods.[To]

-->> Result:
Period      Date
----------- -----------------------
1           2007-01-02 00:00:00.000
2           2007-01-15 00:00:00.000
3           2007-02-01 00:00:00.000
3           2007-02-05 00:00:00.000

Hope this helps ...
0
 
ZberteocConnect With a Mentor Commented:
with the date from Yveau use this query:

SELECT
      [date],
      (
            SELECT TOP 1
                  period
            FROM
                  #T1
            WHERE
                  [date]<t2.[date]
            ORDER BY
                  [date] DESC
      ) AS period
FROM
      #T2 t2
ORDER BY
      period
0
 
YveauCommented:
Glad I could be of any help and thanks for the grade !
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.