VB.NET中SQL语句需新增员工当月12日所在周支票判断

要实现“判断员工在指定日期范围内是否收到当月12日所在周的支票”的需求，核心是先计算每张支票所在月份的12号对应的周（周日为结束日），再统计员工是否在指定日期范围内有支票落在该周内。以下是修改后的SQL语句及说明：

关键逻辑说明 #

1. 计算当月12号：通过DATEADD和DATEDIFF获取当前CheckDate所在月份的1号，再加11天得到当月12号。
2. 确定12号所在周的起止日期：
   • 周以周日为结束日，根据12号的星期数计算该周的最后一天（周日）；
   • 该周的起始日为结束日减6天。
3. 统计符合条件的支票：通过CASE语句判断当前CheckDate是否落在上述周范围内，再用COUNT统计是否存在此类记录，最终返回'是'或'否'。

修改后的完整SQL语句 #

' 先设置周日为一周的第一天（确保DATEPART(weekday)返回1代表周日）
Dim sql As String = "SET DATEFIRST 7; " & _
"SELECT tb1.Employee, " & _
"       SUM(CASE WHEN tb1.TaxID = 'SOC' THEN tb1.Taxable Else 0 END) as txblSOC, " & _
"       SUM(CASE WHEN tb1.TaxID = 'MED' THEN tb1.Taxable Else 0 END) as txblMED, " & _
"       SUM(CASE WHEN tb1.TaxID = 'FED' THEN tb1.Taxable ELSE 0 END) as txblFED, " & _
"       COUNT(CASE WHEN tb1.CheckNumb > 0 Then tb1.PeriodEndDate END) as wks, " & _
"       -- 新增判断字段：是否收到当月12日所在周的支票
"       CASE " & _
"           WHEN COUNT(CASE " & _
"               WHEN tb2.CheckDate BETWEEN " & _
"                   DATEADD(day, -6, " & _
"                       DATEADD(day, " & _
"                           CASE DATEPART(weekday, DATEADD(day, 11, DATEADD(month, DATEDIFF(month, 0, tb2.CheckDate), 0))) " & _
"                               WHEN 1 THEN 0 " & _
"                               ELSE 7 - DATEPART(weekday, DATEADD(day, 11, DATEADD(month, DATEDIFF(month, 0, tb2.CheckDate), 0))) " & _
"                           END, " & _
"                           DATEADD(day, 11, DATEADD(month, DATEDIFF(month, 0, tb2.CheckDate), 0)) " & _
"                       ) " & _
"                   ) " & _
"                   AND " & _
"                   DATEADD(day, " & _
"                       CASE DATEPART(weekday, DATEADD(day, 11, DATEADD(month, DATEDIFF(month, 0, tb2.CheckDate), 0))) " & _
"                           WHEN 1 THEN 0 " & _
"                           ELSE 7 - DATEPART(weekday, DATEADD(day, 11, DATEADD(month, DATEDIFF(month, 0, tb2.CheckDate), 0))) " & _
"                       END, " & _
"                       DATEADD(day, 11, DATEADD(month, DATEDIFF(month, 0, tb2.CheckDate), 0)) " & _
"                   ) " & _
"               THEN 1 " & _
"               ELSE NULL " & _
"           END) > 0 " & _
"           THEN '是' " & _
"           ELSE '否' " & _
"       END AS Has12thWeekCheck, " & _
"       emp1.Name, emp1.SSN, emp1.Add1, emp1.Add2, emp1.City, emp1.State, emp1.ZipCode " & _
"FROM PRCHECKTAX tb1 " & _
"INNER JOIN PRCHECK tb2 ON tb1.Employee = tb2.Employee AND tb1.PeriodEndDate = tb2.PeriodEndDate AND tb1.CheckNumb = tb2.CheckNumb " & _
"INNER JOIN PREMPLOYEE AS emp1 ON tb1.Employee = emp1.Employee " & _
"WHERE (tb1.TaxID = 'SOC' OR tb1.TaxID = 'MED' OR tb1.TaxID = 'FED') AND tb1.Taxable <> 0 AND tb2.CheckDate BETWEEN " & sz1 & " AND " & sz2 & " " & _
"GROUP BY tb1.Employee, emp1.Name, emp1.SSN, emp1.Add1, emp1.Add2, emp1.City, emp1.State, emp1.ZipCode"

注意事项 #

• 如果使用的数据库不支持SET DATEFIRST（如Access），需要调整星期数的计算逻辑，确保周日被识别为一周的最后一天。
• 若sz1和sz2是日期参数，建议使用参数化查询而非字符串拼接，避免SQL注入风险。

内容的提问来源于stack exchange，提问作者S Stottz