时间戳

首页 > 解决方案 > CASE in WHERE 子句 SQL VBA Excel

sql - CASE in WHERE 子句 SQL VBA Excel

问题描述

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

 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 ))  "

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

标签： sqlexcelvba

解决方案

不清楚为什么你在 where 使用 case 但如果你想在两者之间使用应该是

    " 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 ) )   "

推荐阅读