T-SQL question

Posted on 2007-10-02
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.


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
Question by:sopheak
    LVL 18

    Accepted Solution

    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])
            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 ...
    LVL 26

    Assisted Solution

    with the date from Yveau use this query:

                SELECT TOP 1
                ORDER BY
                      [date] DESC
          ) AS period
          #T2 t2
    LVL 18

    Expert Comment

    Glad I could be of any help and thanks for the grade !

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    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…
    This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
    In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    733 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

    Need Help in Real-Time?

    Connect with top rated Experts

    25 Experts available now in Live!

    Get 1:1 Help Now