使用两个标准的声明SQL

Question

除了上一个语句外，我几乎都有一切。

我希望查询能够进一步缩小搜索范围并发表声明，该声明说，如果AgencyProfileSetupComplete中的一项记录为0，请查看Agency ProfilecreateatedDate中的日期是否小于90天的日期，是否是我想还包括这些结果吗？

select 
    distinct agencyprofilename, 
    (select 
         top 1 agencypayperiodfinalisestate 
     from 
       agencypayperiod as app 
     where 
       app.agencyprofileid = ap.agencyprofileid 
     order by 
       agencypayperiodpaydate)as 'Pay Period Finalise State', 
     agencyprofilecreateddate, agencyprofilesetupcomplete, 
     agencyemployertaxoffice
from 
    Agencyprofile as ap
left join 
   agencypayperiod as app on ap.agencyprofileid = app.agencyprofileid
left outer join 
   agencyemployer as ae on ap.agencyemployerid = ae.agencyemployerid
where agencypayperiodfinalisestate <>0
   and agencyemployertaxoffice is not null
   and agencyemployertaxoffice <>''
   or (agencyprofilesetupcomplete = 0 and agencyprofilecreateddate >= 
           (GetDate()-90))
order by agencyprofilename

如果您需要知道，我也使用Microsoft SQL Server。

Answer 1

我认为您可以通过对第一组状况进行分组来尝试。例如

SELECT DISTINCT agencyprofilename
    ,(
        SELECT TOP 1 agencypayperiodfinalisestate
        FROM agencypayperiod AS app
        WHERE app.agencyprofileid = ap.agencyprofileid
        ORDER BY agencypayperiodpaydate
        ) AS 'Pay Period Finalise State'
    ,agencyprofilecreateddate
    ,agencyprofilesetupcomplete
    ,agencyemployertaxoffice
FROM Agencyprofile AS ap
LEFT JOIN agencypayperiod AS app ON ap.agencyprofileid = app.agencyprofileid
LEFT OUTER JOIN agencyemployer AS ae ON ap.agencyemployerid = ae.agencyemployerid
WHERE (
        agencypayperiodfinalisestate <> 0
        AND agencyemployertaxoffice IS NOT NULL
        AND agencyemployertaxoffice <> ''
        )
    OR (
        agencyprofilesetupcomplete = 0
        AND agencyprofilecreateddate < DATEADD(dd,1,(GetDate() - 90))
        )
ORDER BY agencyprofilename

Answer 2

请尝试以下查询。

我没有机会对此进行测试。让我知道您是否遇到任何问题。

select 
    distinct agencyprofilename, 
    (select 
         top 1 agencypayperiodfinalisestate 
     from 
       agencypayperiod as app 
     where 
       app.agencyprofileid = ap.agencyprofileid 
     order by 
       agencypayperiodpaydate)as 'Pay Period Finalise State', 
     agencyprofilecreateddate, agencyprofilesetupcomplete, 
     agencyemployertaxoffice
from 
    Agencyprofile as ap
left join 
   agencypayperiod as app on ap.agencyprofileid = app.agencyprofileid
left outer join 
   agencyemployer as ae on ap.agencyemployerid = ae.agencyemployerid
where agencypayperiodfinalisestate <>0
   and IsNull(agencyemployertaxoffice,'') != ''
   or (agencyprofilesetupcomplete = 0 and agencyprofilecreateddate < DATEADD(dd,1,(GetDate() - 90))
order by agencyprofilename

Answer 3

在您的日期进行调整，在WHERE子句中比较...

您需要使用DATEADD function on your GETDATE() - 90.

... >= DATEADD(dd, -90, GETDATE())