重复输入的每一行，并添加一个额外的字段，该字段在给定的开始日期和结束日期之间的某一天进行计数 [英] Duplicate each row pulled in and add an extra field counting up one day between a given start and end date

问题描述

我正在使用Microsoft Access，我想创建一个查询，该查询重复从表中提取的每一行，并在给定开始日期和结束日期之间的一天进行计数时添加一个额外的字段.

因此，如果在开始日期和结束日期之间有4天，则每条记录将返回4个重复的行，唯一的不同是添加的日期字段累加了1天.

这实际上可行吗?

解决方案

如果您有一个日历表，其中包含您需要的每个日期的行，则此任务非常容易. (出于其他原因，请参阅此Stack Overflow答案，因为日历表可能有用.)在Access SQL中不可行.

使用我的日历表以及 YourTable ...

中的示例数据

 fld1 start_date end_date
---- ---------- ----------
a    12/26/2013 12/26/2013
b    12/27/2014 12/28/2014
c      1/1/2014   1/3/2014
d      1/4/2014   1/1/2014
 

...下面的查询给了我这个输出.

fld1 start_date end_date   added_date
---- ---------- ---------- ----------
a    12/26/2013 12/26/2013 12/26/2013
b    12/27/2014 12/28/2014 12/27/2014
b    12/27/2014 12/28/2014 12/28/2014
c      1/1/2014   1/3/2014   1/1/2014
c      1/1/2014   1/3/2014   1/2/2014
c      1/1/2014   1/3/2014   1/3/2014

请注意，输出中不包含"d" 行，因为end_date在该行中早于start_date.您可以包括表级验证规则，以强制要求每行start_date< = end_date.

 SELECT
    y.fld1,
    y.start_date,
    y.end_date,
    c.the_date AS added_date
FROM
    tblCalendar AS c
    INNER JOIN YourTable AS y
    ON
            c.the_date >= y.start_date
        AND c.the_date <= y.end_date
ORDER BY
    y.fld1,
    c.the_date;
 

I'm using Microsoft Access and I want to create a query that duplicates each row pulled in from a table with an extra field added on counting up one day between a given start and end date.

So if there's 4 days between the start and end date there's 4 duplicate rows returned for each record with only difference being the added date field counting up 1 day.

Is this actually possible to do?

解决方案

This task is fairly easy if you have a calendar table which includes a row for each date you need. (See this Stack Overflow answer for other reasons why a calendar table can be useful.) Without such a table, this task is not practical with Access SQL.

Using my calendar table and this sample data in YourTable ...

fld1 start_date end_date
---- ---------- ----------
a    12/26/2013 12/26/2013
b    12/27/2014 12/28/2014
c      1/1/2014   1/3/2014
d      1/4/2014   1/1/2014

... the query below gave me this output.

fld1 start_date end_date   added_date
---- ---------- ---------- ----------
a    12/26/2013 12/26/2013 12/26/2013
b    12/27/2014 12/28/2014 12/27/2014
b    12/27/2014 12/28/2014 12/28/2014
c      1/1/2014   1/3/2014   1/1/2014
c      1/1/2014   1/3/2014   1/2/2014
c      1/1/2014   1/3/2014   1/3/2014

Notice the "d" row was not included in the output because end_date was earlier than start_date in that row. You can include a table-level validation rule to enforce the requirement that start_date <= end_date in every row.

SELECT
    y.fld1,
    y.start_date,
    y.end_date,
    c.the_date AS added_date
FROM
    tblCalendar AS c
    INNER JOIN YourTable AS y
    ON
            c.the_date >= y.start_date
        AND c.the_date <= y.end_date
ORDER BY
    y.fld1,
    c.the_date;

这篇关于重复输入的每一行，并添加一个额外的字段，该字段在给定的开始日期和结束日期之间的某一天进行计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！