sql - CASE in WHERE 子句 SQL VBA Excel

Question

我正在尝试在 WHERE 中添加当前会计年度的过滤条件。我的代码中的声明。但是，我不知道如何集成 CASE我的 WHERE 中的声明VBA 中的子句。这是我正在处理的代码:

 Source = "SELECT tblretirements.retirementID As `RetirementID`,tblretirements.InputBy As `Input By`, tblretirements.ReceiptDate As `Date Received` , tblretirements.FirstName As `First Name`, tblretirements.LastName As `Last Name`, tblretirements.DateOfBirth As `DOB`, tblretirements.DateOfRetirement As `Retirement Date`, tblBenefitInProcress.RetirementDescription As `Retirement Type`, tblretirements.MemberPIN As `Pin`, tblcurrentplan.CurrentPlan As `Current Plan`, tblretirements.ServiceCredits As `Service Crdits w/o PS/AC`, tblretirements.MultiplePlans As `Multiple Plans/Tiers?`, " & _
    " tblretirements.ConfirmationLetterMailed As `Mailed Confirm Letter`, tblretirements.WorkbookSetup As `Set Up Excel Workbook`, tblCalculationTeam.CalcTeam As `Calculation Team`, tblCalculationPersonnel.Name As `Assigned Staff`, tblretirements.ReviewDate As`Review Date`, tblBoardStatus.StatusReported As`BoardStatusID`, tblretirements.ReciprocityID As `Reciprocity?`, tblretirements.EmployerCodeID As `Employer`, " & _
    " tblDepartment.DepartmentName As `Department`, tblretirements.PendingPurchase As `Pending Purchase(s)?`, tblJoinder.Joinder As `Joinder or Pending DRO?`, tblDisabilityPending.AgendaDisability As `DisabilityID`, tblReciprocity.ReciprocalStatus, tblOrientationStatus.OrientationStatus As `Orientation Elected?`,tblretirements.OrientationDate As `Orientation Date`, tblReviewPersonnel.ReviewerName As `Designated Reviewer`, tblretirements.FileSetupApproved As `File Set-Up Approved`, tblretirements.AgendaApplication As `Agenda Application`, tblretirements.EstimateToReviewer As `Estimate to Reviewer`, tblretirements.EstimateToSupervisor As `Estimate to Supervisor`, " & _
    " tblretirements.EstimateApproved As `Estimate Approved`, tblretirements.FinalPaycheck As `Final Paycheck Date`, tblretirements.FinalService As `Final Service with PS/AC`, tblretirements.FinalToReviewer As `Final Calc to Reviewer`, tblretirements.FinalToSupervisor As `Final Calc to Supervisor`, tblretirements.FinalApproved As `Final Calc Approved`, tblretirements.ApplicationCancelled As `App Cancelled by Member?`, tblretirements.RetElectionDistributed As `Retirement Election Distributed`, tblretirements.RetElectionReturned As `Retirement Election Returned`, tblPaymentOption.AgendaOption As `Option/Payment Selected`, " & _
    " tblretirements.TempAnnuityID As `Age Request for Temp Annuity`, tblretirements.FinalAllowance As `Final Allowance Calculation`, tblretirements.Continuance As `Continuance`, tblretirements.PayrollFormsStaff As `Payroll Forms Completed (Staff)`, tblretirements.AgendaPayment As `Option-Payment`, tblretirements.PayrollFormsSupervisor As `Payroll Forms Reviewed (Supv)`, " & _
    " tblretirements.CboApprovedAllowance As `Allowance Approved (CBO)`, tblretirements.AllowanceEstimated As `Allowance Estimated?`, tblretirements.AllowEnteredInPayroll As `Allowance Entered In Payroll`, tblretirements.DistributionCycleID As `Distribution Cycle for 1st Payment`, " & _
    " tblretirements.FirstPayDate As `Distribution Date for 1st Payment`, tblretirements.AllowanceFinalized As `Allowance Finalized`, tblretirements.FileImaged As `Retirement File Imaged`" & _
    " FROM ((((((((((((tblRetirements " & _
    " LEFT JOIN tblCalculationPersonnel On tblRetirements.CoordinatorID=tblCalculationPersonnel.CoordinatorID) " & _
    " LEFT JOIN tblCurrentPlan On tblRetirements.CurrentPlanID=tblCurrentPlan.CurrentPlanID) " & _
    " LEFT JOIN tblBenefitInProcress On tblRetirements.BenefitInProcess=tblBenefitInProcress.RetirementTypeID) " & _
    " LEFT JOIN tblPaymentOption ON tblretirements.OptionID=tblPaymentoption.OptionID) " & _
    " LEFT JOIN tblReviewPersonnel ON tblretirements.ReviewerID=tblReviewPersonnel.ReviewerID) " & _
    " LEFT JOIN tblOrientationStatus ON tblretirements.OrientationID=tblOrientationStatus.OrientationID) " & _
    " LEFT JOIN tblDisabilityPending ON tblretirements.DisabilityID=tblDisabilityPending.DisabilityID) " & _
    " LEFT JOIN tblJoinder ON tblretirements.JoinderID=tblJoinder.JoinderID) " & _
    " LEFT JOIN tblDepartment ON tblretirements.DepartmentID=tblDepartment.DepartmentID) " & _
    " LEFT JOIN tblEmployerCode ON tblretirements.EmployerCodeID=tblEmployerCode.EmployerCodeID) " & _
    " LEFT JOIN tblReciprocity ON tblretirements.ReciprocityID=tblReciprocity.ReciprocityID) " & _
    " LEFT JOIN tblCalculationTeam ON tblretirements.CalculationTeamID=tblCalculationTeam.CalculationTeamID) " & _
    " LEFT JOIN tblBoardStatus ON tblretirements.BoardStatusID=tblBoardStatus.BoardStatusID " & _

