?
Solved

T-SQL question

Posted on 2007-10-02
3
Medium Priority
?
297 Views
Last Modified: 2010-03-19
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
0
Comment
Question by:sopheak
  • 2
3 Comments
 
LVL 18

Accepted Solution

by:
Yveau earned 500 total points
ID: 20002120
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
 
LVL 27

Assisted Solution

by:Zberteoc
Zberteoc earned 500 total points
ID: 20002331
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
 
LVL 18

Expert Comment

by:Yveau
ID: 20009899
Glad I could be of any help and thanks for the grade !
0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question