计算关联名称（VBA，SQL）的连续日期实例

admin

计算关联名称（VBA，SQL）的连续日期实例

sql

我正在尝试从数据集中确定连续日期（不包括星期日）的实例。数据存储在Access中，我将所需的日期拖入Excel。然后，我试图确定每个人在提供的数据中有多少个实例。下面的例子。

数据示例：

| Name | Date of absence|
| Bob  | 02/01/17       | 
| Jill | 02/01/17       |
| Bob  | 03/01/17       |
| Jill | 04/01/17       |

结果示例：

Bob - 1 Instance, 2 days
Jill - 2 Instance, 2 days

我开始尝试使用Excel在VBA中使用循环通过循环遍历每个缺勤实例，直到所有人都完成工作/被淘汰为止，但是代码变得非常繁琐，而且效率非常低，更不用说它这么慢了获取更大的数据集！我想知道是否可以查询数据库中的信息或编写一些更有效的东西。

任何帮助或建议，将不胜感激！

更新：

建议；
Sql = "SELECT Absence.Racf,count(RecordDate) as dups" Sql = Sql & " FROM Absence" Sql = Sql & " left outer join" Sql = Sql & " (select Racf, [RecordDate]+IIf(Weekday([RecordDate],7)=1,2,1) as date1 from Absence) t1" Sql = Sql & " on Absence.RecordDate=t1.date1 and Absence.Racf=t1.Racf" Sql = Sql & " where date1 Is Not Null" Sql = Sql & " group by Absence.Racf"

但不幸的是，它在下面的日期列表中返回7，而不是5。

日期：16年2月23日，24年2月16日，08年8月16日，09年8月16日，10年8月16日，16年8月31日，16年10月24日，16年10月25日，
2016年10月26日，17年1月25日，17年1月26日，17年1月27日

阅读 176

2021-07-01

共1个答案

admin

因此，这就是SQL在Access查询中的实际外观

SELECT table1.name,count(date) as dups
FROM Table1
left outer join
(select name, [date]+IIf(Weekday([Date],7)=1,2,1) as date1 from table1) t1
on table1.date=t1.date1 and table1.name=t1.name    
where date1 is not null
group by table1.name
;

如果要使用宏从Excel中运行这个，这里是一个有益的参考。

我从那里取出代码，并将设置SQL查询字符串的行更改为

SQL = "SELECT table1.name,count(date) as dups"
SQL = SQL & " FROM table1"
SQL = SQL & " left outer join"
SQL = SQL & " (select name, [date]+IIf(Weekday([Date],7)=1,2,1) as date1 from table1) t1"
SQL = SQL & " on table1.date=t1.date1 and table1.name=t1.name"
SQL = SQL & " where date1 Is Not Null"
SQL = SQL & " group by table1.name"

而且效果很好。

如果要获取长度大于一的序列，请尝试此操作

SELECT Absence.Racf, Count(Absence.RecordDate) AS CountOfRecordDate
FROM (Absence LEFT JOIN (select Racf, RecordDate+IIf(Weekday([RecordDate],7)=1,2,1) as RecordDate1 from Absence) AS t1 ON (Absence.RecordDate = t1.RecordDate1) AND (Absence.Racf = t1.Racf))
LEFT JOIN (select Racf, [RecordDate]-IIf(Weekday([RecordDate],2)=1,2,1) as RecordDate2 from Absence) AS t2 ON (Absence.RecordDate = t2.RecordDate2) AND (Absence.Racf = t2.Racf)
WHERE (((t1.RecordDate1) Is Not Null) AND ((t2.RecordDate2) Is Null))
GROUP BY Absence.Racf;

如果您要获取一个或多个连续日期的序列，则使用此方法

SELECT Absence.Racf, Count(Absence.RecordDate) AS CountOfRecordDate
FROM Absence LEFT JOIN (select Racf, [RecordDate]+IIf(Weekday([RecordDate],7)=1,2,1) as RecordDate2 from Absence) AS t2 ON (Absence.RecordDate = t2.RecordDate2) AND (Absence.Racf = t2.Racf)
WHERE (((t2.RecordDate2) Is Null))
GROUP BY Absence.Racf;

像以前一样添加到SQL字符串中。

2021-07-01