这是我需要帮助的:

 " WHERE tblretirements.ApplicationCancelled = 'No' AND (tblretirements.ReceiptDate IS NULL OR tblretirements.ReceiptDate " & _
        " BETWEEN (CASE WHEN MONTH(getdate()) < 7 THEN DATEFROMPARTS(YEAR(getdate())-1,7,1) " & _
        " ELSE DATEFROMPARTS(YEAR(getdate()),7,1) End ) AND  (CASE WHEN MONTH(getdate()) < 7 THEN DATEFROMPARTS(YEAR(getdate()),6,30) " & _
        " ELSE DATEFROMPARTS(YEAR(getdate())+1,6,30)End ))  "

有人可以帮帮我吗？谢谢你。

Answer 1

这里需要注意几个问题:

对于许多连接的复杂查询，不要在应用程序代码期间动态构建 SQL。将其保存为 MS Access 中的存储对象，以便引擎可以保存最佳执行计划。然后让应用程序(这里是 Excel)按名称引用查询。您甚至可以将值作为参数传递给已保存的查询。

使用表别名来避免写出长表名，以便更易读、更易于维护且查询时间更短。

没有两种 SQL 方言是相同的。即使在同一供应商下，这里是微软。 MS Access 使用的 JET/ACE SQL(Windows .dll 文件)方言不同于 SQL Server 使用的 T-SQL 方言。有趣的是，另一个数据库 Sybase 也使用 T-SQL，并说明了原因。

但就像所有其他方言(例如，Oracle、Postgres)一样，它们都共享大多数传统的 ANSI SQL 函数，这是该语言的标准。因此，SQL Server 的 CASE , DATEFROMPARTS() , 和 GETDATE()必须替换 Access' IIF() , DATESERIAL() , 和 DATE() .有趣的是，CASE是一种 ANSI 方法。

考虑以下对查询的重写，特别是 WHERE子句作为存储对象保存在 MS Access 中。

SQL

