时间戳

首页 > 解决方案 > 如何填补 Access 表中的每日日期空白？

sql - 如何填补 Access 表中的每日日期空白？

问题描述

我有一个名为 Test三列的表：Account, Date, and Amount. 日期如下所示：

Account  Date            Amount
1        25/01/2013      5000
1        20/01/2013      3000
2        25/01/2016      4000
2        20/01/2016      1000

基本上，金额只有在发生变化时才会记录下来。例如，对于Account 1，Amount从 20/01/2013 到 24/01/2013 是 3000，然后它变为 5000，因此，我们有记录。

我想完成每个记录从第一条记录到最后一条记录的天数account

例如，对于Account 1，我想要从 20/01/2013 到 24/01/2013 的所有天数Date和 3000天数Amount

我知道这是一个棘手的问题，非常感谢您的帮助。非常感谢 ！！

标签： sqlms-access

解决方案

你只需要找到第一个和最后一个日期。反正：

首先，找到最小和最大日期：

SELECT 
    Account, 
    Min(tblProvider.[Entry Date]) AS FirstDate, 
    Max(tblProvider.[Entry Date]) AS LastDate
FROM 
    Test
GROUP BY 
    Account;

将此保存为qDateMinMax。

然后创建因子查询：

SELECT DISTINCT 
    [Thousands]+[Hundreds]+[Tens]+[Ones] AS Factor, 
    1000*Abs([Mille].[id] Mod 10) AS Thousands, 
    100*Abs([Centa].[id] Mod 10) AS Hundreds, 
    10*Abs([Deca].[id] Mod 10) AS Tens, 
    Abs([Uno].[id] Mod 10) AS Ones
FROM 
    MSysObjects AS Uno, 
    MSysObjects AS Deca,
    MSysObjects AS Centa, 
    MSysObjects AS Mille;

将此另存为qdxFactor。

现在，组装这些：

SELECT 
    qDateMinMax.Account, 
    qDateMinMax.FirstDate, 
    qDateMinMax.LastDate, 
    DateAdd("d",[Factor],[FirstDate]) AS OtherDate,
        (Select Last(Amount) 
        From Test 
        Where Test.Account = qDateMinMax.Account 
        And Test.[Entry Date] = qDateMinMax.LastDate) As LastAmount
FROM 
    qDateMinMax, 
    qdxFactor
WHERE 
    DateAdd("d",[Factor],[FirstDate]) <= [LastDate]
ORDER BY 
    qDateMinMax.Account, 
    DateAdd("d",[Factor],[FirstDate]);

对于中位数：

SELECT 
    qDateMinMax.Account, 
    qDateMinMax.FirstDate, 
    qDateMinMax.LastDate, 
    DateAdd("d",[Factor],[FirstDate]) AS OtherDate, 
        (Select (Min(Amount) + Max(Amount)) / 2
        From Test 
        Where Test.Client = qDateMinMax.Account 
        And Test.[Entry Date] Between qDateMinMax.FirstDate And qDateMinMax.LastDate) AS MedAmount
FROM 
    qDateMinMax, 
    qdxFactor
WHERE 
    DateAdd("d",[Factor],[FirstDate]) <= [LastDate]
ORDER BY 
    qDateMinMax.Account, 
    DateAdd("d",[Factor],[FirstDate]);

按年月分组：

SELECT DISTINCT 
    qDateMinMax.Account, 
    Format([FirstDate],"yyyymm") AS FirstYM, 
    Format([LastDate],"yyyymm") AS LastYM, 
    Format(DateAdd("d",[Factor],[FirstDate]),"yyyymm") AS YM, 
        (Select (Min(Amount) + Max(Amount)) / 2
        From Test 
        Where Test.Client = qDateMinMax.Account 
        And Test.[Entry Date] Between qDateMinMax.FirstDate And qDateMinMax.LastDate) AS MedAmount
FROM 
    qDateMinMax, 
    qdxFactor
WHERE 
    DateAdd("d",[Factor],[FirstDate]) <= [LastDate];

要包括最后一个金额：

SELECT DISTINCT 
    qDateMinMax.Account, 
    Format([FirstDate],"yyyymm") AS FirstYM, 
    Format([LastDate],"yyyymm") AS LastYM, 
    Format(DateAdd("d",[Factor],[FirstDate]),"yyyymm") AS YM, 
        (Select (Min(Amount) + Max(Amount)) / 2
        From Test 
        Where Test.Client = qDateMinMax.Account 
        And Test.[Entry Date] Between qDateMinMax.FirstDate And qDateMinMax.LastDate) AS MedAmount, 
        (Select Amount
        From Test 
        Where Test.Client = qDateMinMax.Account 
        And Test.[Entry Date] =LastDate) AS LastAmount
FROM 
    qDateMinMax, 
    qdxFactor
WHERE 
    DateAdd("d",[Factor],[FirstDate] <= [LastDate];

推荐阅读