Sql 如何在VB.NET中使用MS SYS根据开始和结束期间日期进行查询

我正在try 使用VB.NET中的MS SYS根据开始和结束日期进行查询

表ABSEN可以与表HELPERABSEN合并

因此，我想从表PERIOD中获取开始日期和结束日期，其中条件基于表PERIOD中的PERIODNAME.

如果数据的开始日期和结束日期不存在于DATEABSEN字段的Absen表中，则查询根据ID2和INOUT创建新记录

我不想使用MS Access中的函数，因为我使用的是VB. NET的SQL.

如果还有其他方法请指导我

谢谢

Public Class Form1
    Private AbsenService As New ABSENservice()
    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        DataGridView1.DataSource = AbsenService.GetAbsenfull()
    End Sub
End Class
Public Class ABSENservice
    Public Function GetOledbConnectionString() As String
        Return "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\trialabsen.accdb;Persist Security Info=False;"
    End Function
    Private ReadOnly _conn As OleDbConnection
    Private _connectionString As String = GetOledbConnectionString()
    Public Sub New()
        _conn = New OleDbConnection(_connectionString)
    End Sub
    Public Function GetAbsenfull() As IEnumerable(Of ABSEN)
        Dim sql = $"SELECT ABSEN.ID2 AS [ID2],MASTERID2.NAMEID2 AS [NAMEID2],ABSEN.INOUT AS [INOUT],ABSEN.DATEABSEN AS [DATEABSEN],Format(ABSEN.TIME,'hh:mm:ss') AS [TIME],ABSEN.STATUS AS [STATUS] FROM ABSEN INNER JOIN MASTERID2 ON ABSEN.ID2 = MASTERID2.ID2"
        Using _conn = New OleDbConnection(GetOledbConnectionString())
            Return _conn.Query(Of ABSEN)(sql).ToList()
        End Using
    End Function
End Class
Public Class ABSEN
    Public Property ID() As Integer
    Public Property ID2() As String
    Public Property NAMEID2() As String
    Public Property INOUT() As String
    Public Property DATEABSEN() As DateTime
    Public Property TIME() As String
    Public Property STATUS() As String

End Class

来自代码的结果

样本数据

表ABSEN

表HELPERABSEN

表MASTERID2

表PERIOD

已确定的结果

但我想基于每个时期