SELECT r.retirementid             AS `RetirementID`, 
       r.inputby                  AS `Input By`, 
       r.receiptdate              AS `Date Received`, 
       r.firstname                AS `First Name`, 
       r.lastname                 AS `Last Name`, 
       r.dateofbirth              AS `DOB`, 
       r.dateofretirement         AS `Retirement Date`, 
       bp.retirementdescription   AS `Retirement Type`, 
       r.memberpin                AS `Pin`, 
       cr.currentplan             AS `Current Plan`, 
       r.servicecredits           AS `Service Crdits w/o PS/AC`, 
       r.multipleplans            AS `Multiple Plans/Tiers?`, 
       r.confirmationlettermailed AS `Mailed Confirm Letter`, 
       r.workbooksetup            AS `Set Up Excel Workbook`, 
       ct.calcteam                AS `Calculation Team`, 
       co.name                    AS `Assigned Staff`, 
       r.reviewdate               AS`Review Date`, 
       bs.statusreported          AS`BoardStatusID`, 
       r.reciprocityid            AS `Reciprocity?`, 
       r.employercodeid           AS `Employer`, 
       d.departmentname           AS `Department`, 
       r.pendingpurchase          AS `Pending Purchase(s)?`, 
       j.joinder                  AS `Joinder or Pending DRO?`, 
       dp.agendadisability        AS `DisabilityID`, 
       ry.reciprocalstatus, 
       o.orientationstatus        AS `Orientation Elected?`, 
       r.orientationdate          AS `Orientation Date`, 
       rp.reviewername            AS `Designated Reviewer`, 
       r.filesetupapproved        AS `File Set-Up Approved`, 
       r.agendaapplication        AS `Agenda Application`, 
       r.estimatetoreviewer       AS `Estimate to Reviewer`, 
       r.estimatetosupervisor     AS `Estimate to Supervisor`, 
       r.estimateapproved         AS `Estimate Approved`, 
       r.finalpaycheck            AS `Final Paycheck Date`, 
       r.finalservice             AS `Final Service with PS/AC`, 
       r.finaltoreviewer          AS `Final Calc to Reviewer`, 
       r.finaltosupervisor        AS `Final Calc to Supervisor`, 
       r.finalapproved            AS `Final Calc Approved`, 
       r.applicationcancelled     AS `App Cancelled by Member?`, 
       r.retelectiondistributed   AS `Retirement Election Distributed`, 
       r.retelectionreturned      AS `Retirement Election Returned`, 
       p.agendaoption             AS `Option/Payment Selected`, 
       r.tempannuityid            AS `Age Request for Temp Annuity`, 
       r.finalallowance           AS `Final Allowance Calculation`, 
       r.continuance              AS `Continuance`, 
       r.payrollformsstaff        AS `Payroll Forms Completed (Staff)`, 
       r.agendapayment            AS `Option-Payment`, 
       r.payrollformssupervisor   AS `Payroll Forms Reviewed (Supv)`, 
       r.cboapprovedallowance     AS `Allowance Approved (CBO)`, 
       r.allowanceestimated       AS `Allowance Estimated?`, 
       r.allowenteredinpayroll    AS `Allowance Entered In Payroll`, 
       r.distributioncycleid      AS `Distribution Cycle for 1st Payment`, 
       r.firstpaydate             AS `Distribution Date for 1st Payment`, 
       r.allowancefinalized       AS `Allowance Finalized`, 
       r.fileimaged               AS `Retirement File Imaged` 
FROM   ((((((((((((tblretirements r 
                   LEFT JOIN tblcalculationpersonnel cp 
                          ON r.coordinatorid = co.coordinatorid) 
                  LEFT JOIN tblcurrentplan cr 
                         ON r.currentplanid = cr.currentplanid) 
                 LEFT JOIN tblbenefitinprocress bp 
                        ON r.benefitinprocess = bp.retirementtypeid) 
                LEFT JOIN tblpaymentoption p 
                       ON r.optionid = p.optionid) 
               LEFT JOIN tblreviewpersonnel rp 
                      ON r.reviewerid = rp.reviewerid) 
              LEFT JOIN tblorientationstatus o 
                     ON r.orientationid = o.orientationid) 
             LEFT JOIN tbldisabilitypending dp 
                    ON r.disabilityid = dp.disabilityid) 
            LEFT JOIN tbljoinder j 
                   ON r.joinderid = j.joinderid) 
           LEFT JOIN tbldepartment dt 
                  ON r.departmentid = d.departmentid) 
          LEFT JOIN tblemployercode e 
                 ON r.employercodeid = e.employercodeid) 
         LEFT JOIN tblreciprocity ry 
                ON r.reciprocityid = ry.reciprocityid) 
        LEFT JOIN tblcalculationteam ct 
               ON r.calculationteamid = ct.calculationteamid) 
       LEFT JOIN tblboardstatus bs 
              ON r.boardstatusid = bs.boardstatusid 

地点 条款

WHERE r.applicationcancelled = 'No' 
  AND r.receiptdate IS NULL 
   OR r.receiptdate BETWEEN (
                             IIF(MONTH(DATE()) < 7, 
                                 DATESERIAL(YEAR(DATE()) - 1, 7, 1), 
                                 DATESERIAL(YEAR(DATE()), 7, 1)) 
                            )
                        AND (
                             IIF(MONTH(DATE()) < 7, 
                                 DATESERIAL(YEAR(DATE()), 6, 30), 
                                 DATESERIAL(YEAR(DATE()) + 1, 6, 30)) 
                            )