CODEWITHSUNDEEP
sqldatems-access
KillerSnail
KillerSnail

Reputation: 3591

SQL query assign days to periods based on end date

I have a table that has a list of dates from now till 2015. eg. 

Date
11/1/2013
12/1/2013
13/1/2013
...
25/1/2013

I have a separate table this holds report dates. 

cutoff_ date    purpose
11/1/2013       Mid Month Report
25/1/2013       Month End Report

So I need to assign a the dates between 11/1/2013 and 25/1/2013 all to 25/1/2013 whats the best way to go about this?

Can I do it in a simple SQL query? The DB is currently in Access if that makes a difference

Upvotes: 1

Views: 283

Answers (2)

Fionnuala
Fionnuala

Reputation: 91366

There can be advantages to using subqueries, for example, you can use a parameter, which can be assigned in code, got from a form, or simply typed in. This example selects only those records that have a date that matches the cutoff_date purpose entered in the prompt [please enter purpose].

SELECT DISTINCT dates.day,
                (SELECT TOP 1 cutoff_date
                 FROM   report_dates
                 WHERE  cutoff_date >= dates.day
                        AND purpose = [please enter purpose]
                 ORDER  BY cutoff_date) AS CutOff
FROM   dates
WHERE  (SELECT TOP 1 cutoff_date
        FROM   report_dates
        WHERE  cutoff_date >= dates.day
               AND purpose = [please enter purpose]
        ORDER  BY cutoff_date) IS NOT NULL;

Upvotes: 0

HansUp
HansUp

Reputation: 97101

Use DMin to retrieve the minimum cutoff_date which is greater than or equal to [Date].

SELECT
    [Date],
    DMin("cutoff_date", "report_dates",
        "[cutoff_date] >= " & Format([Date], "\#yyyy-m-d\#")) AS report_date
FROM first_table;

Upvotes: 1

Related Questions